A summary of connection queries by using the Entity Framework

A summary of connection queries by using the Entity Framework

Recently, I was working on a project that used the combination of linq and EntityFramework to process database operations. I have been using it for almost a year. I found that some tips should be noted. I would like to make a summary and hope to help my friends who are just getting started. When I first started using subqueries in various cycles, the performance of the results was not good. now I think it is funny. It is elegant to know what the code is only when the code is poorly written. Haha. I will summarize the connection query first. I found that many of my new friends are the same as me. I don't know how to use linq to write connection queries similar to SQL (left join, inner join, etc ).

Connection Query

• Internal Connection Query

The following is the linq syntax:

1 var res = from a in sys.Apple2           join b in sys.Banana3           on a.Id equals b.AppleId 4           select new {5                 a.Id,6                 a.Name7            };

Directly Writing a join statement is an internal join query. Let's take a look at the SQL statements it produces to understand why this is an internal join.

The generated SQL statement 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 and right join

The syntax for left-join linq is as follows:

1         var res = from a in sys.Apple2                       join b in sys.Banana3                       on a.Id equals b.AppleId into b14                       select new {5                          a.Id,6                          a.Name,7                          Bananas = b18                       };    

The key here is to assign values to the set of 7th rows. EF will generate corresponding SQL statements based on the query results you need.

Let's take a 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], 1 AS [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 as int) ELSE 1 end 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] ASCView Code

The result of this query will be the result of a temporary table after an object contains b1, rather than SQL queries. Here EF automatically helps us sort the data. It is useful when querying one-to-many table data. You can also use navigation attributes.

 

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

The following is the linq syntax:

1         var res = from a in sys.Apple2                       join b in sys.Banana3                       on a.Id equals b.AppleId into b14                       from b2 in b1.DefaultIfEmpty()5                       select new {6                          a.Id,7                          a.Name,8                          t = b2.AppleId9                       };    

Generated SQL statement:

SELECT 1 AS [C1], [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[AppleId] AS [AppleId]FROM  [dbo].[apple] AS [Extent1]LEFT OUTER JOIN [dbo].[banana] AS [Extent2] ON [Extent1].[Id] = [Extent2].[AppleId]

In this way, the Set Data of the query result corresponds to the number of rows in the temporary table after Descartes. In case of one to multiple tables, the data in the primary table will be duplicated.

For the right connection, you only need to adjust the order of apple and banana.

To sum up, the SQL statements generated by EF are flexible. The select content has a great impact on the final inner join or left join statements. The above writing method is for reference only. Of course, there are other writing methods for left and right connections.

• Full connection

How to write the full connection? It's a little surprising, but it's actually very simple.

The following is the linq syntax:

        var res = from a in sys.Apple                      from b in sys.Banana                      select new {                         a.Id,                         a.Name,                         //Bananas = b1                         t = b.AppleId                      };

Generated SQL statement:

SELECT 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]

Now we have finished the connection query introduction. When querying multiple tables, there are many connected query applications. We recommend that you do not use foreach or select to write subqueries when querying multiple tables. Try to use join queries instead. In my practice, connection query is much more efficient than loop subqueries in most cases. The gap is even more than 10 times.