left join in Linq query

Question

I'm trying to do a left join, not an inner join in a linq query. I have found answers related to using DefaultIfEmpty() however I can't seem to make it 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 only gives me the rows from table 1 that have a corresponding record in table 2. I would like every record in table 1 populated into MyObject and a list of 0-n corresponding records listed in manySubObjects for each MyObject.

UPDATE: I tried the answer to the question that is a "possible duplicate", mentioned below. I now have the following code that does give me one record for each item in Table1 even if there is no Table2 record.

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 record in table2 I get "manySubObject" as a list with one "SubObject" in it with all null values for the properties of "SubObject". What I really want is "manySubObjects" to be null if there is no values in table2.

Answer 1

In reply to your update, to create the null listing, you can do a ternary in your assignment of 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 tries to do what you're attempting. https://dotnetfiddle.net/kGJVjE
• Here is a subsequent dotnetfiddle based on your comments. https://dotnetfiddle.net/h2xd9O

In reply to your comments, the above works with Linq to Objects but NOT with Linq to SQL. Linq to SQL will complain that it, "Could not translate expression ... into SQL and could not treat as a local expression." That's because Linq cannot translate the custom new SubObject constructor into SQL. To do that, you have to write more code to support translation into SQL. See Custom Method in LINQ to SQL query and this article.

I think we've sufficiently answered your original question about left joins. Consider asking a new question about using custom methods/constructors in Linq to SQL queries.

Answer 2

I think the desired Result that you want can be given by using GroupJoin()

The code Below will produce a structure like so

Field1, Field2, List < SubObject > null if empty

Sample code

 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 whole query, it will help solve the issue.

** UPDATE BIS **

 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()

Should 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 a First, if it doesn't the group doesn't have any records so the Action.Name for a Time Stamp has no emails to send. If the First isn't null the loop throw the group elements and create a list of Email,

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 project in-memory to your non-EF-model type.