Hi,
I need to execute a query with join and group by. I would like the linq query to result in the single DB query.
If I execute the following query without grouping:
var fetchStrategy = new FetchStrategy { MaxFetchDepth = 3 };
fetchStrategy.LoadWith<DbStatsEventsDailySummary>(seds => seds.DbItemType);
fetchStrategy.LoadWith<DbStatsEventsDailySummary>(seds => seds.DbBusinessCustomer);
var statsEventsDailySummariesResult = DbContext.DbStatsEventsDailySummaries.LoadWith(fetchStrategy)
.Where(seds => seds.StatsDate >= startDate && seds.StatsDate <= endDate && seds.DbStatus.IsEndStatus)
.ToList();
Then I can see that it is transformed to the single database query with 2 additional tables (DbItemType, DbBusinessCustomer) nicely joined. Then I need to execute a group by like this:
var bCustomersItemTypesStatsList = statsEventsDailySummariesResult
.GroupBy(seds => new { seds.BusinessCustomerId, seds.ItemTypeId })
.Select(g => new
{
custName = g.FirstOrDefault().DbBusinessCustomer.Name,
itemTypeHash = g.FirstOrDefault().DbItemType.Hash,
itemTypeName = g.FirstOrDefault().DbItemType.Name,
qty = g.Sum(o => o.EventsQty)
}).ToList();
but the problem is that it is all done in memory. If I do it in the single shot like this:
var bCustomersItemTypesStatsList2 = DbContext.DbStatsEventsDailySummaries.LoadWith(fetchStrategy)
.Where(seds => seds.StatsDate >= startDate && seds.StatsDate <= endDate && seds.DbStatus.IsEndStatus)
.GroupBy(seds => new { seds.BusinessCustomerId, seds.ItemTypeId })
.Select(g => new
{
custName = g.FirstOrDefault().DbBusinessCustomer.Name,
itemTypeHash = g.FirstOrDefault().DbItemType.Hash,
itemTypeName = g.FirstOrDefault().DbItemType.Name,
qty = g.Sum(o => o.EventsQty)
}).ToList();
I have 2 issues:
1. I an not get the values from the joined objects: custName = g.FirstOrDefault().DbBusinessCustomer.Name, I got an exception that is not supported by the DB (I am using MySQL 5.7).
If I change it as follows:
var bCustomersItemTypesStatsList3 = DbContext.DbStatsEventsDailySummaries.LoadWith(fetchStrategy)
.Where(seds => seds.StatsDate >= startDate && seds.StatsDate <= endDate && seds.DbStatus.IsEndStatus)
.GroupBy(seds => new { seds.DbBusinessCustomer, seds.DbItemType })
.Select(g => new
{
custName = g.Key.DbBusinessCustomer.Name,
itemTypeHash = g.Key.DbItemType.Hash,
itemTypeName = g.Key.DbItemType.Name,
qty = g.Sum(o => o.EventsQty)
}).ToList();
It works, but it results in multiple DB queries. One to the main table: DbStatsEventsDailySummaries with group by, but without any join, and then multipel queries to DbItemType and DbBusinessCustomer, which spoils my performance.
So either way the performance is bad: either I have to perform groping in memory or I need to execute multiple DB queries.
Is there any way to write a query that will result with single DB query with group by and join?