Linq语法如下:

var sg = (from g in dc.sgroup
                     join gu in dc.sgroupuser on g.gKey equals gu.gKey into l
                     from lgu in l.DefaultIfEmpty()
                     select new { g, lgu }).ToList();

Lambda表达式如下:

var sg = dc.sgroup.GroupJoin(dc.sgroupuser, g => g.gKey, gu => gu.gKey, (g, gu) => new { g, gu }).Select(o=>o).ToList() ;

注意:
Linq 与Lambda表达式取出的结果有所不同.Linq取出的结果的记录数与Sql中的Left Join的结果相同,而Lambda表达式取出的记录数是sgroup表中的记录数,sgroupuser对应的记录是以对象集合存在于结果中

下面是Inner Join:

Linq语法如下:

var sg = (from g in dc.sgroup
                    join gu in dc.sgroupuser on g.gKey equals gu.gKey                  
                    select new { g, gu }).ToList();

Lambda表达式如下:

var sg = dc.sgroup.Join(dc.sgroupuser, g => g.gKey, gu => gu.gKey, (g, gu) => new { g, gu }).Select(o=>o).ToList() ;

注意:
上面最后都用到了ToList()方法 , 用ToList()是为了一次性将数据取到本地.

LINQ标准查询操作符(二)——Join、GroupJoin、GroupBy、Concat

四、联接操作符

联接是指将一个数据源对象与另一个数据源对象进行关联或者联合的操作。这两个数据源对象通过一个共同的值或者属性进行关联。

LINQ有两个联接操作符:JoinGroupJoin

1. Join

Join操作符类似于T-SQL中的inner join,它将两个数据源相联接,根据两个数据源中相等的值进行匹配。例如,可以将产品表与产品类别表相联接,得到产品名称和与其相对应的类别名称。以下的代码演示了这一点:

//查询语法
var query =
    (from p in db.Products
     join c in db.Categories on p.CategoryID equals c.CategoryID
     where p.CategoryID == 1
     select new { p.ProductID, p.ProductName, c.CategoryID, c.CategoryName }).ToList();

生成的sql:

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent2].[CategoryID] AS [CategoryID], 
    [Extent2].[CategoryName] AS [CategoryName]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    WHERE (1 = [Extent1].[CategoryID]) AND ([Extent1].[CategoryID] IS NOT NULL)
//方法语法
var q =
    db.Products
    .Join
        db.Categories,
        p => p.CategoryID,
        c => c.CategoryID,
        (p, c) => new { p.ProductID, p.ProductName, c.CategoryID, c.CategoryName }
    .Where(p => p.CategoryID == 1)
    .ToList();

生成的sql:

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent2].[CategoryID] AS [CategoryID], 
    [Extent2].[CategoryName] AS [CategoryName]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    WHERE 1 = [Extent2].[CategoryID]

以上代码为表述清晰加入了一个条件“where p.CategoryID == 1”,即仅返回产品类别ID1的所有产品。

生成的sql语句略有不同。

2. GroupJoin

GroupJoin操作符常应用于返回“主键对象-外键对象集合”形式的查询,例如“产品类别-此类别下的所有产品”。以下的代码演示了这一点:

//查询语法
var query =
    (from c in db.Categories
     join p in db.Products on c.CategoryID equals p.CategoryID into r
     select new
         c.CategoryName,
         Products = r
     }).ToList();
//方法语法
var q =
    db.Categories
    .GroupJoin
       db.Products,
       c => c.CategoryID,
       p => p.CategoryID,
       (c, p) => new
           c.CategoryName,
           Products = p
    .ToList();

生成的sql:

