Several practical functions of FreeSql v0.11

Keywords: C# MySQL Attribute github Database

FreeSql has been open source for nearly a year, and is determined to become a convenient and easy-to-use ORM for. Net platform. Warehouse address: https://github.com/2881099/FreeSql

With the continuous iterative updating, it becomes more and more stable and powerful. It is expected to release the official version of 1.0 on the first anniversary (January 1, 2020).

The golden nine silver ten days have passed. In this copper like month, I have done several major functions, hoping to provide greater convenience for user development.

  • I. Dto mapping query
  • II. Include many
  • III. Where (a = > true) logical expression parsing optimization
  • IV. SaveManyToMany save many to many set attributes
  • 5. Migrate entities to the specified table name
  • Vi. On Duplicate Key Update and Pgsql upsert
  • VII. ISelect.ToDelete advanced deletion
  • VIII. Global filter

The following code, the prerequisite definition code is as follows:

IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\db1.db;Max Pool Size=10";)
    .UseAutoSyncStructure(true) //Automatically synchronize entity structure to database
    .Build();

public class Blog
{
    public Guid Id { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}

I. Dto mapping query

class Dto
{
    public Guid Id { get; set; }
    public string Url { get; set; }
    public int xxx { get; set; }
}

fsql.Select<Blog>().ToList<Dto>();
//SELECT Id, Url FROM Blog

fsql.Select<Blog>().ToList(a => new Dto { xxx = a.Rating} );
//SELECT Id, Url, Rating as xxx FROM Blog
//Write like this, attach all maps, and then add xxx map

fsql.Select<Blog>().ToList(a => new Blog { Id = a.Id }) 
//Write like this, only query id

fsql.Select<Blog>().ToList(a => new { a.Id }) 
//In this way, only the id is queried, and anonymous objects are returned

Mapping supports single table / multi table, which is mapping before querying data (not querying all fields first and then mapping to memory)

Search rules and attribute names will loop through internal objects such as "tables" (which will grow after join query), and the main table will be used first until the same fields are found.

Such as:

A, B, C all have ID, dto {ID, A1, A2, B1, B2}, A.id is mapped. You can also specify id = C.id mapping.

Friendly reminder: a navigation attribute can be mapped directly in dto

II. Include many

It has been implemented before. It is associated with the navigation collection properties that have a set relationship and have no relationship.

With settings (support one to many, many to many):

fsql.Select<Tag>().IncludeMany(a => a.Goods).ToList();

If no relationship is set, temporarily specify the relationship (only one to many is supported):

fsql.Select<Goods>().IncludeMany(a => a.Comment.Where(b => b.TagId == a.Id));

Query only the first few pieces of data of each sub collection to avoid poor IO performance caused by loading all data like EfCore (for example, 2000 comments under a product):

fsql.Select<Goods>().IncludeMany(a => a.Comment.Take(10));

The existing IncludeMany functions above are not free and flexible enough.

New function 1: mapping IncludeMany on Dto

The old IncludeMany limit can only be used in ISelect. You must first check the superior data. To solve this problem, we have mapped directly on Dto:

Query the Goods table, 10 pieces of data in categories 1, 2 and 3

//Define a temporary class, or a Dto class
class Dto {
    public int TypeId { get; set; }
    public List<Goods > GoodsList { get; set; }
}

var dto = new [] { 1,2,3 }.Select(a => new Dto { TypeId = a }).ToList();
dto.IncludeMany(d => d.GoodsList.Take(10).Where(gd => gd.TypeId == d.TypeId));

//After execution, dto will have only 10 records per element. Vods

Now IncludeMany is no longer the patent of ISelect. Ordinary list < T > can also be used to greedily load data and accurately fill in internal elements.

New function 2: query the specified fields of the subset table

The old IncludeMany limit can only check all fields of the sub table. If there are too many sub tables, IO performance will be wasted.

The new function can set the subset to return part of the fields to avoid the problem of too many fields in the subset.

fsql.Select<Tag>().IncludeMany(a => a.Goods.Select(b => new Goods { Id = b.Id, Title = b.Title }));
//Only query the ID and title fields of the goods table, and then fill them in

III. Where (a = > true) logical expression parsing optimization

I believe that many ORM parsing expressions can't handle this problem, we have solved 99% before.

This month, we found that there is still a lot to be done. After finding the problem, we solved it in time, and added unit test code to solve the problem.

IV. SaveManyToMany save many to many set attributes

Before this, FreeSql.DbContext and warehousing implementation have realized the joint level saving function, as follows:

When the joint saving function can save an object, the set of [OneToMany] and [ManyToMany] navigation attributes will also be saved.

Global shutdown:

fsql.SetDbContextOptions(opt => opt.EnableAddOrUpdateNavigateList = false);

Local close:

var repo = fsql.GetRepository<T>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = false;

New functions:

Save the specified many to many navigation attribute of the entity. The SaveManyToMany method is implemented in BaseRepository and DbContext.

Problem solving: when the entity class navigation data is too complex, it is wise to choose to turn off the function of joint level saving, but at this time, the function of [many to many] data saving is very cumbersome (because it needs to be saved after comparing with the existing data).

var song = new Song { Id = 1 };
song.Tags = new List<Tag>();
song.Tags.Add(new Tag ...);
song.Tags.Add(new Tag ...);
song.Tags.Add(new Tag ...);
repo.SaveManyToMany(song, "Tags");
//Easily save the association between song and tag table

The mechanism rules are the same as the [many to many] saved at the associated level, as follows:

We save the intermediate table as a complete comparison operation, and only add external entities (note that it will not be updated)

