0

I have

var query = from c in db.customers_info
                            group c by c.issue_date into g
                            select new
                            {
                                name = g.Key,
                                value = g.Count()
                            };

Instead of grouping by c.issue_date, there are other fields such as 'c.gender', 'c.termination_date', 'c.customer_name' and many many fields. I'd like to create a console app where user can input a string of what he want to group by, and the query will pick it up. Something like

var query = from c in db.customers_info
                            group c by c.{user_input} into g
                            select new
                            {
                                name = g.Key,
                                value = g.Count()
                            };

My suboptimal solution so far:

var query = from c in db.customers_info
                            group c by user_input.Equals("issue_date")? c.issue_date : user_input.Equals("gender")? c.gender : ... and more conditions.
                            into g
                            select new
                            {
                                name = g.Key,
                                value = g.Count()
                            };

Is it possible?

EDIT: would prefer to process groupby as part of the generated SQL query, rather than bringing all fields into the app, and groupby in app level. Thanks!

K Y
  • 191
  • 1
  • 8
  • Does this answer your question? [LINQ Grouping dynamically](https://stackoverflow.com/questions/17678197/linq-grouping-dynamically) – gunr2171 Apr 26 '21 at 17:17
  • Hey, @gunr2171 thanks for the comments. Looks like their solutions bring the 'groupBy' into the app level. I'd prefer to have 'GroupBy' be done in the database. – K Y Apr 26 '21 at 17:58
  • Linq will implement the Group By condition as a clause in the SQL query that it generates. Is that what you mean by "have 'GroupBy' be done in the database"? You can use the Database.Log property of the dbContext to examine the SQL that Linq generates. – Nicholas Hunter Apr 26 '21 at 18:23
  • thanks @NicholasHunter . I cannot simply create a view because the field to be groupBy has to be a choice made by the app user. What I meant by 'prefer to have GroupBy to be done in the database' is preferring the groupby be done through SQL query generated, and processed in the database.. as you described. – K Y Apr 26 '21 at 18:30
  • 2
    One of the answers in [LINQ Grouping dynamically](https://stackoverflow.com/a/17679217/3744182) suggests to use Dynamic Linq, https://dynamic-linq.net/. I believe that this properly generates queries dynamically, see https://dynamic-linq.net/basic-query-operators#groupby: *The Dynamic LINQ GroupBy functionality works the same as the normal, strongly typed GroupBy.* See: [How to use GroupBy using Dynamic LINQ](https://stackoverflow.com/q/20329558/3744182). – dbc Apr 26 '21 at 18:35
  • 1
    If you are using .net framework (instead of .net core) check out [DynamicQueryable](https://github.com/umutozel/DynamicQueryable) – Igor Apr 26 '21 at 18:39
  • Thanks, @dbc i'm reading up on the dynamic Linq according to the suggestion and comments. A comment by Dustin in that answer suggest Dynamic Linq hasn't been maintained for years, which made me hesitate in using it. – K Y Apr 26 '21 at 18:40
  • I can't recommend it because I haven't used it, but there have been three new point releases in 2021: https://www.nuget.org/packages/System.Linq.Dynamic.Core. So I'd suggest to give it a try. [Dustin Kingen](https://stackoverflow.com/users/580951/dustin-kingen)'s [comment](https://stackoverflow.com/questions/17678197/linq-grouping-dynamically/17679217#comment25754221_17678332) is from 2013, so the comment itself may not have been maintained for years. – dbc Apr 26 '21 at 18:46
  • The problem with that is C# is a language of types. What type would `name` be when the column changes? What do you want to do with the result? – NetMage Apr 27 '21 at 06:16
  • @NetMage good question. This seems to be the trouble I encounter. My name will take on "DateTime?" if my key is Date, "string" if my key is customer_name, "short?" or "int?" it's customer's age etc. Then I will sum another column (say saving or checking balance) The result will be output as Json, delivered to the front using API. The front-end will take these data and draw a bar chart.. (I know I say console app in my question just for simplicity) – K Y Apr 27 '21 at 16:25
  • You only get to pick one type for `name` in a static programming language, or use `object` for all of them and leave static typing behind. – NetMage Apr 27 '21 at 23:59

1 Answers1

0

Maybe something like this:

/*  
 *  Listing 1
 *  Do TRY this at home, this code IS meant to be compiled
 */

var keySelectors = new List<Expression<Func<Measurement, ReportResult>>>();

/* list of select keys and aggregates that resulting groups will be projected to */
var selectAggregate = new List<Expression<Func<IGrouping<ReportResult, Measurement>, ReportResult>>>();

if (groupByClient)
{
    /* if grouping by client is selected, then we will not only group by that key
     * but will also add it to the projection */
    keySelectors.Add(x => new ReportResult { Client = x.Client });
    selectAggregate.Add(x => new ReportResult { Client = x.Key.Client });
}

if (groupByUser)
{
    /* same as before, add it to key selectors and add it to the projection */
    keySelectors.Add(x => new ReportResult { FirstUsername = x.User.UserName });
    selectAggregate.Add(x => new ReportResult { FirstUsername = x.Key.FirstUsername });
}

if (groupByClient || groupByUser)
{
    /* if either of the grouping is selected, then add the following aggregator */
    selectAggregate.Add(x => new ReportResult { FirstReading = x.Sum(x => x.Reading) });
}

var result = _context.Measurement
    /* call the magic function */
    .GroupBy(LinqExpressionHelpers.CombineKeySelectors(keySelectors))
    /* call the magic function again in a different role */
    .Select(LinqExpressionHelpers.CombineKeySelectors(selectAggregate));

Taken from this article: https://jksware.net/posts/ef-core-linq-groupby-dynamic-key/

Ariel Haim
  • 86
  • 7