Linq query issues - determining column at runtime

Question

I have a linq question (linq to sql). I have the following peice of code which works fine;

       var queryx = (from sa in d1.SampleAttributes
                      where nodeTable.ToList().Distinct().Contains(sa.client_post_code_prefix)
                     select sa.SampleId).Distinct();

Note: nodeTable is of type IQueryable

However, I would like to change this so that the column name within the contain method can be decided at runtime. I determine the column name from another query dependent on certain user filters being applied and would ideally like something with the follwing logic;

//please note that the string I pass into obtain a 'column object' always shares the same name as the column

        var columnWhatever = GetColumnName(string colName);

        var queryx = (from sa in d1.SampleAttributes
                      where nodeTable.ToList().Distinct().Contains(sa.client_post_code_prefix)
                     select sa.SampleId).Distinct();

So far I have been unable to find anything that will allow this and I'm beggining to think that Linq does not allow such logic. Any help would be much appreciated

dup? http://stackoverflow.com/questions/2148309/how-do-i-reference-a-field-in-linq-based-on-a-dynamic-fieldname — Rob Fonseca-Ensor, Jan 27 '10 at 16:23
@Rob - not necessarily; the first could be answered with reflection, but this may be something else... — Marc Gravell, Jan 27 '10 at 16:24
@saj - what is the underlying LINQ implementation here? LINQ-to-Objects? Entity Framework? LINQ-to-SQL? It matters, unfortunately. — Marc Gravell, Jan 27 '10 at 16:24
What is the type of `nodeTable`? Is this LINQ to SQL or LINQ to objects? — jason, Jan 27 '10 at 16:25
By the way, your `where` clause should be `nodeTable.Contains(sa.client_post_code_prefix)`. There's no point in calling `ToList().Distinct()`; it'll just waste time and memory. — SLaks, Jan 27 '10 at 16:25
thanks for the heads up SLaks, I think I started like you suggest but after trying all sorts ended up with what I put up — saj, Jan 27 '10 at 20:49

score 3 · Answer 1 · answered Jan 27 '10 at 16:19

3

You might take a look at the Dynamic LINQ library. I posted about it here. Take a look and see if that might help you. Also see Scott Guthrie's post about it here.

answered Jan 27 '10 at 16:19

Brian Sullivan

27,513
23
77
91

By far the worst of both worlds: dynamic sql and linq. great. – NotMe Jan 27 '10 at 16:30

jason · Answer 2 · 2010-01-27T16:35:59.040

If this is LINQ to object you can do this very easily using reflection. To wit:

string colName;
var queryx = (from sa in d1.SampleAttributes
              where nodeTable.Contains(
                                 sa.GetType()
                                   .GetProperty(colName)
                                   .GetValue(sa, null)
                                   .ToString()
                             )
              select sa.SampleId).Distinct();

This is assuming that nodeTable is an IEnumerable<string>.

It would be better to only perform the reflection piece once. Say that the compile-time type of sa is SampleAttribute. Then you could do the following:

string colName;
PropertyInfo info = typeof(SampleAttribute).GetProperty(colName);
Func<SampleAttribute, string> func = sa => info.GetValue(sa, null).ToString();
var queryx = (from sa in d1.SampleAttributes
              where nodeTable.Contains(func(sa))
              select sa.SampleId).Distinct();

If this is LINQ to SQL you can do this easily using System.Linq.Expressions.Expression. If you give a little more detail about the type of nodeTable I can walk you through this.

Hi Jason this is linq to sql. I'm assuming your answer would not apply to linq to sql although on first sight I can't understand why it would work on linq to object and not on linq to sql. — saj, Jan 27 '10 at 20:47

Linq query issues - determining column at runtime

2 Answers2