Convert SQL to Linq left join with null

Question

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

Tried http://www.linqpad.net/? – Numan Feb 07 '12 at 05:02 — Numan, Feb 07 '12 at 05:02
will this convert SQL to LINQ – COLD TOLD Feb 07 '12 at 05:05 — COLD TOLD, Feb 07 '12 at 05:05

score 117 · Accepted Answer · edited May 23 '17 at 10:31

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

score 3 · Answer 2 · answered Feb 07 '12 at 05:10

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.

score 2 · Answer 3 · answered Jul 21 '21 at 17:29

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

This is what I am interested in "Compiler creates a warning because int will never be null, but it works and creates outer left join". Thanks. — Sahil M., Dec 23 '22 at 10:45

score 2 · Answer 4 · answered Feb 07 '12 at 05:13

2

Could you use except instead?

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

answered Feb 07 '12 at 05:13

Abe Miessler

82,532
99
305
486

1

This would only work if the ProgramID were the only value you needed to retrieve. – devlord Mar 07 '13 at 17:35

score 0 · Answer 5 · answered Jun 22 '17 at 13:02

0

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

answered Jun 22 '17 at 13:02

Tapan

1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. Please read this [how-to-answer](http://stackoverflow.com/help/how-to-answer) for providing quality answer. – thewaywewere Jun 22 '17 at 13:22

Convert SQL to Linq left join with null

5 Answers5

Linked