CODEWITHSUNDEEP
c#entity-frameworklinqcountgroup-by
Jorn Theunissen
Jorn Theunissen

Reputation: 191

Map Linq groupby count to C# entity

I have this sql query that performs a groupby on a single field. It then counts the groupby's. So far so good.

select type, count(*) 
from myTable
group by type
//Result    
//TypeA = 5
//TypeB = 3

However, I am having trouble performing this query with Linq as I need to map the outcome of Count() to a specific entity.

The entity I want to map the count to:

public class MyEtity(){
    public int TypeACount {get; set;}
    public int TypeBCount {get; set;}
}

The linq query I currently use which 

MyEntity test = data
    .GroupBy(c => c.type)
    .Select(g => new MyEntity (){
        TypeACount = g.Where(d => d.type == "A").Count(),
        TypeBCount = g.Where(d => d.type == "B").Count()
    });

Extra info

Based on some answers, a little extra info. My original plan was to use following.

var firstResults = session.Query<MyEntity>()
    .Where(//several date filter conditions)
    .ToList();

return new MyEntity() {
    TypeACount = firstResults.Where(s => s.type == "A").Count(),
    TypeBCount = firstResults.Where(s => s.type == "B").Count()
};

This works, but table queried is rather large and the query took quite some time. Based on a colleagues feedback I was asked if the query couldn't be made in to 1 part instead of separating it. The idea being that the query counting logic would remain in SQL rather than in C#. I don't know if that would actually be faster, but that is what I am trying to figure out.

Upvotes: 0

Views: 971

Answers (2)

Lucian Bumb
Lucian Bumb

Reputation: 2881

why not the clasic way?, I do not see in your query the reason for group by or Select; 

            var entity=new MyEntity()

            entity.TypeACount = data.Count(a => a.TypeOfUsage == "A"),
            entity.TypeBCount =data.Count(b => b.TypeOfUsage == "B")

Upvotes: 1

juharr
juharr

Reputation: 32266

You should map after you get the information

var results = data
    .Where(c => c.TypeOfUsage == "A" || c.TypeOfUsage == "B")
    .GroupBy(c => c.TypeOfUsage)
    .Select(g => new 
    {
        Type = g.Key,
        Count = g.Count()
    }).ToList();

MyEntity test = new MyEntity
{
    TypeACount = results.FirstOrDefault(d => d.Type == "A")?.Count ?? 0,
    TypeBCount = results.FirstOrDefault(d => d.Type == "B")?.Count ?? 0
}

Or if you don't have C# 6

var a = results.FirstOrDefault(d => d.Type == "A");
var b = results.FirstOrDefault(d => d.Type == "B");
MyEntity test = new MyEntity
{
    TypeACount = a == null ? 0 : a.Count,
    TypeBCount = b == null ? 0 : b.Count
}

Another option would be to use a constant group by.

MyEntity test= data
    .Where(c => c.TypeOfUsage == "A" || c.TypeOfUsage == "B")
    .GroupBy(c => 1)
    .Select(g => new MyEntity 
    {
        TypeACount = g.Where(d => d.TypeOfUsage == "A").Count(),
        TypeBCount = g.Where(d => d.TypeOfUsage == "B").Count()
    }).Single();

This would be more like the following SQL

select 
    sum(case when typeOfUseage = 'A' then 1 else 0 end) AS TypeACount
    , sum(case when typeOfUseage = 'B' then 1 else 0 end) AS TypeBCount
from myTable

Upvotes: 2

Related Questions