CODEWITHSUNDEEP
c#linqentity-frameworkgroup-byentity-framework-6
Migue
Migue

Reputation: 91

Group By Query with Entity Framework

In my application I have Movements associated with a category. I want a list of the most frequent category.

My objects are:

Category: catId, catName
Movement: Movid, movDate, movMount, catId

I think it would have to raise it with a "Group By" query (grouping by catId and getting those more) (Im using Entity Framework 6 in c#)

From already thank you very much!

Upvotes: 3

Views: 32602

Answers (5)

Simon_Weaver
Simon_Weaver

Reputation: 145880

IMPORTANT: Entity Framework 7 (now renamed to Entity Framework Core 1.0) does not yet support GroupBy() for translation to GROUP BY in generated SQL. Any grouping logic will run on the client side, which could cause a lot of data to be loaded.

https://blogs.msdn.microsoft.com/dotnet/2016/05/16/announcing-entity-framework-core-rc2

Upvotes: 3

Amir Sherafatian
Amir Sherafatian

Reputation: 2083

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();

Upvotes: 1

Amir Sherafatian
Amir Sherafatian

Reputation: 2083

you just need to use navigation property on category simply, you have a navigation property on category contains all related Movement, i call it Movements in following query. you can write your query like this, with minimum of connection with DB.

class Cat
{
     public Guid catId { get; set; }
     public string catName { get; set; }
     public IEnumerable<Movement> Movements { get; set; }
     public int MovementsCount { get { return Movements.Count(); } }
}

var Categories = category.Select(u => new Cat() 
{
    u.catId, 
    u.catName,     
    Movements = u.Movements.AsEnumerable()
}).ToList();

var CategoriesIncludeCount = Categories.OrderBy(u => u.MovementsCount).ToList();

Upvotes: 1

Migue
Migue

Reputation: 91

I resolved the problem!

I used the proposal by "Raja" solution (Thanks a lot!).

This return a collection composed of "Category" and "Count". I Change it a bit to return a list of Categories.

var groupedCategories = model.Movement.GroupBy(m => m.catId).Select(
                                g => new {catId= g.Key, Count = g.Count() }); 

var freqCategories= groupedCategories.Join(model.Category, 
                                              g => g.catId, 
                                              c => c.catId, 
                                              (g, c) => new {category = c, count = g.Count}).OrderByDescending(ca => ca.count).Select(fc => fc.category).ToList ();

Upvotes: 1

Raja Nadar
Raja Nadar

Reputation: 9489

group the movements by category and select catid and count. join this result with category to get the name and then descending sort the results on count.

var groupedCategories = context.Movements.GroupBy(m=>m.catId).Select(g=>new {CatId = g.Key, Count = g.Count()});    
var frequentCategories = groupedCategories.Join(context.Categories, g => g.CatId, c => c.catId, (g,c) => new { catId = c.catId, catName = c.catName, count = g.Count }).OrderByDescending(r => r.Count);

foreach (var category in frequentCategories)
{
 // category.catId, category.catName and category.Count
}

Upvotes: 2

Related Questions