Im working on the LINQ query which returns counts from several entities. I created this kind of query:
public async Task<(int UsersCount, int GuestUsersCount, int WorkItemsCount, int WorkGroupsCount)> GetSiteInfoAsync()
{
var counters = await (from user in Context.UserAccounts
from guestUser in Context.GuestAccounts
from workItem in Context.WorkItems
from workGroup in Context.WorkGroups
select new
{
usersCount = Context.UserAccounts.Count(),
guestUsersCount = Context.GuestAccounts.Count(),
workGroupsCount = Context.WorkGroups.Where(x => x.IsActive).Count(),
workItemsCount = Context.WorkItems.Count()
}).FirstOrDefaultAsync();
return (counters.usersCount, counters.guestUsersCount, counters.workItemsCount, counters.workGroupsCount);
}
I found the query blows up in case if the WorkItem
table is empty. I tried a different thing where I removed this part of code from the query:
from guestUser in Context.GuestAccounts
from workItem in Context.WorkItems
from workGroup in Context.WorkGroups
and then the query worked, cuz in my case I know that the UserAccounts
table is never empty as there is always some default User in the DB:
var counters = await (from user in Context.UserAccounts
select new
{
usersCount = Context.UserAccounts.Count(),
guestUsersCount = Context.GuestAccounts.Count(),
workGroupsCount = Context.WorkGroups.Where(x => x.IsActive).Count(),
workItemsCount = Context.WorkItems.Count()
}).FirstOrDefaultAsync();
Im just wondering if there is a cleaner solution? It needs to be a one LINQ query only. I also thought about creating a SQL view but this would be nasty for me as I would need to create separate migration for it and anytime I have to add some changes to the code I need to update the view with a new migration. Any ideas? Cheers
That's a one LINQ query solution, i hope this will be helpful,
var result = Context.UserAccounts.Select(x => new List<int>() { 1, 0, 0, 0 }).Union(Context.GuestAccounts.Select(y => new List<int>() { 0, 1, 0, 0 }))
.Union(Context.WorkGroups.Where(x => x.IsActive).Select(z => new List<int>() { 0, 0, 1, 0 })).Union(Context.WorkItems.Select(t => new List<int>() { 0, 0, 0, 1 })).ToList();
After database request you can get the count the occurences as shown below,
var count1 = result.Where(x => x[0] == 1).Count();
var count2 = result.Where(x => x[1] == 1).Count();
var count3 = result.Where(x => x[2] == 1).Count();
var count4 = result.Where(x => x[3] == 1).Count();