. NET Deep Combat series-LINQ to SQL advanced

Recently in the process of writing code to use the LINQ query, in the process of finding information found on the web of information strange, so I sorted out some of LINQ in the easy to confuse the place.

All the code in this article is based on: UserInfo and Class Two tables, where the UserID in class corresponds to the ID in UserInfo

This article only access address: http://www.cnblogs.com/yubaolee/p/BestLinqQuery.html

LINQ Federated Query Inline query

Inline is a very high-frequency query that queries two tables that are common and are not empty

From the user in UserInfo joins C in Classes on user. Id equals C.userid Select new {     user. UserName,     user. Id, C.classname}

Query results

The corresponding SQL statement

SELECT [T0]. [UserName], [t0]. [Id], [T1]. [ClassName] From [UserInfo] as [T0]inner joins [Class] as [T1] on [t0]. [Id] = [T1]. [UserId]

Leftist query

Leftist should be the most frequently used query in a federated query. It takes the left table as the standard, carries on the joint inquiry. If the corresponding result does not exist in the right table, it is empty. (Note: There is no right-link in LINQ, because right-side is nothing more than moving the left-hand table to the right, the result of the query is the same as leftist)

From the user in UserInfo  joins 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 results

corresponding SQL statements

SELECT [T0]. [UserName], [t0]. [Id], [T1]. [ClassName] As [Classname]from [UserInfo] as [T0]left OUTER JOIN [Class] as [T1] on [t0]. [Id] = [T1]. [UserId]

！ Notice leftist that "temp", it is actually a IEnumerable collection. So we can get another result to leftist:

From the user in Userinfojoin C in Classes on user. Id equals C.userid into Tempselect new{user,temp}

Query results (in order to more clearly express the collection, in the class table specifically added a record, so class there are 3)

The corresponding SQL statement, the same as leftist's SQL, but a column with a count of rows

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

The full link is the result of two tables (referred to as Cross joins in SQL), and the results of such a connection are largely useless without filtering conditions. Look at the code below:

From the user in UserInfo from the C in Classes select new {     user. UserName,     user. Id, C.classname}

Query results

corresponding SQL statements

SELECT [T0]. [UserName], [t0]. [Id], [T1]. [ClassName] From [UserInfo] as [t0], [Class] as [T1]

Merge (Union)

This kind of query is rarely used, but it can be very useful in some perverted business needs, paying attention to the results of the query. It is the result of merging the same number of columns in two tables, and if the result has the same row, 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 results

corresponding SQL statements

SELECT [T0]. [Id] As [value], [t0]. [UserName] From [UserInfo] as [t0]unionselect [T1]. [UserId] As [value], [T1]. [ClassName] From [Class] as [T1]

LINQ Group queries

Group By is also a common operation in the actual project, the query operation is as follows:

From C in Classesgroup C by C.userid into Tempselect temp

Query results

Note the results of the query, the outer layer is a common iqueryable<t>, which is a dictionary-like K-v collection. The simple point is that the group returns a collection of collections, so the output needs a double loop.

When we use its results, we should call it this way:

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);                }            }

Entity Increment field Handling

The UserInfo entity classes I have in this example are 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 in the entity. In order to preserve the purity of the original entity class. I added a new partial class:

Public partial class UserInfo {//<summary>///        Test Extended Properties///        </summary> public        string Userext        {            get {return UserName + ":" + usertype;}        }    }

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

But if we use it this way:

From the user in _context. Userinfoesselect new{    user. Id,    user. Userext};

You will find that the compilation is not a problem. However, the following exception occurs at run time:

The specific error message is as follows: The specified type member ' Userext ' is not supported in LINQ to entities. Only initializers, the entity members, and the entity navigation properties are supported.

That is, the "Userext" type is not supported by LINQ. Because before you go into foreach to actually fetch data. EF has turned LINQ into SQL statements, and Userext is turned into corresponding database fields. This problem occurs because the field does not appear in the database. The workaround is simple:

From the user in _context. Userinfoes.tolist () Select New  {      user. Id,      user. Userext  

The ToList () is executed first, the LINQ is executed in advance, and the UserInfo collection is generated so that the userext can be accessed normally. Don't look at this little change. In the case of multi-table search filter field, you will experience endless beauty!

You might think of a problem, what happens if I add a full attribute?

public partial class UserInfo    {public        string userext        {            get {return UserName + ":" + usertype;}        }        //Add a full property public        string UserExt2 {get; set;}    }

The above UserExt2 is a new attribute we are adding, now let's execute the query. I think people who have really studied LINQ must know the result.

The attributes in an entity must be specified when the mapping is configured in a LINQ operation. Our database is of course not UserExt2 this field, so add ignore identity, or call:

This. Ignore (t = t.userext2);

. NET Deep Combat series-LINQ to SQL advanced