Dynamically Create GroupBy and Select with EF

Question

I am working with a pretty large SQL view (50+ columns). To ensure i get the correct data i have to select a subset of the columns (4 or 5 columns) and group by that sub set. (otherwise i will have extra data returned). This means i am writing a lot queries, all very similar with very minor differences.

I currently run my queries like so

 var result = this.UtilContext.MassiveView
    .GroupBy(g => new
    {
        g.id,
        g.Value1,
        g.Value2,
        g.Value3
    }).Where(c => c.Key.id == SomeID)
    .AsNoTracking()
    .Select(x => new
    {
        id = x.Key.id,
        Value1 = x.Key.Value1,
        Value2 = x.Key.Value2,
        Value3 = x.Key.Value3
    }).ToList()

Is it possible to create the GroupBy and Select statements dynamically (the app already knows what columns it requires). I am looking for a code based approch, creating a select statement is not what i am looking for here.

ORMs are for mapping results to objects, not a SQL substitute. Analytical/reporting queries like this one is easier done in SQL then mapped to an object, *if necessary*. — Panagiotis Kanavos, Oct 16 '14 at 07:04
I agree. But this is not an option, so i have to take a route like this. Basically i will be doing hundreds of queries of subsets of this view. My options are - Create hundreds of View, Create hundreds of Reprository calls or they to do something dynamic. — KevDevMan, Oct 16 '14 at 07:50
The beauty of SQL is that it allows you to be very dynamic. You can construct the proper SQL statement (and run it through EF's context) just as you can do this with eSQL. LINQ expressions produce eSQL and SQL in the end, so you are simply writing the same queries in a much harder way. Worse, LINQ-to-EF can produce hideous statements that will kill your DB's performance. It's a hammer and nail problem — Panagiotis Kanavos, Oct 16 '14 at 07:55

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

You could build an expression tree and create linq statements dynamically.

In this post, check Jorrit Steetskamp's answer to create an expression tree for group by and its usage.

You can similarly do something for select statement also as this post suggests.

Another option could be ESQL (Entity Sql) which uses more like a sql syntax to execute a query against the DB. Check this for example.

edited May 23 '17 at 11:51

Community

1
1

answered Oct 14 '14 at 17:06

Krishna Veeramachaneni

2,131
3
18
24

Thanks for the pointers. This looks very promising and i had been playing about with something like this before. How do i do a "where" on expression groupBy? – KevDevMan Oct 15 '14 at 08:07

Dynamically Create GroupBy and Select with EF

1 Answers1