C ා data operation series - Introduction to 19 FreeSql

1.1 simple insertion

Then try inserting the data:

var row = FreeSqlContext.FreeSqlConnect.Insert(new Model { Name = "test", StringLength = 10 }).ExecuteAffrows();

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:

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.

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:

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 ), 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]).WhereDynamic(list[2]).ExecuteAffrows(); 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.

Example:

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 FreeSqlContext.FreeSqlConnect.Update<Model>() .Where(t => true) .Set(t => t.StringLength + 1) .ExecuteAffrows(); // perhaps FreeSqlContext.FreeSqlConnect.Update<Model>(1) .Set(t => t.StringLength + 1) .ExecuteAffrows();

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.

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

Add new comment

0 comments