C ා data operation series - Introduction to 19 FreeSql

0. Preface

The author of FreeSql recommended the FreeSql framework to me a few days ago. I want to help write an article about it. Well, I can't believe I can bring a product. Ha ha, I'm joking. I think it's very interesting after reading it, so I thank you for this article.

Briefly, FreeSql is a sandbox level project organized by NCC. It is a powerful ORM component that supports. NET Core,. NET Framework and Xamarin. At present, FreeSql supports the following databases: MySQL, PostgreSQL, SqlServer, Oracle, Sqlite, Odbc, Microsoft Access and domestic database daydream.

That is to say, it is also an excellent project maintained by excellent developers in China, with complete functions. My friends have time to see it. The following image is copied from its GitHub warehouse. You can see that there are quite a lot of supported functions.

About NCC community, it is an open source community of. net core and the largest. net core open source community in China

1. Preliminary use

As usual, there is no call without installation. So, after creating the project, install the following:

dotnet add package FreeSql

Then create an IFreeSql object:

public class FreeSqlContext
    public static IFreeSql FreeSqlConnect { get; } =
        new FreeSql.FreeSqlBuilder()            .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=document.db")
        .UseAutoSyncStructure(true) //Automatically synchronize entity structure to database

Because of the official requirement to declare IFreeSql objects as singleton mode, I use static properties here.

This writing method is a kind of syntactic sugar of C ා. Only get indicates that the attribute is a read-only attribute (there is a subtle gap between the read-only attribute and the attribute). After the equal sign, it indicates the first assignment of the attribute.

Create a normal Model class:

public class Model
    public int Id { get; set; }
    public int StringLength { get; set; }
    public string Name { get; set; }

1.1 simple insertion

Then try inserting the data:

