LINQ to SQL多表左外连接

Question

我有一个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

- Ehsan Sajjad

Answer 1

以下是使用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

这个查询涉及到三个表的连接。你可以用同样的方式连接其他剩余的表。

- Sergey Berezovskiy

---

我没有理解你的分组点，为什么在选择之后要做到x，这是必要的吗？@lazyberezovsky - Ehsan Sajjad

如果之前的连接结果中有任何一个为null，那么我将会收到一个空引用异常。该如何解决这个问题？ - Badhon Jain

我有同样的问题。如果第一次的外连接结果为空值，那么第二次的外连接就会抛出一个空引用异常。 - jkruer01

我有与上述相同的问题^。如何检查第一个左连接对象的空条件？ - ProxyTech

与 SQL 中的外连接一样，在尝试使用任何字段之前，您需要检查是否存在空值。例如，以上的 m 可能为空，因此在 where 子句中，谓词 m.branchId == 6 应该有一个条件语句，例如 (m == null? 0: m.branchId) 或者使用 C# 6 中的新安全导航运算符 m?.branchId ?? 0。在为匿名结果对象的属性赋值时同样适用。 - Nick

Answer 2

这是带有空值检查的多个左连接示例（以防止空引用异常）。感谢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 = "Jack 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 / Jack 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
}

- Zoran Bosnjak