linq to entities query for count and group by with join on 2 tables

Question

I need a linq to entities query/ lambda expression for the following statement. Any help is greatly appreciated

SELECT 
    at.Name,
    Count(a.AssetTypeId) as CountofAssets,
    at.AssetTypeId
FROM
    AssetTypes at, Assets a
WHERE
    at.AssetClassId = 7
GROUP BY
    at.Name,at.AssetTypeID

Answer 1

Try this:

var assetTypes = context.AssetTypes.Where(a => a.AssetClass.Id == 7).Select(a => new { a.Name, a.AssetTypeId, CountOfAssets = a.Asset.Count()).ToList();

I hope you have foreign keys in your base and model graph is created properly.