SELECT 
    [Project1].[CategoryID] AS [CategoryID], 
    [Project1].[CategoryName] AS [CategoryName], 
    [Project1].[C1] AS [C1], 
    [Project1].[ProductID] AS [ProductID], 
    [Project1].[ProductName] AS [ProductName], 
    [Project1].[SupplierID] AS [SupplierID], 
    [Project1].[CategoryID1] AS [CategoryID1], 
    [Project1].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Project1].[UnitPrice] AS [UnitPrice], 
    [Project1].[UnitsInStock] AS [UnitsInStock], 
    [Project1].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Project1].[ReorderLevel] AS [ReorderLevel], 
    [Project1].[Discontinued] AS [Discontinued]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[CategoryName] AS [CategoryName], 
        [Extent2].[ProductID] AS [ProductID], 
        [Extent2].[ProductName] AS [ProductName], 
        [Extent2].[SupplierID] AS [SupplierID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], 
        [Extent2].[UnitPrice] AS [UnitPrice], 
        [Extent2].[UnitsInStock] AS [UnitsInStock], 
        [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], 
        [Extent2].[ReorderLevel] AS [ReorderLevel], 
        [Extent2].[Discontinued] AS [Discontinued], 
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Categories] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
    )  AS [Project1]
    ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC

返回的结果为:
在这里插入图片描述

五、分组操作符

分组是根据一个特定的值将序列中的元素进行分组。LINQ只包含一个分组操作符:GroupBy

下面的示例中使用了产品表,以CategoryID作为分组关键值,按照产品类别对产品进行了分组。

//查询语法
var query =
    (from p in db.Products
     group p by p.CategoryID).ToList();
//方法语法
var q =
    db.Products
    .GroupBy(p => p.CategoryID)
    .ToList();

生成的sql:

SELECT 
    [Project2].[C1] AS [C1], 
    [Project2].[CategoryID] AS [CategoryID], 
    [Project2].[C2] AS [C2], 
    [Project2].[ProductID] AS [ProductID], 
    [Project2].[ProductName] AS [ProductName], 
    [Project2].[SupplierID] AS [SupplierID], 
    [Project2].[CategoryID1] AS [CategoryID1], 
    [Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Project2].[UnitPrice] AS [UnitPrice], 
    [Project2].[UnitsInStock] AS [UnitsInStock], 
    [Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Project2].[ReorderLevel] AS [ReorderLevel], 
    [Project2].[Discontinued] AS [Discontinued]
    FROM ( SELECT 
        [Distinct1].[CategoryID] AS [CategoryID], 
        1 AS [C1], 
        [Extent2].[ProductID] AS [ProductID], 
        [Extent2].[ProductName] AS [ProductName], 
        [Extent2].[SupplierID] AS [SupplierID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], 
        [Extent2].[UnitPrice] AS [UnitPrice], 
        [Extent2].[UnitsInStock] AS [UnitsInStock], 
        [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], 
        [Extent2].[ReorderLevel] AS [ReorderLevel], 
        [Extent2].[Discontinued] AS [Discontinued], 
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[CategoryID] AS [CategoryID]
            FROM [dbo].[Products] AS [Extent1] ) AS [Distinct1]
        LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON ([Distinct1].[CategoryID] = [Extent2].[CategoryID]) OR (([Distinct1].[CategoryID] IS NULL) AND ([Extent2].[CategoryID] IS NULL))
    )  AS [Project2]
    ORDER BY [Project2].[CategoryID] ASC, [Project2].[C2] ASC

执行GroupBy得到的序列中包含的元素类型为IGrouping<TKey, T>,其Key属性代表了分组时使用的关键值,遍历IGrouping<TKey, T>元素可以读取到每一个T类型。在此示例中,对应的元素类型为IGrouping<int, Products>,其Key属性即为类别ID,遍历它可以读取到每一个产品对象。

六、串联操作符

串联是一个将两个集合联接在一起的过程。在LINQ中,这个过程通过Concat操作符来实现。

在下面的示例中,将会把类别名称串联在产品名称之后:

//方法语法
            var q =
                db.Products
                .Select(p => p.ProductName)
                .Concat
                    db.Categories.Select(c => c.CategoryName)
                .ToList();

