.NET的LINQ加入 [英] .NET Linq Join
问题描述
我在SQL 2表中。
Table 1
Step Id
Step Name
Table 2
Profile Id
Step Id
Completed
我想返回以下结果即使没有在表2相匹配:
I would like to return the following results even if there is not match in table 2:
Results
Table1.Step Id
Table1.Step Name
Table2.Profile Id
Table2.Completed
我在SQL这样做的方法如下:
The way I am doing this in SQL is the following:
select * from [Table 1] t1
left join [Table 2] t2
on t1.Step Id = t2.Step Id
这会产生我所期望的结果。
This produces the results I expect.
当我翻译成LINQ这样的:
When I translate this into linq:
public static List<UserCompletion> GetStepCompletion(string category, string profileid) {
List<Step> step = GetSteps(category);
List<UserStep> userStep = GetUserSteps(category, profileId);
var q = from s in step
join us in userStep
on s.Id equals us.StepId
select new UserCompletion
{
StepId = s.Id,
Headline = s.StepName,
ProfileId = us.ProfileId
Completed= us.Completed
};
return q.ToList();
}
它的工作原理,但像一个JOIN不是一个左连接。我只得到匹配的结果了。
It works but like a JOIN not a left join. I only get matching results back.
此外,UserCompletion的是,我从这个方法返回一个对象。
Also, UserCompletion is an object that I return from this method.
我一直在敲打我的脑袋上这几天...任何帮助,将不胜感激。
I have been banging my head on this for a few days...any help would be appreciated.
推荐答案
您也可以尝试(假设us.Completed是布尔):
You could also try this (assuming us.Completed is boolean):
var q = from s in step
let us = (from i in userStep where s.Id = i.StepId).SingleOrDefault()
select new UserCompletion
{
StepId = s.Id,
Headline = s.StepName,
ProfileId = us.ProfileId
Completed = us == null ? false : us.Completed
};
这不会变成一个加入SQL,而是一个嵌套查询语句是这样的:
This won't turn into a join in sql, but a nested select statement something like this:
select
StepId, Headline, ProfileId,
isnull((select top(1) Completed from userStep where StepId = Id), 0) as Completed
from step
这篇关于.NET的LINQ加入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!