C# LINQ to SQL 多表左外连接

LINQ to SQL multiple tables left outer join

提问by Ehsan Sajjad

I have this query in SQL, and I want it to implement it in LINQ using Entity Framework, but how can I apply multiple tables left outer joins?

我在 SQL 中有这个查询，我希望它使用Entity Framework在 LINQ 中实现它，但是如何应用多个表的左外连接？

SELECT d.bookingid,
       d.labid,
       d.processid,
       p.prid,
       p.prno,
       d.DestinationBranchID,
       d.SendStatus
FROM   dc_tpatient_bookingd d
       LEFT OUTER JOIN dc_tpatient_bookingm m ON d.bookingid = m.bookingid
       LEFT OUTER JOIN dc_tpatient p ON p.prid = m.prid
       LEFT OUTER JOIN dc_tp_test t ON d.testid = t.testid
       LEFT OUTER JOIN dc_tp_groupm gm ON t.groupid = gm.groupid
       LEFT OUTER JOIN dc_tpanel pn ON m.panelid = pn.panelid
       LEFT OUTER JOIN dc_tp_organization og ON og.orgid = m.clientid
       LEFT OUTER JOIN dc_tp_ward w ON w.wardid = m.wardid
       LEFT OUTER JOIN dc_tp_branch tb ON tb.BranchID = m.BranchID
WHERE  d.processid = 6
       AND ( ( m.branchId = 1
               AND d.DestinationBranchID = 0 )
              OR ( d.DestinationBranchID = 1
                   AND d.sendstatus = 'R' ) )
       AND d.testid IN (SELECT testid
                        FROM   dc_tp_test
                        WHERE  subdepartmentid = 13)
       AND date_format(m.enteredon, '%Y/%m/%d') BETWEEN '2013/06/15' AND '2013/06/15'
GROUP  BY m.bookingid
ORDER  BY d.priority DESC,
       m.bookingid ASC

采纳答案by Sergey Berezovskiy

Here is how left outer joins are implemented with LINQ. You should use GroupJoin(join...intosyntax):

下面是如何使用 LINQ 实现左外连接。您应该使用GroupJoin（join...into语法）：

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()
// ... other joins here
where d.processid == 6 &&
      ((m.branchId == 1 && d.DestinationBranchID == 0) ||
       (d.DestinationBranchID == 1 && d.sendstatus == "R"))
// ... other conditions here
orderby d.priority descending, m.bookingid
select new {
   d.bookingid,
   d.labid,
   d.processid,
   p.prid,
   p.prno,
   m.bookingid // need for grouping
} into x
group x by x.bookingid into g
select g

This query joins three tables. You can join the rest of the tables the same way.

此查询连接三个表。您可以以相同的方式连接其余的表。

回答by Zoran Bosnjak

This is multiple left join sample with null checking (preventing null reference exception). Thnx to Nick!

这是带有空检查的多个左连接示例（防止空引用异常）。谢谢尼克！

void Main()
{
    var data = DataAccess.GetData();
    var res = 
        from m in data.Movies
        join ma in data.MovieActor on m.Id equals ma.MovieId into mma
        from ma in mma.DefaultIfEmpty()
        join p in data.People on (ma == null ? 0 : ma.ActorId) equals p.Id into pma
        from p in pma.DefaultIfEmpty()
        orderby m.Name
        select new {
            Movie = m.Name,
            Actor = p != null ? p.Name + " as " + ma.Name : ""
        };
    foreach (var el in res)
    {
        Console.WriteLine($"{el.Movie} - {el.Actor}");
    }
}

public class DataAccess
{
    public static Data GetData()
    {
        var list = new Data
        {
            Movies = new List<Movie>{
             new Movie{ Id = 1, Name= "Raiders of the Lost Ark", Year = 1981},
             new Movie{ Id = 2, Name= "Blade Runner", Year = 1982},
             new Movie{ Id = 3, Name= "Star Wars: Episode IV - A New Hope", Year = 1977},
             new Movie{ Id = 4, Name= "Total Recall", Year = 1990},
             new Movie{ Id = 5, Name= "The Fugitive", Year = 1993},
             new Movie{ Id = 6, Name= "Men in Black", Year = 1997},
             new Movie{ Id = 7, Name= "U.S. Marshals", Year = 1998},
             new Movie{ Id = 8, Name= "Batman", Year = 1989},
             new Movie{ Id = 9, Name= "A Few Good Men", Year = 1992},
             new Movie{ Id = 10, Name= "Tropic Thunder", Year = 2008},
             new Movie{ Id = 11, Name= "Minority Report", Year = 2002},
             new Movie{ Id = 12, Name= "The Fifth Element", Year = 1997},
             new Movie{ Id = 13, Name= "District 9", Year = 2009},
             new Movie{ Id = 14, Name= "12 Monkeys", Year = 1995},
            },
            People = new List<Person>{
                new Person{ Id = 1, Name = "Harrison Ford"},
                new Person{ Id = 2, Name = "Tommy Lee Jones"},
                new Person{ Id = 3, Name = "Will Smith"},
                new Person{ Id = 4, Name = "Michael Keaton"},
                new Person{ Id = 5, Name = "Will Smith"},
                new Person{ Id = 6, Name = "Hyman Nicholson"},
                new Person{ Id = 7, Name = "Tom Cruise"}
            },
            MovieActor = new List<MovieActor>{
                new MovieActor{ MovieId = 1, ActorId = 1, Name = "Indy"},
                new MovieActor{ MovieId = 2, ActorId = 1, Name = "Rick Deckard"},
                new MovieActor{ MovieId = 3, ActorId = 1, Name = "Han Solo"},
                new MovieActor{ MovieId = 5, ActorId = 1, Name = "Dr. Richard Kimble"},
                new MovieActor{ MovieId = 5, ActorId = 2, Name = "Samuel Gerard"},
                new MovieActor{ MovieId = 6, ActorId = 2, Name = "Kay"},
                new MovieActor{ MovieId = 7, ActorId = 2, Name = "Samuel Gerard"},
                new MovieActor{ MovieId = 6, ActorId = 3, Name = "Jay"},
                new MovieActor{ MovieId = 8, ActorId = 4, Name = "Batman / Bruce Wayne"},
                new MovieActor{ MovieId = 8, ActorId = 6, Name = "Joker / Hyman Napier"},
                new MovieActor{ MovieId = 9, ActorId = 6, Name = "Col. Nathan R. Jessep"},
                new MovieActor{ MovieId = 9, ActorId = 7, Name = "Lt. Daniel Kaffee"},
                new MovieActor{ MovieId = 10, ActorId = 7, Name = "Les Grossman"},
                new MovieActor{ MovieId = 11, ActorId = 7, Name = "Chief John Anderton"}
            }

        };
        return list;
    }
}

public class Data
{
    public List<Movie> Movies = new List<Movie>();
    public List<Person> People = new List<Person>();
    public List<MovieActor> MovieActor = new List<MovieActor>();
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Movie
{
    public int Id { get; set; }
    public string Name {get; set;}
    public int Year { get; set; }
}

public class MovieActor
{
    public int MovieId { get; set; }
    public int ActorId { get; set; }
    public string Name { get; set; } // appearance as
}