One LINQ query to get counts from several entities when using entity framework core

.net-core c# entity-framework-core linq

Question

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

1
1
12/12/2019 10:01:02 AM

Popular Answer

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();
0
12/12/2019 2:04:30 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow