This code written by @Rahul Singh in this post:
var result = _dbContext.ExtensionsCategories.ToList().GroupBy(x => x.Category)
.Select(x =>
{
var files = _dbContext.FileLists.Count(f => x.Select(z => z.Extension).Contains(f.Extension));
return new
{
Category = x.Key,
TotalFileCount = files
};
});
This code has a problem when used inside database context and we should use ToList()
like this to fix "Only primitive types or enumeration types are supported in this context" error:
var result = _dbContext.ExtensionsCategories.ToList().GroupBy(x => x.Category)
The problem of this is that ToList()
fetches all records and reduces performance. Now I wrote my own code:
var categoriesByExtensionFileCount =
_dbContext.ExtensionsCategories.Select(
ec =>
new
{
Category = ec.Category,
TotalSize = _dbContext.FileLists.Count(w => w.Extension == ec.Extension)
});
var categoriesTotalFileCount =
categoriesByExtensionFileCount.Select(
se =>
new
{
se.Category,
TotalCount =
categoriesByExtensionFileCount.Where(w => w.Category == se.Category).Sum(su => su.TotalSize)
}).GroupBy(x => x.Category).Select(y => y.FirstOrDefault());
The performance of this code is better but it has many lines of code. Any idea about improving the performance of the latter, or reducing the length of the former?