[SQL] SQL 여러 테이블에 LINQ는 외부 조인 왼쪽

SQL 여러 테이블에 LINQ는 외부 조인 왼쪽

나는 SQL에서이 쿼리를 가지고 있고, 나는 그것이 엔티티 프레임 워크를 사용하여 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

해결법

1. ==============================

   1.여기에 LINQ 구현 조인 방법 왼쪽 외부입니다. 당신은 GroupJoin을 (구문에 ... 가입)를 사용한다 :

   여기에 LINQ 구현 조인 방법 왼쪽 외부입니다. 당신은 GroupJoin을 (구문에 ... 가입)를 사용한다 :

   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
   

   이 쿼리는 세 개의 테이블을 조인. 당신은 테이블의 나머지 같은 방법으로 가입 할 수 있습니다.

2. ==============================

   2.이것은 여러 왼쪽 널 검사 (NULL 참조 예외 방지) 샘플에 참여한다. 닉에 Thnx!

   이것은 여러 왼쪽 널 검사 (NULL 참조 예외 방지) 샘플에 참여한다. 닉에 Thnx!

   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
   }
   

from https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join by cc-by-sa and MIT license