EntityFramework 使用Linq处理内连接(inner join)、外链接(left/right outer join)、多表查询场景

秒速五厘米 2022-05-23 05:27 107阅读 0赞

https://www.cnblogs.com/xinwang/p/6145837.html

场景：在实际的项目中使用EntityFramework都会遇到使用Ef处理连接查询的问题，这里做一些小例子如何通过Linq语法处理内连接(inner join)、外连接(left/right outer join)；

废话不多说先看实体类：

![513504-20161208171824007-2008625888.png][]

1.内连接：

Linq：

![复制代码][copycode.gif]

var query = from st in context.SchoolBoys
                join gl in context.SchoolGirls on st.GirlfriendId equals gl.Id
                select new
                {
                    Id = st.Id,
                    Name = st.Name,
                    GirlfriendName = gl.Name
                };

![复制代码][copycode.gif]

sql：

![复制代码][copycode.gif]

SELECT 
        1 AS [C1], 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent2].[Name] AS [Name1]
        FROM  [dbo].[SchoolBoys] AS [Extent1]
        INNER JOIN [dbo].[SchoolGirls] AS [Extent2] ON [Extent1].[GirlfriendId] = [Extent2].[Id]

![复制代码][copycode.gif]

2.外连接(这里只介绍左外连接)

Linq:

![复制代码][copycode.gif]

var  query = from st in context.SchoolBoys
                 join cl in context.Classes on st.ClassId equals cl.Id into cls
                 from c in cls.DefaultIfEmpty()
                 select new
                 {
                     Id = st.Id,
                     Name = st.Name,
                     ClassName = c.Name
                 };

![复制代码][copycode.gif]

sql：

![复制代码][copycode.gif]

SELECT 
        1 AS [C1], 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent2].[Name] AS [Name1]
        FROM  [dbo].[SchoolBoys] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Classes] AS [Extent2] ON [Extent1].[ClassId] = [Extent2].[Id]

![复制代码][copycode.gif]

3.多表混合

linq:

![复制代码][copycode.gif]

var query3 = from st in context.SchoolBoys
                 join gl in context.SchoolGirls on st.GirlfriendId equals gl.Id into sgs
                 from sg in sgs
                 join cl in context.Classes on sg.ClassId equals cl.Id into cls
                 from cla in cls.DefaultIfEmpty()
                 join g in context.Grades on cla.GradeId equals g.Id into gs
                 from gr in gs.DefaultIfEmpty()
                 select new
                 {
                      Id = st.Id,
                      Name = st.Name,
                      ClassName = cla.Name,
                      GradeName = gr.Name
                 };

![复制代码][copycode.gif]

sql：

![复制代码][copycode.gif]

SELECT 
        1 AS [C1], 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent3].[Name] AS [Name1], 
        [Extent4].[Name] AS [Name2]
        FROM    [dbo].[SchoolBoys] AS [Extent1]
        INNER JOIN [dbo].[SchoolGirls] AS [Extent2] ON [Extent1].[GirlfriendId] = [Extent2].[Id]
        LEFT OUTER JOIN [dbo].[Classes] AS [Extent3] ON [Extent2].[ClassId] = [Extent3].[Id]
        LEFT OUTER JOIN [dbo].[Grades] AS [Extent4] ON [Extent3].[GradeId] = [Extent4].[Id]

![复制代码][copycode.gif]

[513504-20161208171824007-2008625888.png]: /images/20220523/ff63e2be2c4f4079b101defac7b2c8ea.png
[copycode.gif]: /images/20220523/f288c829680b417f9e6d6100e54a7d43.png