var row = FreeSqlContext.FreeSqlConnect.Insert(new Model
    Name = "test",
    StringLength = 10

The prompt is as shown in the figure. We need to manually install SQLite driver of FreeSql. After installation:

dotnet add packages FreeSql.Provider.Sqlite

FreeSql has independently developed driver packages for various supported databases, which are uniformly named as:

FreeSql.Provider . < database type >

After the installation is completed, the execution is completed successfully after the re run. By the way, the database has been generated (which I feel is very good). At the same time, a Model table with the primary key as Id has been generated:

create table Model
    Id           INTEGER
        primary key,
    StringLength INTEGER not null,
    Name         NVARCHAR(255)

1.2 simple query

Next, simply query the data just inserted:

var list = FreeSqlContext.FreeSqlConnect.Queryable<Model>().ToList();

It can be found that query is very convenient.

1.3 simple update

Compared with other frameworks, the update of FreeSql is a little more complex. Here is an update method:

list[0].Name = "Modify test";
row = FreeSqlContext.FreeSqlConnect.Update<Model>().SetSource(list[0]).ExecuteAffrows();

First declare that the type to be updated is Model, and then set the update source.

1.4 simple deletion

row = FreeSqlContext.FreeSqlConnect.Delete<Model>(new[] { list[0] }).ExecuteAffrows();

Delete the previously acquired data.

In a nutshell, the addition, deletion, modification and query designed by FreeSql are all in the form of commands, and the data will not be saved in the database before the actual call to excelxxx.

2. Details of addition, deletion and modification

In the previous section, we simply used the add, delete, and modify query. This section will give you a detailed analysis of the add, delete, and modify query of FreeSql.

2.1 NEW

IInsert<T1> Insert<T1>() where T1 : class;
IInsert<T1> Insert<T1>(T1 source) where T1 : class;
IInsert<T1> Insert<T1>(IEnumerable<T1> source) where T1 : class;
IInsert<T1> Insert<T1>(List<T1> source) where T1 : class;
IInsert<T1> Insert<T1>(T1[] source) where T1 : class;

This is the Insert method declared in IFreeSql interface. Through this method, we can see the single data insertion and multiple data insertion, and return an interface with iiinsert < T1 >. Of course, you can get an IInsert interface instance directly without passing in data. These methods are very simple. We won't spend too much time here, and then jump into IInsert to see what methods are in it.

Let's start with a set of methods:

IInsert<T1> AppendData(T1 source);
IInsert<T1> AppendData(T1[] source);
IInsert<T1> AppendData(IEnumerable<T1> source);

These methods can continue to add data for IInsert later, so that more inserts can be performed.

IInsert<T1> IgnoreColumns(string[] columns);
IInsert<T1> IgnoreColumns(Expression<Func<T1, object>> columns);

Set the columns that are ignored during insertion, after which they will not be inserted into the database. Where expression < func < T1, Object > > represents an anonymous object containing the column name attribute.

IInsert<T1> InsertColumns(string[] columns);
IInsert<T1> InsertColumns(Expression<Func<T1, object>> columns);

Set to insert only these columns, other columns will not be inserted.

Inserts are performed by calling the following methods:

int ExecuteAffrows();// Return affected columns
long ExecuteIdentity();// Return auto increment primary key value

This method requires the primary key of the entity class to be marked as auto increment (see the next section for this part). If bulk insert mode is enabled, this value returns the primary key value of the last data.

List<T1> ExecuteInserted();// Return the inserted data

The official annotation of this method only works in Postgresql/SqlServer.

This is the basic content of insertion, which is relatively simple.

2.2 deletion

Change the order this time, because the deletion method is relatively simple here. FreeSql is cautious about single table data deletion. Let's take a look at how to delete it.

IDelete<T1> Delete<T1>() where T1 : class;

Set the generic type and create a remover (the name I gave, which is not officially given, i.e. an IDelete interface instance).

IDelete<T1> Delete<T1>(object dywhere) where T1 : class;

This method is very interesting and supports a wide range.

Here are the official notes:

Primary key value | new [] {primary key value 1, primary key value 2} tentity1 | new [] {tentity1, tentity2} new {id = 1}

According to the actual performance, the data of the corresponding primary key will be deleted. If the entity is passed in, the primary key of the corresponding entity will be automatically analyzed, and then the data will be marked as to be deleted.

Remember this, because it will be used in subsequent updates.

Any deletion in IFreeSql will not be deleted immediately, and an IDelete instance will be returned. Like IInsert, the excelxxx method needs to be called manually.

Let's take a look at the method in IDelete:

IDelete<T1> Where(Expression<Func<T1, bool>> exp);
IDelete<T1> Where(string sql, object parms = null);
IDelete<T1> Where(T1 item);
IDelete<T1> Where(IEnumerable<T1> items);

Simply look at the method, you can know its meaning through methods and parameters.

It should be noted that if exp is used for batch deletion, only the attributes of entity class can be used as the condition, not the navigation attributes.

If you use sql statements, you can use the parameterized writing method as follows: where ("id =? ID", new {id = 1}), and splicing in sql if there are multiple conditions.

IDelete<T1> WhereDynamic(object dywhere, bool not = false);

Here, dywhere is the same as dywhere of Delete. If not is set to true, it means to Delete other objects.

When FreeSql designs delete mode, if IFreeSql.Delete If you continue to call Where or WhereDynamic after passing in the parameter, the conditions are spliced in the form of and twice:

list = FreeSqlContext.FreeSqlConnect.Queryable<Model>().ToList();
FreeSqlContext.FreeSqlConnect.Delete<Model>(list[0]).Where(t=>t.Id > 10).ExecuteAffrows();

The following SQL statements are generated respectively:

DELETE FROM "Model" WHERE ("Id" = 1) AND ("Id" = 11)
DELETE FROM "Model" WHERE ("Id" = 1) AND ("Id" > 10)

Well, it's better to pay attention to the deletion when calling, because if the conditions conflict, the data may not change at all.

Perform delete:

int ExecuteAffrows();//Return the number of rows affected
List<T1> ExecuteDeleted();// Return the deleted data. Only Postgresql/SqlServer has the same effect

2.3 update

IUpdate<T1> Update<T1>() where T1 : class;
IUpdate<T1> Update<T1>(object dywhere) where T1 : class;

Similarly, open an updater (get an IUpdate example), where dywhere is the same as deleting the supported content. However, there is one difference:

row = FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).ExecuteAffrows();

There will be no data changes. Well, this is quite different from Delete. To understand, here FreeSql only parses the entities in the data, but does not parse the update SQL statements from the incoming entities.

Next, enter IUpdate:

IUpdate<T1> UpdateColumns(string[] columns);
IUpdate<T1> UpdateColumns(Expression<Func<T1, object>> columns);
IUpdate<T1> IgnoreColumns(Expression<Func<T1, object>> columns);
IUpdate<T1> IgnoreColumns(string[] columns);

Set columns to update and columns to ignore, conflicting.


row = FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).UpdateColumns(new[] { "Name" }).ExecuteAffrows();

Do you think it will be updated happily? The answer is cruel, No. Still returns 0. As mentioned above, when FreeSql is updating, it needs to specify the updated data source additionally:

IUpdate<T1> SetSource(T1 source);
IUpdate<T1> SetSource(IEnumerable<T1> source);

That is, FreeSql parses the fields that need to be updated from source, and then uses Update/Ignore to set which columns are only updated or ignored.

Final example:

row = FreeSqlContext.FreeSqlConnect.Update<Model>(list[0])
    .SetSource(list[0]).UpdateColumns(new[] { "Name" }).ExecuteAffrows();
row = FreeSqlContext.FreeSqlConnect.Update<Model>(list[0])
    .SetSource(list).UpdateColumns(new[] { "Name" }).ExecuteAffrows();
row = FreeSqlContext.FreeSqlConnect.Update<Model>(new[] { list[0] ,list[1]})
    .SetSource(list[0]).UpdateColumns(new[] { "Name" }).ExecuteAffrows();
row = FreeSqlContext.FreeSqlConnect.Update<Model>(new[] { list[0], list[1] })
    .SetSource(list).UpdateColumns(new[] { "Name" }).ExecuteAffrows();

Then generate the following SQL:

UPDATE "Model" SET "Name" = @p_0 WHERE ("Id" = 1) AND ("Id" = 1)
UPDATE "Model" SET "Name" = CASE "Id"
WHEN 1 THEN @p_0
WHEN 10 THEN @p_1
WHEN 11 THEN @p_2
WHEN 12 THEN @p_3
WHEN 13 THEN @p_4
WHEN 14 THEN @p_5
WHEN 15 THEN @p_6
WHEN 16 THEN @p_7
WHEN 17 THEN @p_8
WHEN 18 THEN @p_9
WHEN 19 THEN @p_10
WHEN 20 THEN @p_11
WHEN 21 THEN @p_12
WHEN 22 THEN @p_13
WHEN 23 THEN @p_14
WHEN 24 THEN @p_15 END
WHERE ("Id" IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) AND ("Id" = 1)
UPDATE "Model" SET "Name" = @p_0 WHERE ("Id" = 1) AND ("Id" = 1 OR "Id" = 10)
UPDATE "Model" SET "Name" = CASE "Id"
WHEN 1 THEN @p_0
WHEN 10 THEN @p_1
WHEN 11 THEN @p_2
WHEN 12 THEN @p_3
WHEN 13 THEN @p_4
WHEN 14 THEN @p_5
WHEN 15 THEN @p_6
WHEN 16 THEN @p_7
WHEN 17 THEN @p_8
WHEN 18 THEN @p_9
WHEN 19 THEN @p_10
WHEN 20 THEN @p_11
WHEN 21 THEN @p_12
WHEN 22 THEN @p_13
WHEN 23 THEN @p_14
WHEN 24 THEN @p_15 END
WHERE ("Id" IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)) AND ("Id" = 1 OR "Id" = 10)

It can be seen that when generating IUpdate instances, it's weird to use SetSource to update the incoming data. Therefore, the normal use of SetSource is to obtain an IUpdate instance directly without passing dywhere.

So when should dywhere be used?

row = FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).Set(t => t.StringLength + 1).ExecuteAffrows();

Update by calling Set/SetDto/SetIf, of course, these three methods are not limited to using the dywhere parameter.

IUpdate<T1> Set<TMember>(Expression<Func<T1, TMember>> exp);
IUpdate<T1> Set<TMember>(Expression<Func<T1, TMember>> column, TMember value);
IUpdate<T1> SetDto(object dto);
IUpdate<T1> SetIf<TMember>(bool condition, Expression<Func<T1, TMember>> exp);
IUpdate<T1> SetIf<TMember>(bool condition, Expression<Func<T1, TMember>> column, TMember value);

Among them:

  • Expression < func < T1, tmember > > exp means to operate on the basis of the value of the field itself
  • Expression < func < T1, tmember > > column, tmember value means to set column value
  • object dto an anonymous class containing the attributes and values to be updated, or a dictionary type (the key is the column to be updated, and the value is the value of the corresponding column)
  • bool condition means update if conditions are met, otherwise no update will be made

IUpdate also provides Where mode:

