Entity framework with “Group By” and/or “Order by”

Say we've got a project that allows user to download things. On the main page, I want to show the Most downloaded files ordered by the number of download! All that using EF.

How can i do this !! I've tried many things with Group By (Its a nightmare when you've got a lot of informations in an object). And i still dunno how to do this...

var query = from details in m_context.TransactionDetails
                        where details.Chanson != null
                        group details by details.Items into AnItem
                        orderby AnItem.Count()
                        select new Item() {
                            IdItem = Chansons.Key.IdItem,
                            ItemState= Chansons.Key.ItemState,
                            [...This object got something like 20 including links to other objects ... ]

Anyone have an idea?

Thanks :o)

Oh and sorry for my english, I'm giving my best but im from Quebec (Usualy talk french).

13.10.2009 20:44:26


I'm going to guess at your data model a little, here, but I don't think you need to group:

var query = from details in m_context.TransactionDetails
            where details.Chanson != null
            orderby details.Items.Count() descending
            select new Item
                IdItem = details.Chanson.IdItem,
                ItemState= details.Chanson.ItemState,
                // ...

Bonne chance!

Update: For albums:

var query = from details in m_context.TransactionDetails
            where details.DisqueCompact != null
            orderby details.Items.Count() descending
            select new Item 
                IdItem = details.DisqueCompact.IdItem,
                ItemState= details.DisqueCompact.QuelqueChose...
                // ...

You probably need two queries given your data model.

14.10.2009 20:35:52
Ahh nice! Then i don't need to make a "new item()" !! I juste have to return a list of the selected objects and that it! Haha, "Why do easily when I can make it difficult" ... Quebec proverb.
Simon Dugré 14.10.2009 12:16:44
Ho well, after check back my code, it's not working! Sorry! In this case, i want to get a list of "TransactionDetails" ordered by number of time that a "TransactionDetails.Chanson" where downloaded ... I think i MUST do a group by and gettin a cout of that.
Simon Dugré 14.10.2009 12:21:23
It doesn't sound to me (again, without seeing your model) like you need a group by. You say "it's not working." Can you explain precisely what is not working?
Craig Stuntz 14.10.2009 12:24:41
Are you saying that you want a combined list of songs and albums, and your current query is returning songs only, and not albums? If so, remove "details.Chanson != null" and change Item so that IdItem is nullable (as well as any other properties on Item, since not all returned records will have songs).
Craig Stuntz 14.10.2009 19:33:17
The query I gave you should work for songs. I'll try a query for albums, but I'll have to guess at data model property names for that.
Craig Stuntz 14.10.2009 20:33:50

For grouping data, you can read this How-To from MSDN.

13.10.2009 20:49:37
Yea i've read this thread and still don't find a way to make it work with my problem :o|
Simon Dugré 13.10.2009 20:50:25

This is an example of how you should do it:

//this is a entity framework objects
CTSPEntities CEntity = new CTSPEntities();

//and this is your example query
var query = (from details in CEntity.Purchase_Product_Details
             group details by new { details.Product_Details.Product_Code, details.Product_Details.Product_Name} into Prod
             select new
                 PID = Prod.Key.Product_Code,
                 PName = Prod.Key.Product_Name,
                 Amount = Prod.Sum(c => c.Lot_Amount),
                 count= Prod.Count()
             }).OrderBy(x => x.Amount);
foreach (var item in query)
7.11.2012 17:23:22