Linq to SQL using group By, and order by count

Question

Hello,

This is mysql query:

SELECT count(PVersion), PVersion
  FROM [Products].[dbo].[Active_Details] 
group by PVersion 
order by count(PVersion);

What will be its LINQ to SQL.

Answer 1

Should be a group into:

var product = (
    from p in yourContext.Active_Details
    group p by p.PVersion into pgroup
    select new { VersionCount= pgroup.Count(), pgroup.Key }
).OrderBy(x=>x.VersionCount);

Here is a MSDN Resource with examples

Answer 2

Thanks for your response. But I am not able to access this p.pVersion in select new braces.

select new { VersionCount= pgroup.Count(), p.PVersion };

Answer 3

Try this:

var product = 
            from p in yourContext.Active_Details
            group p by p.PVersion into pgroup
            let count = pgroup.Count()
            orderby count
            select new { Count = count, PVersion = pgroup.Key };

SELECT count(ProductVersion), ProductVersion , ProductID , SubProductID FROM [do-not-delete-accounts].[dbo].[Activation_Details] group by ProductVersion,ProductID,SubProductID order by count(ProductVersion);

var query = 
            from p in yourContext.Activation_Details
            group p by new 
            { 
               ProductVersion = p.ProductVersion, 
               ProductID = p.ProductID,
               SubProductID = p.SubProductID 
            } 
            into pgroup
            let count = pgroup.Count()
            orderby count
            select new 
            { 
                Count = count, 
                ProductVersion = pgroup.Key.ProductVersion, 
                ProductID = pgroup.Key.ProductID,
                SubProductID = pgroup.Key.SubProductID  
            };

Answer 4

Thank u very much. One more thing I want that I also want to access the other columns of table like PID, SPID . But this query does not work,

How can I?