CODEWITHSUNDEEP
sqllinqentity-framework
captonssj
captonssj

Reputation: 311

dynamically change LINQ to Entity query

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' ?

Upvotes: 3

Views: 723

Answers (5)

Adrian Iftode
Adrian Iftode

Reputation: 15663

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)");

Upvotes: 0

Jacek Gorgoń
Jacek Gorgoń

Reputation: 3214

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.

Upvotes: 3

usr
usr

Reputation: 171188

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.

Upvotes: 1

k06a
k06a

Reputation: 18785

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);

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

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.

Upvotes: 1

Related Questions