LINQ to SQL combined with Entity Framework Connection Query Summary

Recently doing projects using LINQ in conjunction with EntityFramework to handle database operations. Want to also use for almost a year, found that some use of skills is necessary to pay attention to, special to do under the summary, I hope that the new friends are helpful. At the beginning of the use of the total loop query subquery, the results of poor performance is not good, now look at all feel funny. Also only write a very bad code to know what kind of code is elegant bar, haha. Let me summarize the connection query, I found a lot of new friends just like me, do not know how to use LINQ to write similar to SQL connection query (left JOIN, INNER join, etc.).

Connection Query

• Internal connection Query

The LINQ syntax is as follows:

1 var  from inch sys. Apple2in             sys. Banana3          4           Selectnew  {5                  a.id,6                a.name7            };

The direct write join is an inner join query, so let's look at the SQL statement it produces to see why this is inside the connection.

The resulting SQL is as follows:

SELECT 1 as [C1], [Extent1]. [ID] as [id], [Extent1]. [Name] As [Name]from  [dbo].[ Apple] as [Extent1]inner JOIN [dbo]. [Banana] As [Extent2] on [Extent1]. [Id] = [Extent2]. [AppleID]

• Left join connected, right connection

The LEFT Join LINQ syntax is as follows:

1         varres = fromAinchsys. Apple2Join Binchsys. Banana3 On a.id equals B.appleid into B14                       Select New {5 a.ID,6 A.name,7Bananas =B18};

The key here is that the collection assignment of line 7th, EF will generate the corresponding SQL statement based on the results you need to query.

Let's look at the generated SQL statement:

SELECT [Project1]. [C1] As [C1], [Project1]. [ID] as [id], [Project1]. [Name] As [Name], [Project1]. [C2] As [C2], [Project1]. [ID1] As [Id1], [Project1]. [Name1] As [Name1], [Project1]. [AppleID] As [AppleID], [Project1]. [Comment] As [Comment]from (SELECT [extent1].[ ID] as [id], [Extent1]. [Name] As [Name],1as [C1], [Extent2]. [Id] as [Id1], [Extent2]. [Name] As [Name1], [Extent2]. [AppleID] As [AppleID], [Extent2]. [Comment] As [comment], case when ([extent2].[ ID] is NULL) then CAST (null asint) ELSE1END as [C2] from [dbo]. [Apple] As [Extent1] left OUTER JOIN [dbo]. [Banana] As [Extent2] on [Extent1]. [Id]=[Extent2].  [AppleID]) As [Project1]order by [Project1]. [Id] ASC, [Project1]. [C2] ASC

View Code

The result of this query is that an object contains B1 combined, not SQL queries to the temporary table results after the connection. This is where EF automatically helps us sort out the data in this form. Useful when querying a one-to-many table data. Of course, you can also use the navigation properties.

Let's take a look at another form of left connection.

The LINQ syntax is as follows:

1         varres = fromAinchsys. Apple2Join Binchsys. Banana3 On a.id equals B.appleid into B14                        fromB2inchB1. DefaultIfEmpty ()5                       Select New {6 a.ID,7 A.name,8t =B2. AppleID9};

Generated SQL statement:

1 as [C1], [Extent1]. [ID] as [id], [Extent1]. [Name] As [Name], [Extent2]. [AppleID] As [appleid]from  = [Extent2]. [AppleID]

The collection data for the result of this query corresponds to the number of rows of the temporary table after the Cartesian product. In a one-to-many scenario, the data for the main table is duplicated.

Right connection as long as the apple and the banana in the order of the swap is OH.

Let's wrap it up. The SQL statement generated by EF is flexible, and the content of select is very much affected by the last generated inner join or left join. The above is for reference only, of course, left connection, right connection and other wording.

• Fully connected

The whole connection how to write, a little surprise, actually very simple.

The LINQ syntax is as follows:

        var  from inch sys. Apple                      from in  sys. Banana                      Selectnew  {                         a.id,                         a.name,                         //  Bananas = B1                         t = b.appleid                      };

Generated SQL statement:

1 as [C1], [Extent1]. [ID] as [id], [Extent1]. [Name] As [Name], [Extent2]. [AppleID] As [Appleid]from  [dbo].[ Apple] as [Extent1]cross JOIN [dbo]. [Banana] As [Extent2]

The connection query has been introduced here. When querying multiple tables, there are a number of places to connect to query applications. Remind you when querying multiple tables, do not use foreach or select to write sub-queries, as far as possible to use the connection query to replace. In my practice, the efficiency of a connection query is much higher than a circular subquery in most cases. Even more than 10 times times the gap.

LINQ to SQL combined with Entity Framework Connection Query Summary