I need to create the following TSQL query in EF/Linq but I am struggling :
SELECT mi.CategoryID
, cat.Category
FROM tblPropertyMaintenanceItem mi
INNER JOIN tblCategory cat
ON cat.CategoryID = mi.CategoryID
where mi.PropertyID = 451
GROUP BY mi.CategoryID
, cat.Category
ORDER BY cat.Category
I have got a basic query working but that obviously give me duplicate lines :
var cats = context.MaintenanceItems
.Include(s => s.Category)
.Where(s => s.PropertyID == id)
.OrderBy(s => s.Category.CategoryName).ToList();
How do I achieve what I need in SQL in Linq? I normally use SQL Stored Procs for data retrieval but trying to do this project all in EF/Linq.
Thanks
CodePudding user response:
Thanks for that - initially it was generating an error :
The LINQ expression 'DbSet\r\n .Where(m => m.PropertyID == __id_0)\r\n .Join(\r\n outer: DbSet, \r\n inner: m => EF.Property<Nullable>(m, "CategoryID"), \r\n outerKeySelector: c => EF.Property<Nullable>(c, "CategoryID"), \r\n innerKeySelector: (o, i) => new TransparentIdentifier<MaintenanceItem, Category>(\r\n Outer = o, \r\n Inner = i\r\n ))\r\n .GroupBy(\r\n source: m => m.Inner, \r\n keySelector: m => m.Outer)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
I changed it slightly and now it seems to work :
var cats = context.MaintenanceItems
.Include(s => s.Category)
.Where(s => s.PropertyID == id).AsEnumerable()
.GroupBy(x => x.Category)
.Select(x => x.First())
.OrderBy(s => s.Category.CategoryName).ToList();
Got some reading to do to understand why!
CodePudding user response:
that may be useful...
var cats = context.Categories
.Include(s => s.MaintenanceItems)
.Where(s => s.MaintenanceItems.Any(y => y.PropertyID == id))
.Select(s => new { s.CategoryID, s.CategoryName });