locked
dynamic linq groupby with substring RRS feed

  • Question

  • User-492280791 posted

    Hi All,

    I have a linq statement where I group by "timestamp" for instance: 20140220132600204 but now I only want to group on date for instance 20140203

    There is no real date column I can use so I assume I need a Substring(0,8) but how to implement in following statement?

    foreach (var messageGroup in results.AsQueryable().GroupBy(groupingCriteriaString, "it").Select(selectStr))

    Thanks!

    Bert

    Monday, August 31, 2015 7:36 AM

Answers

  • User281315223 posted

    var selectStr = "new(Key as Cycle, Count() as Total)"; var key = idBase;

    I don't believe you are going to be able to mix this as expected by using pure SQL. The equivalent of this would be :

    foreach (var messageGroup in results.AsQueryable().GroupBy(g => g.timestamp.ToString().Substring(0, 8)).Select(x => new { Cycle = x.Key, Total = x.Count()}))
    {
         // Your messageGroups will now be objects with two properties :
         // Cycle, which contains the actual key value (i.e. your 8 character timestamp)
         // Total, which is a total for the number of elements in that specific group
    }

    What is g in this context ?

    The variables that are used within the lambda queries (i.e. those containing =>) are used to map a specific value which represents an item in the group to another value. In the GroupBy method above, g will represent each record within your results, so the group by will map each result and group them based on the second part of the query (their timestamp).

    Likewise, the newly added select section "x => new { Cycle = x.Key, Total = x.Count()}", will do the same thing and x will instead represent each of the groups this time and it will map them to an anonymous object that has the two properties that were mentioned earlier.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 2, 2015 8:50 AM

All replies

  • User281315223 posted

    If I understand you correctly, you would probably want to try something like this :

    var groups = results.AsQueryable() 
                        .GroupBy(g => g.timestamp.ToString().Substring(0,8))    // Groups by an assumed timestamp property's first 8 characters
                        .ToList();
    
    // Iterate through ere
    foreach(var group in groups)
    {
         // Do work
    }
    

    Monday, August 31, 2015 11:57 AM
  • User-492280791 posted

    HI Rion,

    Thanks for your reply!

    I changed

    foreach (var messageGroup in results.AsQueryable().GroupBy(groupingCriteriaString, "it").Select(selectStr))

    into

    foreach (var messageGroup in results.AsQueryable().GroupBy(g => g.timestamp.ToString().Substring(0, 8), "it").Select(selectStr))

    But get following error: Cannot convert lambda expression to type 'string' because it is not a delegate type

    Can you help once more?

    Thanks!

    Wednesday, September 2, 2015 2:55 AM
  • User281315223 posted

    Have you tried removing the "it" section of your GroupBy clause as LINQ is likely expecting an IEqualityComparer as a second parameter? Additionally, what is selectStr in this context? 

    foreach (var messageGroup in results.AsQueryable().GroupBy(g => g.timestamp.ToString().Substring(0, 8)).Select(x => x))
    {
         // This would iterate through each grouping and you could access the group via messageGroup
         // and the individual values in the group through messageGroup.Value
    }
    

    Wednesday, September 2, 2015 8:09 AM
  • User-492280791 posted
    var selectStr = "new(Key as Cycle, Count() as Total)";
    var key = idBase;

    What is g in this context ?

    Wednesday, September 2, 2015 8:18 AM
  • User281315223 posted

    var selectStr = "new(Key as Cycle, Count() as Total)"; var key = idBase;

    I don't believe you are going to be able to mix this as expected by using pure SQL. The equivalent of this would be :

    foreach (var messageGroup in results.AsQueryable().GroupBy(g => g.timestamp.ToString().Substring(0, 8)).Select(x => new { Cycle = x.Key, Total = x.Count()}))
    {
         // Your messageGroups will now be objects with two properties :
         // Cycle, which contains the actual key value (i.e. your 8 character timestamp)
         // Total, which is a total for the number of elements in that specific group
    }

    What is g in this context ?

    The variables that are used within the lambda queries (i.e. those containing =>) are used to map a specific value which represents an item in the group to another value. In the GroupBy method above, g will represent each record within your results, so the group by will map each result and group them based on the second part of the query (their timestamp).

    Likewise, the newly added select section "x => new { Cycle = x.Key, Total = x.Count()}", will do the same thing and x will instead represent each of the groups this time and it will map them to an anonymous object that has the two properties that were mentioned earlier.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 2, 2015 8:50 AM
  • User-492280791 posted

    OK I am following your logic. Unfortunately I get this error:

    Error 19 'System.Linq.IQueryable' does not contain a definition for 'GroupBy' and no extension method 'GroupBy' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?) 

    Strange because System.Linq is added already on the top of my page as directive

    Any thoughts?

    Wednesday, September 2, 2015 9:01 AM
  • User281315223 posted

    Have you tried not using an IQueryable (or more specifically AsQueryable()) :

    foreach (var messageGroup in results.GroupBy(g => g.timestamp.ToString().Substring(0, 8)).Select(x => new { Cycle = x.Key, Total = x.Count()}))
    {
         // Your messageGroups will now be objects with two properties :
         // Cycle, which contains the actual key value (i.e. your 8 character timestamp)
         // Total, which is a total for the number of elements in that specific group
    }

    Wednesday, September 2, 2015 12:12 PM
  • User-492280791 posted

    Also does not work without 'AsQueryable', I get following msg:

    Error 14 'System.Collections.IEnumerable' does not contain a definition for 'GroupBy' and no extension method 'GroupBy' accepting a first argument of type 'System.Collections.IEnumerable' could be found (are you missing a using directive or an assembly reference?) 

    Thursday, September 3, 2015 2:20 AM
  • User281315223 posted

    That's a bit strange as it should work assuming you have System.Linq referenced, which you mentioned earlier that you do. Could you post the exact code you are using? I suppose you could also try a ToList() instead of AsQueryable() to see if that made any difference (but it shouldn't be necessary).

    Thursday, September 3, 2015 8:07 AM
  • User-492280791 posted

    HI Rion,

    Can you check your PM?

    I assume I cannot query result because it declared like this: IEnumerable results

    Thanks

    Thursday, September 3, 2015 9:35 AM