linq to SQL with aggregate functions

Question

I have spent a great deal of time trying to convert the query below to linq to entities.

SELECT ItemCode, AVG([Count]) As [Count]
FROM [tblHistory]
GROUP BY ItemCode
ORDER BY [Count] desc

In my entity framework edmx, I have a tblHistory with fields

{ ItemCode(int), Count(int), Date(datetime) }

I am able to group by one column and display that particular column (in this case Count) but to access ItemCode in the same context is giving me trouble.

One of the methods that I tried to use is below:

 var query = from p in Context.tblHistories
             group p by p.ItemCode into g
             select new tblHistory
                        {
                            ItemCode = g.Key,
                            Count = from c in g select new { c.Count }
                        };

Please let me know if additional info is required for anyone who has knowledge of this as I will gladly provide.

Answer 1

tblHistories.GroupBy(p => p.ItemCode)
         .Select(g => new { ItemCode = g.Key, Count = g.Average (c => c.Count)})
         .OrderByDescending(x => x.Count)
         .AsEnumerable()
         .Select(x => new tblHistory{ ItemCode = x.ItemCode, Count = (int)x.Count })