Entity Framework - Distinct Facets - Performance Optimization
c# distinct entity-framework entity-framework-core performance
Question
Suppose we have a complex generated query...
IQueryable<...> query = ... //something complex
And then I would like to return multiple "facets" and my current implementation is...
var facets = new
{
Countries = query.Select(r => new { Id = r.CountryId, Name = r.Country.Name })
.Distinct().ToList(),
Regions = query.Select(r => new { Id = r.RegionId, Name = r.Region.Name })
.Distinct().ToList(),
... //7 more facets
};
With this implementation I have 9 ToList() so I would have 9 db queries and since "query" is quite complex, it causes some performance issues. Is there any possibility to implement this with EF and a single db query?
Update: EF Core version 3.1
Accepted Answer
Currently (EF Core 3.1) it's not possible to achieve the desired result shape with single database query.
What you can try though is to retrieve all the (distinct) data needed with single database query in memory and then retrieve the "facets" using LINQ to Objects. Basically splitting the query on server and client evaluation, which in pre EF Core 3.0 was implicit, and in EF Core 3.0+ has to be explicit.
e.g.
var data = query.Select(r => new
{
Country = new { Id = r.CountryId, Name = r.Country.Name }, // facet 1 data
Region = new { Id = r.RegionId, Name = r.Region.Name }, // facet 2 data
//... 7 more facets
})
.Distinct() // with or w/o this depending on query performance vs result set size
.ToList();
var facets = new
{
Countries = data.Select(r => r.Country).Distinct().ToList(), // facet 1
Regions = data.Select(r => r.Region).Distinct().ToList(), // facet 2
//... 7 more facets
};
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow