Converting SQL Query with Multiple Left Outer Joins to LINQ Query

Question

Struggling with converting a normal SQL Query to a LINQ Query that involves multiple LEFT OUTER JOINS..

Original SQL Query:

SELECT a.*
FROM Testers t
LEFT OUTER JOIN Users u ON u.TesterId = t.TesterId
LEFT OUTER JOIN ValidForms v ON v.DepartmentId = u.DepartmentId

LINQ Query Code is as below:

var x = (from t in Context.Testers.AsEnumerable()
         from u in Context.Users
                    .Where(a => a.TesterId == t.TesterId)
                    .DefaultIfEmpty()
                 from v in Context.ValidForms
                  .Where(b => b.DepartmentId == u.DepartmentId)
                    .DefaultIfEmpty()
                    Select new myEntity
                    {
                      col1 = t.col1,
                        col2 = t.col2
                    }).AsEnumerable()

return x.ToList();

Running the query, I am getting an error: Non-static method requires a target

Appreciate if someone could point out how to do the query properly in LINQ.

I also checked the SO question posted here, but I am unable to grasp the concept provided: SQL to Linq query with multiple left outer joins

Thanks.

Update: I got this from this SO question.

Yes, I did. Hence I got an example code, which for reasons beyond me at the moment throws the error. — Batuta, May 27 '15 at 20:10
The general approach is OK, the AsEnumerable's are bogus, why did you add them? Also since you didn't give details on the error the question is unanswerable. Closing. — usr, May 27 '15 at 21:57
There was no other details provided on the error. InnerException is just throwing null. — Batuta, May 27 '15 at 22:42

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

This is a good way to do it. If you follow that example, your code should look something like this:

var x = (from t in Context.Testers.AsEnumerable()
         join u in Context.Users on t.TesterId equals u.TesterId into group1
         from a in group1.DefaultIfEmpty()
         join v in Context.ValidForms on a.DepartmentId equals v.DepartmentId into group2
         from b in group2.DefaultIfEmpty()
         select new MyEntity {
             col1 = b.col1,
             col2 = b.col2
         }).AsEnumerable();

UPDATED

var x = (from t in Context.Testers.AsEnumerable()
         join u in Context.Users on t.TesterId equals u.TesterId 
         join v in Context.ValidForms on u.DepartmentId equals v.DepartmentId into group1
         from b in group1.DefaultIfEmpty()
         select new MyEntity {
             col1 = (b != null) ? b.col1 : null,
             col2 = (b != null) ? b.col2 : null
         }).AsEnumerable();

edited May 23 '17 at 12:06

Community

1
1

answered May 27 '15 at 18:08

Cory

783
5
12

In the lin group2.DefaultIfEmpty(), I am having this warning "An expression of type method group" is not allowed in subsequent from clause in a query expression. Type inference failed in the call to 'SelectMany' – Batuta May 27 '15 at 19:26
Will you please update your question with the code you are using now, as well as the error? – Cory May 27 '15 at 19:32
The issue I think I am getting is that in the second outer join to ValidForms, the a.DepartmentId is a null reference because, it was just being retrieved even without a value. Essentially, the 2 tables being joined does not contain any thing (as of yet), thus throwing a null reference exception error. – Batuta May 27 '15 at 19:48
In what line are you seeing the null reference exception? Will you please post the code you're using? Also, I have updated my example with something else you can try. – Cory May 27 '15 at 20:28
Followed the 1st sample code you've provided, and I was getting the error on the line on a.DepartmentId equals v.DepartmentId. I think part of the reason is that there is no record for the 2nd and 3rd tables. As we are making an outer join and the ID is being used as a comparator value. – Batuta May 27 '15 at 22:01

Converting SQL Query with Multiple Left Outer Joins to LINQ Query

1 Answers1