使用null将SQL转换为Linq左连接
使用null将SQL转换为Linq左连接
提问于 2012-02-07 12:35:49
如何正确地将此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();谢谢
回答 5
Stack Overflow用户
回答已采纳
发布于 2012-02-07 13:13:46
根据this question，您想要使用.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);
}
}
}Stack Overflow用户
发布于 2012-02-07 13:10:08
尝尝这个
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();你可以查看this post on MSDN。
希望这对你有用。
Stack Overflow用户
发布于 2012-02-07 13:13:30
你能用except代替吗？
var progy = (
from u in db.ProgramLocations
select u.ProgramID
).Except(from b in db.Programs select b.ProgramID);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9171063
复制相关文章
相似问题