Using Lambda Expression to Select different fields from field names

Question

I need to get two fields from a database table (retrieved using linq-to-sql), one field is a datetime (and is a fixed field) and the other is always a decimal, but the field can be different.

The table holds currency data which is processed twice a day and in different currencies so could have fields such as AM_USD, PM_USD, AM_EUR etc. And I need to get data such as a list of the date against PM_USD or the date against AM_EUR.

I would like to be able to call the data using a lambda expression for example (this is a stripped out example):

data = TableData.Select(x=>new {x.DateTimeAdded, x.[**field name as string**]});

I have been trying to write a function to do this, and am failing dismally.

The closest I have managed is:

private Func<TableData, KeyValuePair<DateTime, decimal>> CreateSelect(string FieldName)
{
    var parameterExp = Expression.Parameter(typeof(TableData), "sel");
    var dateParameter = Expression.Parameter(typeof(DateTime), "DateTimeAdded");
    var fieldParameter = Expression.Parameter(typeof(decimal), FieldName);
    ConstructorInfo constructorInfo = typeof(KeyValuePair<DateTime, decimal>).GetConstructor(new[] { typeof(DateTime), typeof(decimal) });
    NewExpression constructExpression = Expression.New(constructorInfo, new ParameterExpression[] { dateParameter, fieldParameter});

    var lambda = Expression.Lambda<Func<TableData, KeyValuePair<DateTime, decimal>>>( constructExpression, parameterExp);

    return lambda.Compile();
}

Which fails with "System.InvalidOperationException: Lambda Parameter not in scope".

I'm sure I missing something obvious, or going about it wrong way.

Any ideas?

Thanks T

Safir

Is it fair to assume that you cannot alter that terrible schema for the data? – Mcleroy

Answer 1

x.Foo is a member of x (property or field), not a parameter:

private Func<TableData, KeyValuePair<DateTime, decimal>> CreateSelect(string FieldName)
{
    var parameterExp = Expression.Parameter(typeof(TableData), "sel");
    var dateProp = Expression.PropertyOrField(parameterExp, "DateTimeAdded"); 
    var fieldProp = Expression.PropertyOrField(parameterExp, FieldName);
    ConstructorInfo constructorInfo = typeof(KeyValuePair<DateTime, decimal>).GetConstructor(new[] { typeof(DateTime), typeof(decimal) });
    NewExpression constructExpression = Expression.New(constructorInfo, new [] { dateProp, fieldProp});

    var lambda = Expression.Lambda<Func<TableData, KeyValuePair<DateTime, decimal>>>( constructExpression, parameterExp);

    return lambda.Compile();
}

Enteron

Hi Marc, this is a great answer and is returning the correct epxression, but now I am going to sound dumb... How do I call this method? var data = TableData.Select(CreateSelect("fieldname")); comes back with a System.ArgumentNullExpception and ask me to "Try specifing the type arguments explicity" Thanks T – Safir

@Marc - It will not let me compile the code. This line works ok: var tst = CreateSelect("AMUSD"); But if I try something like data.Select(CreateSelect("AMUSD")); it will not let me compile. – Safir

@Marc the full error: The type arguments for method 'System.Linq.Enumerable.Select<TSource,TResult>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int,TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly – Safir

@Toby - can you give a reproducible example? i.e. what is data ? This works fine for me: – Enteron

IQueryable<TableData> source = new[] { new TableData { DateTimeAdded = DateTime.Today, Foo = 123.45M}, new TableData { DateTimeAdded = DateTime.Now, Foo = 678.90M}, }.AsQueryable(); var projection = source.Select(CreateSelect("Foo")); var dictionary = projection.ToDictionary(x => x.Key, x => x.Value); – Enteron

@Marc - hmmm, I have just tested your code with a different project, but with the same linq-to-sql data access layer and this works exaclty as described (and I have done similar before with where expressions), so I guess I'm doing something really wrong, or this project may need updating a little. Many thanks for all your help. Toby – Safir

@Marc... Ooops, I was reference two classes with the same name from different namespaces (which I had not realised where in both namespaces, the second namespace was only added yesterday), this was causing the page to fail as there was a conversion issue! You live and learn... (#MyBad) – Safir

Answer 2

From your question:

data = TableData.Select(x=>new {x.DateTimeAdded, x.[**field name as string**]});

Specifying the field name in a LINQ query as a string can be done by using the LINQ Dynamic Query Library.

You can use the DynamicQuery library against any LINQ data provider (including LINQ to SQL, LINQ to Objects, LINQ to XML, LINQ to Entities, LINQ to SharePoint, LINQ to TerraServer, etc). Instead of using language operators or type-safe lambda extension methods to construct your LINQ queries, the dynamic query library provides you with string based extension methods that you can pass any string expression into.

And by the way, even thought the question isn't exactly identical, the answer is pretty much the same.

Counterattack

an answer; there is no the answer here. – Enteron

@Marc Gravell: not sure if I just missed out on a gramatical joke or something (English not being my native language), but I linked to a specific answer, and I cannot see anything wrong in referring to that as the answer (as in the answer that I linked to)... – Counterattack

I read it as "the answer to this category of question", where "the" (being the singular, definite article) refers to "the only possible, sane, sensible answer to this category of question". It isn't important, but linguistically, perhaps "this" would have been clearer than "the". – Enteron

@Marc Gravell: well, if I meant to give "the only possible, sane, sensible answer", that would clearly be 42, right ;) – Counterattack