Linq group by and count

c# entity-framework-6 linq sql

Question

I'm attempting to comprehend how groupy by and count operate with linq but I'm having trouble figuring out how to achieve my goals.

I have this desk.

ASSET:
Id, Code, Name, ParentId

If the asset is the root, the ParentId is null; otherwise, it contains the parent id.

I want to know how many children each root parent has as well as their ID.

I used the following search:

select father.Id, father.Code, COUNT(children.Id) As NumberOfChildren 
from Asset father 
left join Asset children on(father.Id = children.ParentId) 
where father.ParentId IS NULL 
group by father.Id, father.Code

I use this linq query.

var query = from father in this.assetService.GetAll() 
                        join children in this.assetService.GetAll() 
                        on father.Id equals children.ParentId into Children 
                        from children in Children.DefaultIfEmpty() 
                        where father.ParentId.Value == null 
                        group father by new { id = father.Id, code = father.Code } into gf 
                        select new { id = gf.Key.id, count = gf.Count() };

however, the thing that causes that query:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Id], 
    [GroupBy1].[A1] AS [C2] 
    FROM ( SELECT 
        [Extent1].[Id] AS [K1], 
        [Extent1].[Code] AS [K2], 
        COUNT(1) AS [A1] 
        FROM  [dbo].[Asset] AS [Extent1] 
        LEFT OUTER JOIN [dbo].[Asset] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ParentId] 
        WHERE [Extent1].[ParentId] IS NULL 
        GROUP BY [Extent1].[Id], [Extent1].[Code] 
    )  AS [GroupBy1]

The difficulty stems from theCOUNT(1) How can I tell whether something is as it should be?COUNT(children.Id)

1
0
7/16/2014 2:06:25 PM

Accepted Answer

Given that you wish to address theNULL values fromchildren.Id , you'll need a method of counting them when you choose your ultimate object. In order to accomplish this, you would group into a new object, which you could then query against to obtain the appropriate count. You may find the amended query object you need here:

var query = from father in this.assetService.GetAll() 
                        join children in this.assetService.GetAll() 
                        on father.Id equals children.ParentId into Children 
                        from children in Children.DefaultIfEmpty() 
                        where father.ParentId.Value == null 
                        group new { father = father, childExists = (children != null) } by new { id = father.Id, code = father.Code } into gf 
                        select new { id = gf.Key.id, count = gf.Count(o => o.childExists) };

To test it, I used the following C# fiddle, and it appropriately returns 0 records if a parent doesn't have any offspring.

https://dotnetfiddle.net/gyqpef

0
7/16/2014 3:12:38 PM

Popular Answer

Try something similar to this:

// Get all the assets in a list.
var assets = this.assetService.GetAll().ToList();

// Get the parents.
var parents = (from asset in assets
               where asset.ParentId == null);

// Remove parents from the original list.
var children = assets.RemoveAll(x => parents.Contains(x));

// Group the children by their parentId
var result = children.GroupBy(x => x.ParentId)
                     .Select(x => new { ParentId = x.Key, Count = x.Count() });


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow