Left join in Linq query

Question

I am trying to make a left join, not an inner join in linq query. I found answers related to using DefaultIfEmpty() , however I cannot get it to work. The following is the linq query:

 from a in dc.Table1 join e in dc.Table2 on a.Table1_id equals e.Table2_id where a.Table1_id == id orderby a.sort descending group e by new { a.Field1, a.Field2 } into ga select new MyObject { field1= ga.Key.Field1, field2= ga.Key.Field2, manySubObjects = (from g in ga select new SubObject{ fielda= g.fielda, fieldb= g.fieldb }).ToList() }).ToList(); 

The query gives me only the rows from table 1 that have the corresponding entry in table 2. I would like each entry in table 1 to be populated in MyObject and a list of 0-n corresponding entries listed in many SubObjects for each MyObject.

UPDATE: I tried to answer the question, which is the “possible duplicate” mentioned below. I now have the following code that gives me one entry for each item in table1, even if there is no entry in table 2.

 from a in dc.Table1 join e in dc.Table2 on a.Table1_id equals e.Table2_id into j1 from j2 in j1.DefaultIfEmpty() where a.Table1_id == id orderby a.sort descending group j2 by new { a.Field1, a.Field2 } into ga select new MyObject { field1= ga.Key.Field1, field2= ga.Key.Field2, manySubObjects = (from g in ga select new SubObject{ fielda= g.fielda, fieldb= g.fieldb }).ToList() }).ToList(); 

However, with this code, when there is no entry in table 2, I get "manySubObject" as a list with one "SubObject" in it with all the null values ​​for the "SubObject" properties. I really want "manySubObjects" to be empty if there are no values ​​in the table.

Answer 1

In response to your update , to create a null enumeration, you can make a triple in your manySubObjects .

 select new MyObject { field1= ga.Key.Field1, field2= ga.Key.Field2, manySubObjects = (from g in ga select g).FirstOrDefaut() == null ? null : (from g in ga select new SubObject { fielda= g.fielda, fieldb= g.fieldb }).ToList() }).ToList(); 

• Here is a dotnetfiddle that is trying to do what you are trying to do. https://dotnetfiddle.net/kGJVjE
• Here is a follow-up dotnetfiddle based on your comments. https://dotnetfiddle.net/h2xd9O

In response to your comments, the above works with Linq for objects, but NOT with Linq to SQL. Linq to SQL will complain that it "cannot convert the expression ... to SQL and cannot be considered as a local expression." This is because Linq cannot translate the custom new SubObject into SQL. To do this, you need to write more code to support SQL translation. See Custom Method in LINQ to SQL Query and in this article .

I think we have answered enough of your original question about left associations. Try asking a new question about using custom methods / constructors in Linq to SQL queries.

Answer 2

I think the desired result you want can be set using GroupJoin ()

The code below will create such a structure

Field1, Field2, List <SubObject> null if empty

Code example

  var query = dc.Table1.Where(x => Table1_id == id).OrderBy(x => x.sort) .GroupJoin(dc.Table2, (table1 => table1.Table1_id), (table2 => table2.Table2_id), (table1, table2) => new MyObject { field1 = table1.Field1, field2 = table1.Field2, manySubObjects = (table2.Count() > 0) ? (from t in table2 select new SubObject { fielda = t.fielda, fieldb = t.fieldb}).ToList() : null }).ToList(); 

Dotnetfiddle link

UPDATE

From your comment I saw this

 ga.Select(g = > new SubObject(){fielda = g.fielda, fieldb = g.fieldb}) 

I think it should be (depends on how ga is built)

 ga.Select(g => new SubObject {fielda = g.fielda, fieldb = g.fieldb}) 

Please update your question with the entire request, this will help solve the problem.

** BIS UPDATE **

  sentEmails = //ga.Count() < 1 ? null : //(from g in ga select g).FirstOrDefault() == null ? null : (from g in ga select new Email{ email_to = g.email_to, email_from = g.email_from, email_cc = g.email_cc, email_bcc = g.email_bcc, email_subject = g.email_subject, email_body = g.email_body }).ToList() 

Must be:

  sentEmails = //ga.Count() < 1 ? null : ((from g in ga select g).FirstOrDefault() == null) ? null : (from g in ga select new Email{ email_to = g.email_to, email_from = g.email_from, email_cc = g.email_cc, email_bcc = g.email_bcc, email_subject = g.email_subject, email_body = g.email_body }).ToList() 

Checks if the group has First, if the group has no entries, so for Action.Name for Time Stamp there are no emails to send. If First is not null, the loop throws the elements of the group and creates an Email list,

Answer 3

 var results = ( // Use from, from like so for the left join: from a in dc.Table1 from e in dc.Table2 // Join condition goes here .Where(a.Id == e.Id) // This is for the left join .DefaultIfEmpty() // Non-join conditions here where a.Id == id // Then group group by new { a.Field1, a.Field2 } ).Select(g => // Sort items within groups g.OrderBy(item => item.sortField) // Project required data only from each item .Select(item => new { item.FieldA, item.FieldB })) // Bring into memory .ToList(); 

Then execute the project in memory for your type other than EF.