How can I code a LINQ query with a count and a left join?

Question

I have this code:

public class DeckOccurrences
{
    public string DeckGuid { get; set; }
    public int Count { get; set; }
}

public class Deck
{
    public string DeckGuid { get; set; } 
    public string Name     { get; set; }  
}

public class DeckSource
{
    public string DeckGuid { get; set; } 
    public string Name     { get; set; }  
}

var decks = App.EF.Db2.Deck.ToList();
var decksources = App.EF.Db2.DeckSource.ToList();

Previously I had this query but now I would like to do it using LINQ:

select  d.deckguid, count(de.deckguid) as Count 
from decksource d 
left join deck de on d.deckguid = de.deckguid group by d.deckguid

Can someone help by giving me some advice on where I should start with trying to do a query like this?

Update showing code used:

        List<DeckOccurrences> res = (from d in App.EF.Db2.DeckSource
                                     join de in App.EF.Db2.Deck on d.DeckSourceId equals de.DeckGuid into j1
                                     from j2 in j1.DefaultIfEmpty()
                                     group j2 by d.DeckSourceId into grouped
                                     select new DeckOccurrences()
                                     {
                                         DeckGuid = grouped.Key,
                                         Count = grouped.Count(t => t.DeckGuid != null)
                                     }).ToList();

If you have navigation properties then `context.DeckSource.Select(d => new { d.deckguid, Count = d.Decks.Count()});` should work. — juharr, Sep 09 '20 at 15:12
But how do I handle the left join deck de on d.deckguid = de.deckguid group by d.deckguid — Alan2, Sep 09 '20 at 15:14
I'm sorry, but there's no relationship between the two tables. I just added them to the question to show you. It's just a list of decks and another similar list called decksource. Possibly you are correct but can you have a look at the classes I just added. Sorry but I'm not so familiar with how Linq can work here. — Alan2, Sep 09 '20 at 15:18
Maybe this question will help you with how to do left joins https://stackoverflow.com/questions/3404975/left-outer-join-in-linq The trick is to use `DefaultIfEmpty` — juharr, Sep 09 '20 at 15:21

Seabizkit · Accepted Answer · 2020-09-09T16:00:03.713

like the first hit off SO search on google

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, Count = grouped.Count(t=>t.ChildId != null) }

soo something like

from d in decksource
join de in deck on d.deckguid equals de.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by d.deckguid into grouped
select new { 
    deckguid = grouped.Key,
    Count = grouped.Count(t=>t.deckguid != null) 
}

soo

var res = (from d in App.EF.Db2.Decksource
        join de in App.EF.Db2.Deck on d.deckguid equals de.ChildParentId into j1
        from j2 in j1.DefaultIfEmpty()
        group j2 by d.deckguid into grouped
        select new { 
            deckguid = grouped.Key,
            Count = grouped.Count(t=>t.deckguid != null) 
        }).ToList()

When running this I get an error:.Count(t => t.DeckGuid != null)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). — Alan2, Sep 09 '20 at 16:12
post ur whole method as an update to ur question which is giving you this. — Seabizkit, Sep 09 '20 at 16:18

How can I code a LINQ query with a count and a left join?

1 Answers1