Convert SQL to Linq left join with null

How can I convert properly this SQL to linq

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

I try that but it not working

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

THANKS

You want to use .DefaultIfEmpty, as per 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's a full, 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);
		}
	}
}

Try this

  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.

Could you use except instead?

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

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

I had this issue now, and neat SQL is a must and got it working in a very optimised way.

Linq:

var recs=from programs in db.Programs
         from locations in db.ProgramLocations.where(x=> x.ProgramID == programs.ProgramID).DefaultIfEmpty()
         where locations.ProgramID == null  //Compiler creates a warning because int will never be null, but it works and creates outer left join
         select programs.ProgramID;

Resulting SQL:

SELECT 
"Extent1"."ProgramID" AS "ProgramID"
FROM "DBO"."Program" "Extent1"
LEFT OUTER JOIN "DBO"."ProgramLocation" "Extent2" ON "Extent2"."ProgramID" = "Extent1"."ProgramID"
WHERE ("Extent2"."ProgramID" IS NULL)

This also work if you have more than one join condition And also work for more than one left join

ps. I am using EF with Oracle, so not sure if this will be slightly different on the SQLServer provider for E