Convert SQL to Linq left join with null

Question

How can I convert this SQL to linq correctly

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

I try this but it does not work

 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(); 

thank

Answer 1

You want to use .DefaultIfEmpty like this question .

 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; 

Here is a complete working example with some mock data objects:

 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); } } } 

Answer 2

try it

  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(); 

You can check this post on MSDN .

Hope this works for you.

Answer 3

Could you use instead?

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

Answer 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