. NET in-depth hands-on Series-advanced to SQL, linqsql

. NET in-depth hands-on Series-advanced to SQL, linqsql

Recently, I used the Linq query in the code writing process. I found some strange information on the Internet during the data searching process. So I sorted out some confusing information about the data in the Linq.

The code in this article is based on the following two tables: UserInfo and Class. The UserId in Class corresponds to the Id in UserInfo.

From user in UserInfo join c in Classes on user. Id equals c. UserId select new {user. UserName, user. Id, c. ClassName}

Query Result

SELECT [t0]. [UserName], [t0]. [Id], [t1]. [ClassName] FROM [UserInfo] AS [t0] inner join [Class] AS [t1] ON [t0]. [Id] = [t1]. [UserId]Left join query

The left join should be the most frequently used query in the joint query. The Union query is performed based on the left table. If no result exists in the right table, leave it blank. (Note: The right join clause does not exist in Linq, because the right join clause only moves the table on the left to the right, and the query result is the same as the left join clause)

from user in UserInfo  join c in Classes on user.Id equals c.UserId into temp  from c in temp.DefaultIfEmpty()  select new  {       user.UserName,       user.Id,       c.ClassName  }  

Query Result

SELECT [t0]. [UserName], [t0]. [Id], [t1]. [ClassName] AS [ClassName] FROM [UserInfo] AS [t0] left outer join [Class] AS [t1] ON [t0]. [Id] = [t1]. [UserId]

! Note that the Left link [temp] is actually an IEnumerable set. So we can get another result of the left join:

from user in UserInfojoin c in Classes on user.Id equals c.UserId into tempselect new{user,temp}

Query results (in order to better express the set, a special record is added to the Class table, so there are 3 records in the class)

SELECT t0. *, [t1]. [Id] AS [Id2], t1. *, (select count (*) FROM [Class] AS [t2] WHERE [t0]. [Id] = [t2]. [UserId]) AS [value] FROM [UserInfo] AS [t0] left outer join [Class] AS [t1] ON [t0]. [Id] = [t1]. [UserId]Full join

Full join is used to obtain the cross results of two tables (called cross join in SQL). The results of this join method are basically useless without filtering conditions. The Code is as follows:

 from user in UserInfo from c in Classes select new {     user.UserName,     user.Id, c.ClassName }

Query Result

SELECT [t0]. [UserName], [t0]. [Id], [t1]. [ClassName] FROM [UserInfo] AS [t0], [Class] AS [t1]Union)

This type of query is rarely used, but it is very useful in some abnormal business needs. Pay attention to the query results. It is the result of merging two tables with the same number of columns. If the results contain the same rows, only one row of records is taken.

(from userinfo in UserInfoselect new {  Id = (System.Int32?)userinfo.Id,  Name = userinfo.UserName}).Union(from c in Classes  select new {  Id = (System.Int32?)c.UserId,  Name = c.ClassName})

Query Result

SELECT [t0]. [Id] AS [value], [t0]. [UserName] FROM [UserInfo] AS [t0] UNIONSELECT [t1]. [UserId] AS [value], [t1]. [ClassName] FROM [Class] AS [t1]Query by Linq Group

Group query is also a common operation in actual projects. The query operation is as follows:

from c in Classesgroup c by c.UserId into tempselect temp

Query Result

Var result = from c in _ context. classes group c by c. userId into temp select temp; foreach (var c in result) {Console. writeLine (c. key); foreach (var citem in c) {Console. writeLine (citem. className );}}Adding fields to an object

In this example, the UserInfo object class is as follows:

 public partial class UserInfo    {        public int Id { get; set; }        public string UserName { get; set; }        public string UserType { get; set; }        public int Money { get; set; }    }

Now I want to add an attribute to the class of this object. To keep the purity of the original entity class. I added a new partial class:

Public partial class UserInfo {// <summary> // test extended attributes /// </summary> public string UserExt {get {return UserName + ": "+ UserType ;}}}

Then we use EF to access and find that it is accessible:

From user in _ context. UserInfoesselect new {user. Id, user. UserExt };

You will find that the compilation is correct. However, the following exception occurs during running:

From user in _ context. UserInfoes. ToList () select new {user. Id, user. UserExt };

That is, execute ToList () First, let the linq execute in advance, and generate the UserInfo collection, so that you can access UserExt normally. Don't look at this small change. When filtering fields in Multi-table join queries, you will experience endless advantages!

You may think of a problem. What happens if I add a complete attribute?

Public partial class UserInfo {public string UserExt {get {return UserName + ":" + UserType ;}// Add a complete public string UserExt2 {get; set ;}}

The above UserExt2 is a new attribute. Now let's execute the query. I think those who actually studied Linq will surely know the results.

This. Ignore (t => t. UserExt2 );