生成的sql:

SELECT 
    [UnionAll1].[ProductName] AS [C1]
    FROM  (SELECT 
        [Extent1].[ProductName] AS [ProductName]
        FROM [dbo].[Products] AS [Extent1]
    UNION ALL
        SELECT 
        [Extent2].[CategoryName] AS [CategoryName]
        FROM [dbo].[Categories] AS [Extent2]) AS [UnionAll1]

返回结果77+8=85
在这里插入图片描述

原文链接:
https://blog.csdn.net/WuLex 本文是C++0x系列的第四篇,主要是内容是C++0x中新增的lambda表达式, function对象和bind机制。之所以把这三块放在一起讲,是因为这三块之间有着非常密切的关系,通过对比学习,加深对这部分内容的理解。在开始之间,首先要讲一个概念,closure(闭包),这个概念是理解lambda的基础。下面我们来看看wikipedia上对于计算机领域的closure的定义:   A closure (also lexical closure, function closure or function value) is a function together with   a refer (一)输入参数 在Lambda表达式中,输入参数是Lambda运算符的左边部分。它包含参数的数量可以为0、1或者多个。只有当输入参数为1时,Lambda表达式左边的一对小括弧才可以省略。输入参数的数量大于或者等于2时,Lambda表达式左边的一对小括弧中的多个参数质检使用逗号(,)分割。 下面创建一个Lambda表达式,它的输入参数的数量为0.该表达式将显示“This is a La List<T_TRA_OrderOptHIstory> result = new List<T_TRA_OrderOptHIstory>(); var statusSet = GetOrderStatusList().Where(o => o.InsideCode == state && o.S... https://www.jianshu.com/p/ef2eb256840d https://www.cnblogs.com/linlinismine/p/9295701.html forkJoinlambda语句中也有所应用,是在parallelStream这个面有应用 1、fork/join的设计思路 了解一个框架的第一件事,就是先了解别人的设计思路! fork/join大体的... List<studentVo> listStudent = (from tbStudent in myModels.PW_Student join tbAcademe in myModels.SYS_Academe on tbStudent.AcademeID equals tbAcademe.AcademeID join tbSpecialty in my... CustomerFollowup.Where(a => a.IsDelete == false && a.CustomerID == request.CustomerID). GroupJoin(Org, a => a.CreateOrgId, b => b.Id, (a, b) => new { Followup = a, Org = b }). // 初始化员工信息 var persons = new[] { new Person { Id = 1, Name = "张三" }, new Person { Id = 2, Name = "李四" }, new Person { Id = 3,... Category => Category.CategoryId, Product => Product.CategoryId, (x, y) => new { Category = x, Products = y }) .S... 之前lambda用的不多,但是lambda表达式确实很好用,今天写了个示例,简单的用一下Join。 新建三个类,User和Phone还有一个查询的结果UserDto class User public int Id { get; set; } public string Name { get; set; } public... 【java】Maybatis-Plus 数据库查询 lambdaQuery和mapper中EQ、NE、GT、LT、GE、LE、select、like、in、leftjoin的用法及详解 1、读取用户和部门两个表的左连接: var sg = db.Users.GroupJoin(db.Departments, u => u.DepartmentId, d => d.DepartmentId, (u,d) => new { u, d }).Select(o=>o).ToList(); 注意:上面将返回所用用户信息和对应的部门信息(即用户部门ID信息缺少,那么用户列表也会显示) 2、读取指定返回列表字段的左连接信息: var GJoinList = db.Sys_Us 注意: 把上例代码groupjoin 换成join,.SelectMany整句删掉,即变成了inner join。是不同名的,所以lanmbda语句定义这俩表连接时也会有所区别,具体请看下面代码中的语句以及相关注释.注意:studentno字段拆分成了studentno1和studentno2联合主键。也拆分成了两个字段,且与上表。......