How to write this linq query so it isn't so slow-CodePudding

I have this sql statement which is pretty instantaneous when running it.

select Distinct statuses.Description, count(*) as count from referrals 
inner join statuses on referrals.StatusId = statuses.id
group by statuses.Description

But when I run the below linq code with entity framework core, it takes almost 5 minutes to run and there are only 680 rows in the database.

var data = context.Referrals.Include(s => s.Status).AsEnumerable().GroupBy(r => r.Status)
    .Select(g => new StatusCountItem { Status = g.Key.Description, Count = g.Select(r => r).Count() }).ToList();

Is there a way to write a similar linq statement that won't take forever to run or do I need to figure out a different way to do what I want?

EDIT: When I don't have the AsEnumerable I get this error message which is why I added it:

The LINQ expression 'DbSet() .Join( inner: DbSet(), outerKeySelector: r => EF.Property<int?>(r, "StatusId"), innerKeySelector: s => EF.Property<int?>(s, "Id"), resultSelector: (o, i) => new TransparentIdentifier<Referral, Status>( Outer = o, Inner = i )) .GroupBy(r => r.Inner)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync

CodePudding user response：

Your Sql query is built based on context.Referrals.Include(s => s.Status).AsEnumerable(), which is equivalent to:

select *
from referrals 
    inner join statuses on referrals.StatusId = statuses.id

Note the star, you're querying every column. In other words, remove the random AsEnumerable() in the middle of your query.

CodePudding user response：

The GroupBy is redundant since you're already doing that in the SQL. Also the AsEnumerable is redundant as well since the Include method returns an IIncludableQueryable<TEntity,TProperty> which inherits off of IEnumerable. You also don't need to recompute the count in .NET since you've already computed the count as a field in the SQL query. There might be a slight syntax issue here but this should get you 99% of the way there.

var data = context.Referrals.Include(s => s.Status).Select(g => new StatusCountItem { Status = g.Key.Description, Count = g.Key.Count }).ToList();