.NET Linq Join

Question

I have 2 tables in SQL.

Table 1 Step Id Step Name Table 2 Profile Id Step Id Completed 

I would like to return the following results, even if there is no match in table 2:

 Results Table1.Step Id Table1.Step Name Table2.Profile Id Table2.Completed 

The way I do this in SQL is as follows:

 select * from [Table 1] t1 left join [Table 2] t2 on t1.Step Id = t2.Step Id 

This leads to the expected results.

When I rewrite this in 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(); } 

It works, but as a JOIN, not a left join. I get only relevant results.

In addition, UserCompletion is an object that I return from this method.

I hit my head about this several times in a few days ... any help would be appreciated.

Answer 1

You can also try this (assuming us.Completed is logical):

 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 }; 

This will not turn into a union in sql, but the nested select statement looks something like this:

 select StepId, Headline, ProfileId, isnull((select top(1) Completed from userStep where StepId = Id), 0) as Completed from step 

Answer 2

Try something in the following lines:

 var q = from s in step join us in userStep on s.Id equals us.StepId into tmpjoin from x in tmpjoin.DefaultIfEmpty() select new UserCompletion { ... } 

Answer 3

Found.

It looks like I need to add a rating to an element that may be null.

I added the following to my selection

 Completed = (x == null) ? String.Empty : x.Completed