none
Group a joined result set in one query RRS feed

  • Question

  • Hi all,

    For now I have the following code which is not performant at all because all the records are retrieved and then grouped, and then all the results are paged in memory:

    var query = from cn in DataContext.CANNachrichts.Where(x => x.IdentifierCANBusID == settings.CANBusID && x.SignalID == cnSearch.SignalID) 
                            join fc_cn in DataContext.ZOT_FunktionCode_Nachrichts.Where(x => x.Modus == fc_cn_Search.Modus) 
                            on cn.NachrichtID equals fc_cn.NachrichtID 
                            join fc in DataContext.FunktionCodes 
                            on fc_cn.FunktionsCodeID equals fc.FunktionsCodeID 
                            join fcd in DataContext.FunktionCode_Details.Where(x => x.Sprachcode == settings.Language) 
                            on fc.FunktionsCodeID equals fcd.FunktionsCodeID 
                            select new ZOT_FunktionCode_Nachricht_DTO 
                            { 
                                FunktionsCodeID = fc.FunktionsCodeID, 
                                FunktionsCode = fc.FunktionsCode, 
                                FunktionCodeBezeichnung = fcd.Bezeichnung, 
                                Identifier = fc_cn.CANNachricht.Identifier, 
                                Bits = cn.BytePos.ToString() 
        
                            }; 
                if (sortExp != null && sortDirection != null) { query = query.OrderBy(sortExp + " " + sortDirection); } 
                List<ZOT_FunktionCode_Nachricht_DTO> result = new List<ZOT_FunktionCode_Nachricht_DTO>(query); 
                var groupedQuery = from n in result 
                                   group n by n.FunktionsCodeID into g 
                                   select 
                                       new ZOT_FunktionCode_Nachricht_DTO 
                                       { 
                                           FunktionsCodeID = g.First().FunktionsCodeID, 
                                           FunktionsCode = g.First().FunktionsCode, 
                                           Identifier = g.First().Identifier, 
                                           FunktionCodeBezeichnung = g.First().FunktionCodeBezeichnung, 
                                           BytePos_min = int.Parse(g.Min(x => x.Bits)), 
                                           BytePos_max = int.Parse(g.Max(x => x.Bits)) 
                                       }; 


    But honestly I don't know how to integrate the group query in the existing query cause as far as i know i can only group an existing linq entry not a whole result set...

    Thx for any hints concerning this topic...
    Tuesday, March 10, 2009 6:50 PM

Answers

  • This happens because you convert the initial results into a list and then do your grouping on that.   If you want it to be all one query you need to not convert it to a list; leave it as a query.


    var groupedQuery = from n in query ...
    Wayward LINQ Lacky
    • Marked as answer by wuz Friday, March 13, 2009 2:13 PM
    Thursday, March 12, 2009 7:15 PM
    Moderator