  • When the property set is empty, delete all their associated data (middle table)
  • When the attribute set is not empty, it is completely compared with the associated data (middle table) in the database, and the records that should be deleted and added are calculated

5. Migrate entities to the specified table name

fsql.CodeFirst.SyncStructure(typeof(Log), "Log_1"); //Migrate to log 1 table
fsql.CodeFirst.SyncStructure(typeof(Log), "Log_2"); //Migrate to log 2 table

In this function, we have made some upgrades to the sub table function, and the following actions will be migrated:

fsql.Select<Log>().AsTable((_, oldname) => $"{oldname}_1");
fsql.GetRepository<Log>(null, oldname => $"{oldname}_1");

Vi. On Duplicate Key Update and Pgsql upsert

FreeSql provides a variety of insertion or update methods. Before v0.11, FreeSql.Repository/FreeSql.DbContext library was mainly used for implementation.

Insertoupdate of FreeSql.Repository

This method has the same functionality as the FreeSql.DbContext AddOrUpdate method.

var repo = fsql.GetRepository<T>();
repo.InsertOrUpdate(entity);

Update if data exists for internal state management.

If there is no data in the internal status management, the same as querying the database, whether it exists.

Update if it exists, insert if it does not exist

Disadvantage: batch operation is not supported

New features: MySql features On Duplicate Key Update

FreeSql.Provider.MySql and FreeSql.Provider.MySql connector have supported MySql specific functions in version 0.11.11, On Duplicate Key Update.

This function can also insert or update data and support batch operation.

class TestOnDuplicateKeyUpdateInfo
{
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string title { get; set; }
    public DateTime time { get; set; }
}

var item = new TestOnDuplicateKeyUpdateInfo { id = 100, title = "title-100", time = DateTime.Parse("2000-01-01") };
fsql.Insert(item)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(100, 'title-100', '2000-01-01 00:00:00.000')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = VALUES(`time`)

Methods that can be called after OnDuplicateKeyUpdate():

Method name describe
IgnoreColumns Ignore updated columns, just like IUpdate.IgnoreColumns
UpdateColumns Specify the updated columns, the mechanism is the same as IUpdate.UpdateColumns
Set Manually specifying the updated column is the same as IUpdate.Set
SetRaw As a supplement to the Set method, SQL strings can be passed in
ToSql Return the SQL statement to be executed
ExecuteAffrows Execute, return the number of rows affected

IInsert and OnDuplicateKeyUpdate both have IgnoreColumns and UpdateColumns methods.

When inserting entity / collection entity, the time column is ignored. The code is as follows:

fsql.Insert(item)
    .IgnoreColumns(a => a.time)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = '2000-01-01 00:00:00.000'

We find that the UPDATE time part becomes a constant instead of VALUES(`time '). The mechanism is as follows:

When a column exists in the insert part, it will be set in the form of VALUES(` field ') in update;

When a column does not exist in the insert part, it will be set as a constant in update. When operating the entity array, this constant is executed for case when... End (the same as IUpdate);

New function 2: PostgreSQL special function On Conflict Do Update

The method of using MySql OnDuplicateKeyUpdate is roughly the same.

VII. ISelect.ToDelete advanced deletion

The default IDelete does not support navigation objects, multi table Association, etc. ISelect.ToDelete can turn a query into a delete object to support navigation objects or other query functions to delete data, as follows:

fsql.Select<T1>().Where(a => a.Options.xxx == 1).ToDelete().ExecuteAffrows();

Note: this method is not to query the data to the memory loop for deletion. The above code generates the following SQL execution:

DELETE FROM `T1` WHERE id in (select a.id from T1 a left join Options b on b.t1id = a.id where b.xxx = 1)

Benefits of using this solution for complex deletion:

  • Test data can be previewed before deletion to prevent incorrect deletion;
  • Support more complex delete operations (IDelete only supports simple operations by default), and even use Limit(10) on ISelect to delete only the first 10 records with conditions attached;

There is also ISelect.ToUpdate advanced data update function, which can be used in a similar way

VIII. Global filter

The FreeSql base layer implements the global filter function that can be set by Select/Update/Delete.

public static AsyncLocal<Guid> TenantId { get; set; } = new AsyncLocal<Guid>();

fsql.GlobalFilter
    .Apply<TestAddEnum>("test1", a => a.Id == TenantId.Value)
    .Apply<AuthorTest>("test2", a => a.Id == 111)
    .Apply<AuthorTest>("test3", a => a.Name == "11");

The Apply generic parameter can be set to any type. When the Select/Update/Delete method is used, a filter matching attempt (try catch) will be made:

  • If the match is successful, the where condition will be attached;
  • If the match fails, the mark will not match again next time to avoid performance loss;

How to disable?

fsql.Select<TestAddEnum>().ToList(); //All entry into force
fsql.Select<TestAddEnum>().DisableGlobalFilter("test1").ToList(); //Disable test1
fsql.Select<TestAddEnum>().DisableGlobalFilter().ToList(); //Disable all

The effect of fsql.Update/Delete method is the same as above.

Note: IFreeSql.GlobalFilter and warehouse filter are not one function and can take effect at the same time

Acknowledgement

Thanks for your feedback!

Warehouse address: https://github.com/2881099/FreeSql

Please move to update the log: https://github.com/2881099/Fr...

Posted by LApprenti Sorcier on Mon, 18 Nov 2019 00:06:48 -0800

Hot Keywords

©2024 Programmer Group