Query joined tables with group by with a single DB query

Thread is closed for posting
1 posts, 0 answers
  1. Łukasz
    Łukasz avatar
    1 posts
    Member since:
    Dec 2016

    Posted 20 Dec 2016 Link to this post

    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?

     

     

     

     

     

     

     

     

     



Back to Top