简单的T-SQL到LINQ [英] Simple T-SQL to LINQ
问题描述
我有一个简单的SQL语句如下：
I have a simple SQL statement as below:
Select s.StaffID, s.FirstName + ' ' + s.LastName as FullName from Staff s
left outer join CheckIn c on c.StaffID = s.StaffID
WHERE s.ContractStatusID=1 and c.CheckInID is null
SQL语句工作正常，现在我想为它编写一个LINQ查询，我尝试了这个，但这不是正确的方法：
The SQL statement works fine, now I want to write a LINQ query for it, I tried this but it is not the right way:
var db = new SREntities();
var all = from s in db.Staffs
join c in db.CheckIns on s.StaffID equals c.StaffID into ps
from c in ps.DefaultIfEmpty()
where s.ContractStatusID == 1 && c.CheckInID == null
select new { s.StaffID, FullName = s.FirstName + " " + s.LastName};
你能帮忙吗？我写了上面的LINQ查询，我只是想从Staff表中检索CheckIn表中没有任何记录的所有记录。
提前谢谢
我尝试过：
Can you please help me write the above LINQ query, I simply want to retrieve all records from Staff table who don't have any record in CheckIn table.
Thanks in advance
What I have tried:
var db = new SREntities();
var all = from s in db.Staffs
join c in db.CheckIns on s.StaffID equals c.StaffID into ps
from c in ps.DefaultIfEmpty()
where s.ContractStatusID == 1 && c.CheckInID == null
select new { s.StaffID, FullName = s.FirstName + " " + s.LastName};
推荐答案
方法＃1
正如我在问题评论中提到的那样你需要使用除方法 [ ^ ]
Method #1
As i mentioned in the comment to the question you need to use Except method[^]
var db = new SREntities();
var allCheckedIn = from s in db.Staffs
join c in db.CheckIns on s.StaffID equals c.StaffID
select s;
var NotCheckedInOnly = (from s in db.Staffs).Except(allCheckedIn);
欲了解更多详情，请参阅：
支持和不支持的LINQ方法（LINQ to Entities） [ ^ ]
实体的NOT IN子句 [ ^ ]
方法＃2
实现这一目标的另一种方法是使用
Wher e + 任何：
For further details, please see:
Supported and Unsupported LINQ Methods (LINQ to Entities)[^]
NOT IN clause for Entities[^]
Method #2
Another way to achieve that is to use
Where + Any:var result = db.Staffs.Where(s=>db.CheckIns.Any(c=>c.StaffID!=s.StaffID));
试试！
祝您好运！
Try!
Good luck!
Maciej的回复没有任何问题，但我认为解释您的示例中出现的问题可能有助于将来。
您的LINQ示例非常接近 - 这是正确的查询：
There's nothing wrong with Maciej's response but I think explaining what went wrong in your example might help in the future.
Your LINQ example is very close - here's the proper query:
var query = from staff in db.Staffs
join checkIn in db.CheckIns on staff.StaffID equals checkIn.StaffID
into result
from r in result.DefaultIfEmpty()
where staff.ContractStatusID == 1 && (r == null || r.CheckIn == null)
select new
{
staff.StaffID,
FullName = staff.FirstName + " " + staff.LastName
};
进行群组加入时，结果列表即使为空也始终存在。通常，在迭代结果集时会跳过这些空结果。当您使用 DefaultIfEmpty（）时，您将这些空列表设置为其默认值，以便始终与左表（左外连接）配对。 db.CheckIns 的行是引用类型，因此它们的默认值为 null 。这意味着 where ... c.CheckIn == null 导致问题，因为您无法访问 null.CheckIn 。
要处理A）结果为空且B）CheckIn值为空的两种情况，你需要对<$ c进行短路检查$ c> c == null 。这将捕获结果列表为空的情况之前您尝试访问CheckIn属性以检查它是否为空。
编辑：为了更好地解释组连接，它的工作方式如下所示，左表中的每一行都与其中一行相关联：
When doing a group join the results list always exists even if empty. Normally these empty results are skipped when iterating over the result set. When you use DefaultIfEmpty() you are setting these empty lists to their default in order to always have a pairing with the left table (left outer join). The rows for db.CheckIns are reference types though so their default is null. This means that where ... c.CheckIn == null is causing the problem since you cannot access null.CheckIn.
To handle both cases where A) the results are empty and B) the CheckIn value is null, you need to do a short-circuit check on c == null. This will catch the case where the result list is empty before you attempt to access the CheckIn property to check if that is null.
To better explain group join, it works like below where each row in the left table is associated with one of these:
//Results returned
resultList = new List<T>(); resultList.Add(result1); resultList.Add(result2); //etc...
//No results returned
resultList = null;
//DefaultIfEmpty
resultList = new resultList<T>(); resultList.Add(default(T));
这篇关于简单的T-SQL到LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
