使用 null 将 SQL 转换为 Linq 左连接

如何正确地将这个 SQL 转换为 linq

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

谢谢

78673 次浏览
小开

试试这个

  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 上的这篇文章。

希望这对你有用。

小开

你能用“除外”代替吗？

var progy = (
from u in db.ProgramLocations
select u.ProgramID
).Except(from b in db.Programs select b.ProgramID);
小开
最佳答案

您希望按照 这个问题使用 .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);
}
}
}
小开 
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
小开

我现在有这个问题，整洁的 SQL 是必须的，并得到它工作在一个非常优化的方式。

林克:

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;

结果 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)

这也工作，如果你有多个连接条件，也为多个左连接

注意。我在 Oracle 中使用 EF，所以不确定这在 E 的 SQLServer 提供程序上是否有些不同


提交答案