All replies

  • The reason it is not performant is that the group-by operator returns hierarchical results, which ends up being a lot more data and a lot more queries than you wanted.

    Normally the hierarchy of a group-by is erased when you select a final result that only references the keys or aggregates of the group.  Your code keeps the heirachy alive by tyring to use the First() operator as an aggregate. 

    Instead of using First() do what you would have normally done in a SQL query; put the extra columns into the group by.

    from n in result
    group n by new { n.FunktionsCodeID, n.FunktionsCode, n.Identifier, n.FunktionCodeBezeichnung } into g
    select new xxx {
           FunktionsCodeID = g.Key.FunktionsCodeID,
           FunktionsCode = g.Key.FunktionsCode,
           Identifier = g.Key.Identifier,
           FunktionCodeBezeichnung = g.Key.FunctionCodeBezeichnung,
           BytePos_min = int.Parse(g.Min(x => x.Bits)),
           BytePos_max = int.Parse(g.Max(x => x.Bits))
     }

    Wayward LINQ Lacky
    Tuesday, March 10, 2009 10:45 PM
    Moderator
  • Big thx, that's definitely a very important point!

    But the original problem remains, i first return all the results in the database and then apply the group:

    List<ZOT_FunktionCode_Nachricht_DTO> result = new List<ZOT_FunktionCode_Nachricht_DTO>(query);
    from n in result
    group n by new { n.FunktionsCodeID, n.FunktionsCode, n.Identifier, n.FunktionCodeBezeichnung } into g
    select new xxx {
           FunktionsCodeID = g.Key.FunktionsCodeID,
           FunktionsCode = g.Key.FunktionsCode,
           Identifier = g.Key.Identifier,


    Is it somehow possible to combine query and groupedQuery into one query? Then i could directly apply the paging as well.

    My problem is that i can only apply "group fc_cn", "group fc" [..] but not group resultset from query

    Thx!
    Wednesday, March 11, 2009 1:29 AM
  • This happens because you convert the initial results into a list and then do your grouping on that.   If you want it to be all one query you need to not convert it to a list; leave it as a query.


    var groupedQuery = from n in query ...
    Wayward LINQ Lacky
    • Marked as answer by wuz Friday, March 13, 2009 2:13 PM
    Thursday, March 12, 2009 7:15 PM
    Moderator
  • Oh my gosh thx so much, of course i tried to group the existing query before but i had to struggle with  "a query body must end with a select clause or group clause"versus "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

    But of course i cannot directly select the DTO in the query AND in the groupedquery. The solution is to select an anonymous type in query and then the dto in the grouped query like:


    var query = from cn in DataContext.CANNachrichts.Where(x => x.IdentifierCANBusID == settings.CANBusID && x.SignalID == cnSearch.SignalID)  
                            join fc_cn in DataContext.ZOT_FunktionCode_Nachrichts.Where(x => x.Modus == fc_cn_Search.Modus)  
                            on cn.NachrichtID equals fc_cn.NachrichtID  
                            join fc in DataContext.FunktionCodes  
                            on fc_cn.FunktionsCodeID equals fc.FunktionsCodeID  
                            join fcd in DataContext.FunktionCode_Details.Where(x => x.Sprachcode == settings.Language)  
                            on fc.FunktionsCodeID equals fcd.FunktionsCodeID  
                            select new 
                            {  
                                FunktionsCodeID = fc.FunktionsCodeID,  
                                FunktionsCode = fc.FunktionsCode,  
                                FunktionCodeBezeichnung = fcd.Bezeichnung,  
                                Identifier = fc_cn.CANNachricht.Identifier,  
                                Bits = cn.BytePos.ToString()  
         
                            };  
                if (sortExp != null && sortDirection != null) { query = query.OrderBy(sortExp + " " + sortDirection); }  
                List<ZOT_FunktionCode_Nachricht_DTO> result = new List<ZOT_FunktionCode_Nachricht_DTO>(query);  
                var groupedQuery = from n in result  
                                   group n by n.FunktionsCodeID into g  
                                   select  
                                       new ZOT_FunktionCode_Nachricht_DTO  
                                       {  
                                           FunktionsCodeID = g.First().FunktionsCodeID,  
                                           FunktionsCode = g.First().FunktionsCode,  
                                           Identifier = g.First().Identifier,  
                                           FunktionCodeBezeichnung = g.First().FunktionCodeBezeichnung,  
                                           BytePos_min = int.Parse(g.Min(x => x.Bits)),  
                                           BytePos_max = int.Parse(g.Max(x => x.Bits))  
                                       };  

    not so bad for building queries.. thx!
    Friday, March 13, 2009 2:03 PM
  • Event though you must end a query with a select or group-by those clauses need not end the query.  You can follow both a select and group-by with an 'into' clause that continues the query.

    var query = from x in collection
                       where x.C == c
                       select new {x.A, x.B} into ab
                       where ab.A < a
                       group ab by ab.B;

    this is equivalent of writing:

    var query = from ab in (
                                from x in collection
                                where x.C == c
                                select new {x.A, x.B}
                                )
                       where ab.A < a
                       group ab by ab.B;

    which is the same as:

    var query1 =  from x in collection
                          where x.C == c
                          select new {x.A, x.B}

    var query2 = from ab in query1
                         where ab.A < a
                         group ab by ab.B;


    Wayward LINQ Lacky
    Friday, March 13, 2009 4:07 PM
    Moderator