SQL to Entity Framework Count Group-By

SQL to Entity Framework Count Group-By

Query syntax

var query = from p in context.People
            group p by p.name into g
            select new
            {
              name = g.Key,
              count = g.Count()
            };

Method syntax

var query = context.People
                   .GroupBy(p => p.name)
                   .Select(g => new { name = g.Key, count = g.Count() });

GroupBy and Count by condition Entity Framework

Conditional Count SQL translation was not supported well in EF6 and at all in EF Core until now.

EF Core 5.0 (currently in preview) finally adds correct translation, so the sample code works without modifications.

In pre EFC Core 5.0 you could use the equivalent conditional Sum, i.e. instead of

Count(condition)

use

Sum(condition ? 1 : 0)

In your query

TrueVotes = x.Sum(v => v.Vote == true ? 1 : 0),
FalseVotes = x.Sum(v => v.Vote == false ? 1 : 0),

---

Unrelated, but you should probably exclude Vote from grouping key

//.GroupBy(x => new { t.Type, t.Vote })
.GroupBy(x => new { t.Type })

otherwise counts will always be 0 and 1 or vice versa.

Translating query with GROUP BY and COUNT to Linq

Avoid predicate version of Count and use the equivalent conditional Sum.

In EF Core 3.0+ you can directly replace Count(condition) with Sum(condition ? 1 : 0), e.g.

var countData = await _context.Sale
    .GroupBy(s => s.InternalUser.UserName)
    .Select(g => new
    {
        UserName = g.Key,
        InsertCount = g.Sum(s => s.Version == 1 ? 1 : 0),
        UpdateCount = g.Sum(s => s.Version > 1 ? 1 : 0),
    })
    .ToListAsync();

EF Core 2.x supports translation only for GroupBy aggregates on simple grouping element property accessors, so you need to preselect the required expressions by using the GroupBy overload with element selector, e.g.

var countData = await _context.Sale
    .GroupBy(s => s.InternalUser.UserName, s => new
    {
        InsertCount = s.Version == 1 ? 1 : 0,
        UpdateCount = s.Version > 1 ? 1 : 0,
    })
    .Select(g => new
    {
        UserName = g.Key,
        InsertCount = g.Sum(s => s.InsertCount),
        UpdateCount = g.Sum(s => s.UpdateCount),
    })
    .ToListAsync();

Entity Framework Count and Group By in a Collection

You could try something like this:

var results = data.SelectMany(user=>user.Languages)
                  .GroupBy(language=>language)
                  .Select(gr=>new { Language = gr.Key, Total = gr.Count()});

I have supposed that each user object has a sequence of strings, IEnumerable<string>, which is called languages and holds the user's languages. Furthermore, the data you have is sequence of all the users.

SQL query with Group By and Having Clause in LINQ structure

You do not need HAVING here and Grouping should be provided by constant, because you have filter on grouping keys:

var data = context.WholesaleCredit
    .Where(x => x.ExpirationUTCDateTime > DateTime.Now && x.RegisteredCustomerId == registeredCustomerId && x.Site.Id == siteid)
    .GroupBy(x => 1)
    .Select(g => new 
    {
        TotalSiteCreditAmount = g.Sum(x => x.RemainingAmount),
        MaxExpiryDate = g.Max(x => x.ExpirationUTCDateTime)
    })
    .First();

How to use Group by and Having count clause in entity framework

This:

select tblRecordingType_ID
from tblEquipmentReadingTypes
group by tblRecordingType_ID
having count(tblRecordingType_ID) > 0

is equivalent to this:

select tblRecordingType_ID
from tblEquipmentReadingTypes
where tblRecordingType_ID is not null
group by tblRecordingType_ID

So you can just write something like this in linq:

var items = from o in context.tblEquipmentReadingTypes 
            where o.tblRecordingType_ID != null                           
            group o by o.tblRecordingType_ID into grouped
            select grouped.Key

EntityFramework. order by count of sub array elements

I would suggest to prepare grouping query with totals:

var totals =
    from e in Entities
    from p in e.Pets
    from t in p.Toys
    group e by e.Id into g
    select new 
    {
        Id = g.Key,
        Count = g.Count()
    };

var query = 
    from e in Entities
    join t in totals on e.Id equals t.Id
    orderby t.Count descending
    select e;

var result = await query
    .Take(10)
    .ToListAsync();

Group By Query with Entity Framework

i hope this help:

var query = dbContext.Category.Select(u => new 
{ 
    Cat = u, 
    MovementCount = u.Movement.Count() 
})
.ToList()
.OrderByDescending(u => u.MovementCount)
.Select(u => u.Cat)
.ToList();

---

Related Topics