dynamically change LINQ to Entity query

Question

int year = 2009; // get summ of TONS2009 column

var query = from ODInfo in DataContext.CIMS_TRUCKS
            where pLocationIDs.Contains(ODInfo.OID) 
            group ODInfo by ODInfo.OID into g
            select new
            {
               OID = g.Key,
               TotalTons = g.Sum( ODInfo => ODInfo.TONS2009)
            };

IN the expression 'ODInfo => ODInfo.TONS2009', how do I change TONS2009 to TONS2010 or TONS2011 based on the method parameter 'int year' ?

Just a question: you can't normalize it in your data model? Such columns are a notorious anti-pattern. — Gert Arnold, Mar 19 '12 at 08:53
Normalize how ? Can you please explain a bit more? I have commodity data for more than 22 different years. — captonssj, Mar 19 '12 at 18:47
22 different years x 4 different modes [truck , air, water and rail]. Too many tables. — captonssj, Mar 19 '12 at 18:54
Yes, tons of tables and columns, I'm sure. At least make a YEAR column and TONS column and query Where YEAR = 2009 etc. — Gert Arnold, Mar 19 '12 at 18:58

score 3 · Accepted Answer · edited May 23 '17 at 11:48

K06a's answer is close but won't work server-side. Try this:

IEnumerable<OutputType> myQuery(IEnumerable<InputType> data, Expression<Func<InputType,decimal>> expr)
{
    return from ODInfo in DataContext.CIMS_TRUCKS
           where pLocationIDs.Contains(ODInfo.OID) 
           group ODInfo by ODInfo.OID into g
           select new OutputType
          {
              OID = g.Key,
              TotalTons = g.AsQueryable().Sum(expr)
           };
}

var query = myQuery(DataContext.CIMS_TRUCKS, ODInfo => ODInfo.TONS2009);

I haven't tried this, but did something similar here.

UPDATE

If you really need to translate input strings (like "2009") to expressions, it's still possible:

string year = "2009";

Type ODInfoType = typeof(ODINFOTYPE); // substitute with the type of ODInfo
ParameterExpression pe = ParameterExpression.Parameter(ODInfoType, "ODInfo");
MemberInfo mi = ODInfoType.GetProperty("TONS" + year);
MemberExpression me = Expression.MakeMemberAccess(pe, mi);
var expr = Expression.Lambda<Func<ODINFOTYPE, decimal>>(me, pe);

Be aware that this is a patch to the extremly evil structure of your database.

I still have to use "ODInfo => ODInfo.TONS2009" right ? How do I vary that? Cretae Expression using Expression class ? Cna you give an example? — captonssj, Mar 19 '12 at 20:23

score 1 · Answer 2 · edited Mar 19 '12 at 06:55

1

You can try something like that:

TotalTons = g.Sum( ODInfo => (year == 2009) ? ODInfo.TONS2009 : ((year == 2010) 
                                            ? ODInfo.TONS2010 : ODInfo.TONS2011))

Or make it more readable and use { } to split that lambda expression into more then one line and use eg. switch statement.

edited Mar 19 '12 at 06:55

gideon

19,329
11
72
113

answered Mar 19 '12 at 06:52

MarcinJuraszek

124,003
15
196
263

Is this gonna translate to SQL? The switch isn't for sure. – Jacek Gorgoń Mar 19 '12 at 11:24
Why wouldn't it work? I certainly can be fixed if anything small was wrong with it. – usr Mar 19 '12 at 22:30
@usr I tried it and it didn't work; I have edited the question above. – captonssj Mar 20 '12 at 02:42
1

The switch wouldn't work, because a lambda with a statement body can't be converted into an expression. See also http://stackoverflow.com/questions/1432941/c-sharp-switch-in-lambda-expression . – Jacek Gorgoń Mar 20 '12 at 12:45
No switch is necessary, just as in the example given. – usr Mar 20 '12 at 20:09

score 1 · Answer 3 · answered Mar 19 '12 at 22:33

The best solution is to break this up into multiple querys that you can compose to a final query:

int year = 2009; // get summ of TONS2009 column

var odInfos =
   year == 2009 ? DataContext.CIMS_TRUCKS.Select(x => new { x.OID, TONS = x.TONS2009 })
   year == 2010 ? DataContext.CIMS_TRUCKS.Select(x => new { x.OID, TONS = x.TONS2010 })
   year == 2011 ? DataContext.CIMS_TRUCKS.Select(x => new { x.OID, TONS = x.TONS2011 })
   : null;

var query = from ODInfo in odInfos
            where pLocationIDs.Contains(ODInfo.OID) 
            group ODInfo by ODInfo.OID into g
            select new
            {
               OID = g.Key,
               TotalTons = g.Sum(ODInfo => ODInfo.TONS)
            };

This will specialize to three possible queries at runtime, thereby giving the best possible performance. It is better than a case-switch.

score 0 · Answer 4 · edited Mar 19 '12 at 22:26

0

Try this way:

IEnumerable<OutputType> myQuery(IEnumerable<InputType> data, Func<InputType,decimal> func)
{
    return from ODInfo in data
           where pLocationIDs.Contains(ODInfo.OID) 
           group ODInfo by ODInfo.OID into g
           select new OutputType
           {
              OID = g.Key,
              TotalTons = g.Sum(func)
           };
}

var query = myQuery(DataContext.CIMS_TRUCKS, ODInfo => ODInfo.TONS2009);

edited Mar 19 '12 at 22:26

Kamyar Nazeri

25,786
15
50
87

answered Mar 19 '12 at 08:43

k06a

17,755
10
70
110

I don't believe Expressions or Delegates could be used that way in EF, unless you bring the whole data into memory via .AsEnumerable() – Kamyar Nazeri Mar 19 '12 at 08:51

score 0 · Answer 5 · answered Mar 20 '12 at 11:16

0

Using DynamicLinq which works with EF also:

int year = 2009; // get summ of TONS2009 column

var query = from ODInfo in DataContext.CIMS_TRUCKS
            where pLocationIDs.Contains(ODInfo.OID) 
            group ODInfo by ODInfo.OID into g
            select g;

var projectedGroups = query.Select("new (Key as OID, Sum(TONS" + year + ") as TotalTons)");

answered Mar 20 '12 at 11:16

Adrian Iftode

15,465
4
48
73

Error 5 The type arguments for method 'System.Linq.Enumerable.Select(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly. C:\Projects\CIMS\FL_CIMS\Src\FDOTCIMSViewer\FDOTCIMSViewer\WcfServiceTestService\Models\CIMSRepository.cs 239 39 FDOTCIMSService – captonssj Mar 20 '12 at 18:51

dynamically change LINQ to Entity query

5 Answers5