[SQL] Linq에로 변환 SQL은 널 (null)에 가입 왼쪽

Linq에로 변환 SQL은 널 (null)에 가입 왼쪽

어떻게 LINQ에 제대로이 SQL을 변환 할 수 있습니다

select  t1.ProgramID
from Program t1 LEFT JOIN ProgramLocation t2 ON  t1.ProgramID = t2.ProgramID 
where t2.ProgramID IS NULL

나는 그것을 시도하지만 그것은 작동하지 않습니다

var progy = (
             from u in db.ProgramLocations join b in db.Programs
             on u.ProgramID equals b.ProgramID into yG 
             from y1 in yG.DefaultIfEmpty() 
             where u.ProgramID == null
             where u.ProgramID == null 
             select u.ProgramID
            ).ToList();

감사

해결법

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

   1.이 질문에 따라, .DefaultIfEmpty을 사용하고 싶습니다.

   이 질문에 따라, .DefaultIfEmpty을 사용하고 싶습니다.

   var query = from p in Programs
               join pl in ProgramLocations
                   on p.ProgramID equals pl.ProgramID into pp
               from pl in pp.DefaultIfEmpty()
               where pl == null
               select p;
   

   다음은 몇 가지 모의 데이터 개체와 전체, 작업 예이다 :

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   
   namespace LinqTest
   {
       class LinqProgram
       {
           public class Program
           {
               public int ProgramID { get; set; }
               public string ProgramName { get; set; }
           }
   
           public class ProgramLocation
           {
               public int ProgramLocationID { get; set; }
               public int ProgramID { get; set; }
               public string ProgramLocationName { get; set; }
           }
   
           public static List<Program> Programs = new List<Program>();
           public static List<ProgramLocation> ProgramLocations = new List<ProgramLocation>();
   
           static void Main(string[] args)
           {
               FillTestData();
   
               var query = from p in Programs
                           join pl in ProgramLocations
                               on p.ProgramID equals pl.ProgramID into pp
                           from pl in pp.DefaultIfEmpty()
                           where pl == null
                           select p;
   
               foreach (var r in query)
               {
                   Console.WriteLine("{0}: {1}", r.ProgramID, r.ProgramName);
               }
   
               Console.ReadLine();
           }
   
           private static void FillTestData()
           {
               var p = new Program()
               {
                   ProgramID = Programs.Count + 1,
                   ProgramName = "Scary Lesson"
               };
               var pl = new ProgramLocation()
               {
                   ProgramLocationID = ProgramLocations.Count + 1,
                   ProgramID = p.ProgramID,
                   ProgramLocationName = "Haunted House"
               };
               Programs.Add(p);
               ProgramLocations.Add(pl);
   
               p = new Program()
               {
                   ProgramID = Programs.Count + 1,
                   ProgramName = "Terrifying Teachings"
               };
   
               pl = new ProgramLocation()
               {
                   ProgramLocationID = ProgramLocations.Count + 1,
                   ProgramID = p.ProgramID,
                   ProgramLocationName = "Mystical Mansion"
               };
               Programs.Add(p);
               ProgramLocations.Add(pl);
   
               p = new Program()
               {
                   ProgramID = Programs.Count + 1,
                   ProgramName = "Unassociated Program"
               };
               Programs.Add(p);
           }
       }
   }
   

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

   2.이 시도

   이 시도

     var progy = (
            from u in db.ProgramLocations join b in db.Programs
            on u.ProgramID equals b.ProgramID into yG 
            from y1 in yG.DefaultIfEmpty() 
            where y1 == null
            select u.ProgramID
           ).ToList();
   

   당신은 MSDN에이 게시물을 확인할 수 있습니다.

   당신을 위해이 작품을 바랍니다.

3. ==============================

   3.대신 제외하고 사용할 수 있을까요?

   대신 제외하고 사용할 수 있을까요?

   var progy = (
     from u in db.ProgramLocations
     select u.ProgramID
   ).Except(from b in db.Programs select b.ProgramID);
   

4. ==============================

   4.

   SELECT pfa.PetID, pt.PetTypeDesc, pfa.petname, pf.PetOwner, pf.remarks, pat.AdoptedBy
       FROM dbo.PetForAdoption pfa
       JOIN dbo.PetAdoptionTran pat
       ON pfa.PetID = pat.PetID
       JOIN dbo.PetTypes pt 
       ON pfa.PetTypeID = pt.PetTypeID
       JOIN dbo.PetProfile pf
       ON pfa.PetID = pf.PetID
       ORDER BY pt.PetTypeDesc
   

from https://stackoverflow.com/questions/9171063/convert-sql-to-linq-left-join-with-null by cc-by-sa and MIT license