概述:在这个代码片段中，我演示了如何按ID分组，其中Table1和Table2具有一对多关系。我集中在 Id, Field1和Field2。如果需要第三次查找表，并且需要左连接关系，那么子查询是有用的。 我展示了一个左连接分组和一个子查询linq。结果是等价的。

class MyView
{
public integer Id {get,set};
    public String Field1  {get;set;}
public String Field2 {get;set;}
    public String SubQueryName {get;set;}                           
}

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=
                                                   (from chv in _dbContext.Table3 where chv.Id==pg.Key.Id select chv.Field1).FirstOrDefault()
                                               }).ToListAsync<MyView>();


 Compared to using a Left Join and Group new

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                       join chv in _dbContext.Table3
                                                  on cii.Id equals chv.Id into lf_chv
                                                from chv in lf_chv.DefaultIfEmpty()

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2, chv.FieldValue}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=pg.Key.FieldValue
                                               }).ToListAsync<MyView>();

下面是一个例子，如果你需要连接2个以上的表:

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm 
     on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
     on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()

裁判:https://stackoverflow.com/a/17142392/2343

使用lambda表达式

db.Categories    
  .GroupJoin(db.Products,
      Category => Category.CategoryId,
      Product => Product.CategoryId,
      (x, y) => new { Category = x, Products = y })
  .SelectMany(
      xy => xy.Products.DefaultIfEmpty(),
      (x, y) => new { Category = x.Category, Product = y })
  .Select(s => new
  {
      CategoryName = s.Category.Name,     
      ProductName = s.Product.Name   
  });

我想补充的是，如果您获得MoreLinq扩展，现在支持同质和异构左连接

http://morelinq.github.io/2.8/ref/api/html/Overload_MoreLinq_MoreEnumerable_LeftJoin.htm

例子:

//Pretend a ClientCompany object and an Employee object both have a ClientCompanyID key on them

return DataContext.ClientCompany
    .LeftJoin(DataContext.Employees,                         //Table being joined
        company => company.ClientCompanyID,                  //First key
        employee => employee.ClientCompanyID,                //Second Key
        company => new {company, employee = (Employee)null}, //Result selector when there isn't a match
        (company, employee) => new { company, employee });   //Result selector when there is a match

编辑:

回想起来，这可能会起作用，但它将IQueryable转换为IEnumerable，因为morelinq不会将查询转换为SQL。

您可以使用如下所述的GroupJoin: https://stackoverflow.com/a/24273804/4251433

这将确保它仍然是一个IQueryable，以防您以后需要对它进行进一步的逻辑操作。

这是一种SQL语法，与用于内连接和左外连接的LINQ语法相比。 左外连接:

http://www.ozkary.com/2011/07/linq-to-entity-inner-and-left-joins.html

下面的示例在产品和类别之间进行组连接。这本质上是左连接。即使类别表为空，into表达式也会返回数据。要访问类别表的属性，现在必须通过在catList.DefaultIfEmpty()语句中添加from cl从可枚举结果中进行选择。

根据我对类似问题的回答，在这里:

Linq到SQL左外连接使用Lambda语法和连接2列(复合连接键)

获得代码在这里，或克隆我的github回购，并发挥!

查询:

        var petOwners =
            from person in People
            join pet in Pets
            on new
            {
                person.Id,
                person.Age,
            }
            equals new
            {
                pet.Id,
                Age = pet.Age * 2, // owner is twice age of pet
            }
            into pets
            from pet in pets.DefaultIfEmpty()
            select new PetOwner
            {
                Person = person,
                Pet = pet,
            };

λ:

        var petOwners = People.GroupJoin(
            Pets,
            person => new { person.Id, person.Age },
            pet => new { pet.Id, Age = pet.Age * 2 },
            (person, pet) => new
            {
                Person = person,
                Pets = pet,
            }).SelectMany(
            pet => pet.Pets.DefaultIfEmpty(),
            (people, pet) => new
            {
                people.Person,
                Pet = pet,
            });