IUpdate<T1> Where(Expression<Func<T1, bool>> exp);
IUpdate<T1> Where(string sql, object parms = null);
IUpdate<T1> Where(T1 item);
IUpdate<T1> Where(IEnumerable<T1> items);
IUpdate<T1> WhereDynamic(object dywhere, bool not = false);

The final update should be as follows:

FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).Set(t => t.StringLength + 1).ExecuteAffrows();
FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).SetDto(new { Name="Test 2" }).ExecuteAffrows();
FreeSqlContext.FreeSqlConnect.Update<Model>(list[0]).SetIf(true, t => t.Name + 1).ExecuteAffrows();
// Or the following modes
                .Where(t => true)
                .Set(t => t.StringLength + 1)
// perhaps
                .Set(t => t.StringLength + 1)

Perform update:

int ExecuteAffrows();// Returns the number of rows affected
List<T1> ExecuteUpdated();// Well, only Postgresql/SqlServer works

2.4 query

There are two ways to query FreeSql: one is to use the ISelect method of FreeSql, and the other is to use the Queryable method which is extended. Both of them return the same finally, and both return an ISelect instance.

Let's take a look at the statement of two methods quietly:

ISelect<T1> Select<T1>() where T1 : class;
ISelect<T1> Select<T1>(object dywhere) where T1 : class;
// Extension method in FreeSqlGlobalExtensions class
public static ISelect<T> Queryable<T>(this IFreeSql freesql) where T : class;

There is a shiny dywhere. Like Update/Delete, it also obtains the corresponding data by parsing the incoming attribute to the primary key value.

Let's go to ISelect:

Ignore the methods supported by multiple generics:

T1 First()
TDto First<TDto>();
TReturn First<TReturn>(Expression<Func<T1, TReturn>> select);
T1 ToOne();
TDto ToOne<TDto>();
TReturn ToOne<TReturn>(Expression<Func<T1, TReturn>> select);
  • First and ToOne both return the first data
  • TDto represents the collection of fields to be queried, and the column names correspond to each other in the data table
  • Expression < func < T1, return > > select type projection, establish the relationship between T1 and return through lambda statement

Return multiple:

List<T1> ToList(bool includeNestedMembers = false);
List<TDto> ToList<TDto>();
List<TReturn> ToList<TReturn>(Expression<Func<T1, TReturn>> select);
  • includeNestedMembers: false: return level 2 LeftJoin/InnerJoin/RightJoin objects; true: return navigation data of all LeftJoin/InnerJoin/RightJoin

Other methods:

long Count();// Number of returns
ISelect<T1> Distinct();//duplicate removal
ISelect<T1> Skip(int offset);// Ignore a few
ISelect<T1> Take(int limit);// Get the first few
ISelect<T1> OrderBy<TMember>(Expression<Func<T1, TMember>> column);// sort
ISelect<T1> OrderBy<TMember>(bool condition, Expression<Func<T1, TMember>> column);// sort
ISelect<T1> OrderByDescending<TMember>(Expression<Func<T1, TMember>> column); // Descending order
ISelect<T1> OrderByDescending<TMember>(bool condition, Expression<Func<T1, TMember>> column);// Descending order
decimal Sum<TMember>(Expression<Func<T1, TMember>> column);// Summation
double Avg<TMember>(Expression<Func<T1, TMember>> column);// Find the average

Set query criteria:

ISelect<T1> Where(Expression<Func<T1, bool>> exp);
ISelect<T1> WhereIf(bool condition, Expression<Func<T1, bool>> exp);
ISelect<T1> Where(string sql, object parms = null);

Note that there is a parallel relationship with dywhere.

There are many optimizations for querying FreeSql. For more information, please refer to the official documents. So far, these methods can meet the needs of a project.

3. Summary

FreeSql can be said to combine a lot of excellent ORM framework content, and according to different ways, it is divided into different plug-in forms, so that the backbone can be loaded lightly.

This is an introduction to different usage habits in the official documents.

Make complaints about the FreeSql content. If there is more demand for FreeSql, you can start to talk about it. Oh, if you want to see a little buddy, I will continue to explain it to you.

So far, 2020-5-28 "C ා data operation series" is temporarily completed (if there are other good ORM in the future, it will continue to be updated).

Please pay attention to more My blog Mr. Gao's Cabin

Tags: C# SQL Attribute Database PostgreSQL

Posted on Fri, 29 May 2020 01:40:19 -0400 by simply