.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屋！