菜单 学习猿地 - LMONKEY

VIP

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练!告别无效练习

接私单赚外块

VIP优先接,累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

入驻
160
0

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

原创
05/13 14:22
阅读数 31154

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

废话不多说先看实体类:

 

 1.内连接:

Linq:

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

sql:

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]

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

Linq:

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

sql:

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]

3.多表混合

linq:

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

 

sql:

    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]

欢迎指正 转载请注明出处:http://www.cnblogs.com/xinwang/p/6145837.html

 

发表评论

0/200
160 点赞
0 评论
收藏