LINQ LEFT JOIN to another LEFT JOIN

Question

I am pretty green with LINQ and brand new to converting LEFT JOINs. I wrote a query in SQL (which works fine) and I am trying to get it into LINQ. I am missing something, but can't see it. The SQL is:

DECLARE @Version int = 1
SELECT Q.WebFormTemplateQuestionID, Lay.*
FROM WebFormTemplates WFT
INNER JOIN WebFormTemplateQuestions Q ON Q.WebFormTemplateID = WFT.WebFormTemplateID
LEFT JOIN WebFormTemplateLayoutHeaders Hdr ON Hdr.WebFormTemplateID = WFT.WebFormTemplateID and hdr.Version = @Version
LEFT JOIN WebFormTemplateLayouts Lay ON Lay.WebFormTemplateLayoutHeaderID = Hdr.WebFormTemplateLayoutHeaderID AND Lay.WebFormTemplateQuestionID = Q.WebFormTemplateQuestionID
WHERE WFT.WebFormTemplateID = 2 
ORDER BY Q.SortOrder

The LINQ I wrote is:

    Version = 1
    Dim q3 = From WFT In ctx.WebFormTemplates
        Join Q In ctx.WebFormTemplateQuestions On Q.WebFormTemplateID Equals WFT.WebFormTemplateID
        Group Join Hdr In ctx.WebFormTemplateLayoutHeaders On Hdr.WebFormTemplateID Equals WFT.WebFormTemplateID Into Hdr_join = Group
            From Hdr In Hdr_join.Where(Function(x) x.Version = Version).DefaultIfEmpty()
        Group Join Lay In ctx.WebFormTemplateLayouts On Lay.WebFormTemplateLayoutHeaderID Equals Hdr.WebFormTemplateLayoutHeaderID And Lay.WebFormTemplateQuestionID Equals Q.WebFormTemplateQuestionID Into Lay_join = Group
            From Lay In Lay_join.DefaultIfEmpty()
        Where WFT.WebFormTemplateID = 2

With this, I can break the code and in the immediate window get the expected values when I type:

?q3.FirstOrDefault().Hdr.Version

But for the Lay item I need to type:

?q3.FirstOrDefault().Lay.FirstOrDefault().QuestionAlign

to get the value. Why do I need to call FirstOrDefault the second time? What am I missing here? When I do a Select, the values are all null for Lay I assume because I don't have another FirstOrDefault someplace. Everything from Hdr, WFT, and Q work just fine. Can anyone explain what is going on?

You have a 2-D array x(,). FirstOrDefault gets index zero an array or returns nothing (an empty item). You need it twice once for each dimension. — jdweng, May 05 '15 at 06:12

score 0 · Answer 1 · answered May 05 '15 at 06:57

0

i have written your equivalent Linq query from the SQL in C#. Update accordingly for VB.

Version = 1
    Dim q3 = (from WFT In ctx.WebFormTemplates
        join Q In ctx.WebFormTemplateQuestions on Q.WebFormTemplateID equals WFT.WebFormTemplateID
        join Hdr In ctx.WebFormTemplateLayoutHeaders on Hdr.WebFormTemplateID equals WFT.WebFormTemplateID into Hdr_join 
        from Hdr In Hdr_join.Where(Function(x) x.Version = Version).DefaultIfEmpty()
        join Lay In ctx.WebFormTemplateLayouts on new { Lay.WebFormTemplateLayoutHeaderID,Lay.WebFormTemplateQuestionID } equals new {Hdr.WebFormTemplateLayoutHeaderID, Q.WebFormTemplateQuestionID}  into Lay_join
         from Lay In Lay_join.DefaultIfEmpty()
         where WFT.WebFormTemplateID = 2
         select new {Q.WebFormTemplateQuestionID, Lay});

answered May 05 '15 at 06:57

sudhansu63

6,025
4
39
52

Thanks for this. I tried converting that line to what I believe is the VB equivalent, ( Join Lay In ctx.WebFormTemplateLayouts On New With {Lay.WebFormTemplateLayoutHeaderID, Lay.WebFormTemplateQuestionID} Equals New With {Hdr.WebFormTemplateLayoutHeaderID, Q.WebFormTemplateQuestionID} Into Lay_join) but it didn't work. I added the word "group" in front which got rid of some of the errors, but still no go. I also tried variations of New with no luck. – JayTee May 05 '15 at 15:31
you need to match the type of the properties used in new {K=1} equals new {K=1}, here both K proerty are Int type. [Follow more details here](http://stackoverflow.com/questions/7664727/linq-join-with-multiple-conditions-in-on-clause) – sudhansu63 May 06 '15 at 04:05

LINQ LEFT JOIN to another LEFT JOIN

1 Answers1