LINQ to SQL - multiple left join, group by, left join on same table, count

Question

I have a SQL query that is pretty simple, but turns out to be a nightmare to transform into LINQ to SQL (or LINQ to Entity). I've been reading lots of questions and articles and I just cannot manage to make it work. Here is the SQL query (Items table 1-N to Operations table, Items has a ParentItemId column):

select 
      i.Id
    , i.Code
    , count(oAdd.ItemId) "Added"
    , count(oRem.ItemId) "Removed"
    , count(iChild.Id) "Existing"

from items i

left join operations oAdd on oAdd.ItemId = i.Id and oAdd.OperationTypeId = 10
left join operations oRem on oRem.ItemId = i.Id and oRem.OperationTypeId = 20
left join items iChild on iChild.ParentItemId = i.Id

group by 
      i.Id
    , i.Code

Now after all the reseach and multiple attemps, I came up with the following code, which compiles but throws a EntityCommandCompilation exception ("The nested query is not supported. Operation1='GroupBy' Operation2='MultiStreamNest'"):

var query =

    from item in dbContext.Items

    join oAdd in dbContext.Operations on item.Id equals oAdd.ItemId into oAddJoin
    join oRem in dbContext.Operations on item.Id equals oRem.ItemId into oRemJoin
    join oChild in dbContext.Items on item.Id equals oChild.ParentItemId into oChildJoin

    from oAddLeftJoin in oAddJoin.Where(o => o.OperationTypeId == (int)OperationTypes.AddTo).DefaultIfEmpty()
    from oRemLeftJoin in oRemJoin.Where(o => o.OperationTypeId == (int)OperationTypes.RemoveFrom).DefaultIfEmpty()
    from oChildLeftJoin in oChildJoin.DefaultIfEmpty()

    group oChildLeftJoin by new
    {
        ItemId = item.Id,
        ItemCode = item.Code,
        Added = oAddJoin.Count(),
        Removed = oRemJoin.Count(),
        Existing = oChildJoin.Count()
    }
    into oChildLeftJoinGrouped

    select new
    {
        oChildLeftJoinGrouped.Key.ItemId,
        oChildLeftJoinGrouped.Key.Added,
        oChildLeftJoinGrouped.Key.Removed,
        oChildLeftJoinGrouped.Key.Existing
    };

var summaryList = query.ToList();

I've also tried not grouping, not "joining into" but selecting from where instead, not putting the count in the group by but in the select new, not grouping by added/removed/existing. Nothing works, and the more I try the less I feel I understand what this is about. It worked only once but without the "Existing" count (count of child items, i.e. join on the same table - see SQL query above).

Seems to me like this is an easy SQL query. Should I put that in a view instead? Is it even possible to achieve this with LINQ (if possible without subqueries)?

Thank you for your help!

EDIT 1

The code below works but if a parent has N children it will appear N times in the result. Because there is no group by.

var query =

    from item in dbContext.Items

    join oAdd in dbContext.Operations on item.Id equals oAdd.ItemId into oAddJoin
    join oRem in dbContext.Operations on item.Id equals oRem.ItemId into oRemJoin

    from oAddLeftJoin in oAddJoin.Where(o => o.OperationTypeId == (int)OperationTypes.AddTo).DefaultIfEmpty()
    from oRemLeftJoin in oRemJoin.Where(o => o.OperationTypeId == (int)OperationTypes.RemoveFrom).DefaultIfEmpty()

    let existingCount = dbContext.Items.Count(i => i.ParentItemId == item.Id)

    select new
    {
        item,
        Added = oAddJoin.Count(),
        Removed = oAddJoin.Count(),
        existingCount
    };

var summaryList = query.ToList();

EDIT 2

Actually, below is the one which works and return good values. Even the SQL Query above is wrong. Sorry about the spam.

var query = 

    from item in dbContext.Items

    let addedCount    = dbContext.Operations.Count(o => o.ItemId == item.Id && o.OperationTypeId == (int)OperationTypes.AddTo)
    let removedCount  = dbContext.Operations.Count(o => o.ItemId == item.Id && o.OperationTypeId == (int)OperationTypes.RemoveFrom)
    let existingCount = dbContext.Items.Count(i => i.ParentItemId == item.Id)

    select new
    {
        item,
        Added = addedCount,
        Removed = removedCount,
        Existing = existingCount
    };

var summaryList = query.Distinct().ToList();

EDIT 3

The super ugly SQL query that works:

select distinct
      i.Id
    , i.Code
    , (select count(*) from operations oAdded where oAdded.itemid = i.id and oAdded.operationtypeid = 10) "Added"
    , (select count(*) from operations oAdded where oAdded.itemid = i.id and oAdded.operationtypeid = 20) "Removed"
    , (select count(*) from items ic where ic.ParentItemId = i.id) "Existing"
from items i

Aaron Gates Aaron Gates · Accepted Answer · 2015-11-04T16:20:55

If this is Entity Framework (you said LINQ to Entity), then you could make the operations and item tables navigation properties of item. Then, you could use a view model to calculate your counts. Something like:

public class ViewModel : Item
{
    public int Added {get;set;}
    public int Removed {get;set;}
    public int Existing {get;set;}

    public ViewModel(Item i) {
        this.id = i.id;
        this.code = i.code;
        this.Added = i.operations.Where(o => o.operationTypeID == 10).Count;
        this.Removed = i.operations.Where(o => o.operationTypeID == 20).Count;
        this.Existing = i.Items.Count;
   }
}

Then your query would just be:

dbContext.Items.Select(i => new ViewModel(i)).ToList();

LINQ to SQL - multiple left join, group by, left join on same table, count

1 Answers