Dynamically refer to Entity Framework columns in IQueryable

Question

I know that some similar question were wandering around stack overflow however though none of them seem to work the way I want to. Here's the idea what I want to achieve:

// arrays vary depends what's in bigger loop
string[] columnNames = {"id", "name", "surname", "address", "phone"} 

// ...

foreach (string columnName in columnNames)
{
    if (condition)
    {
        IQueryable<tableType> query = from x in dbContext where x.(**columnName**).contains(otherVariable) select x;
    }
    // ...
    // Another queries wrapped in if conditions
}

I've tried this with typeof() and Type.GetProperty() which I found here and it seems not be working whatsoever. Another thing is I want this to be as much as possible in standards with current best practices and if I'm looking into wrong direction then where should I point to? I need to get this sort of method reusable for tens of hundreds of views and tables.

cheers,

Check this answer: https://stackoverflow.com/a/24732863/12258072 They use reflection (GetType().GetProperty(columnName).GetValue(x)) to access the values dynamically. — Longoon12000, Nov 13 '19 at 13:06
Problem with this solution is that it only takes values for that particular column in question whereas I need to pull data from IQueryable by applying WHERE condition to that column for further conditioned querying, if that makes any sense (?) — maxhustle, Nov 13 '19 at 13:46
Not sure if I understood that but then you maybe just store the query into a Dictionary indexed by the column name so you have a query for each one of your columns that you can then further refine. — Longoon12000, Nov 13 '19 at 14:01
it's not the matter of pulling just the data for the column by it's variable name it is applying where clause to column by its variable name with no strongly typed column name. As in this project there might be tens of thousands of column names, and its the contains/equal string clause I'm interested in. But at the end I have to get the whole table selected. — maxhustle, Nov 13 '19 at 14:27

score 1 · Answer 1 · answered Nov 13 '19 at 13:34

1

Instead of storing strings in your array, you should store expressions.

Expression<Func<TableType, bool>>[] conditions = new {
    x => x.ID > 5,
    x => x.Name > "M" };

etc. Than you can query

var query = dbContext.YourTable.Where(conditions[1])

That's a little simpler and easy to read, than using reflection or dynamics or whatever.

You can combine multiple conditions also, by multiple where clauses.

Since your sample implied a where clause, I had to invent some boolean expressions here.

Just don't start with storing strings (unless these strings are entered by the user)

answered Nov 13 '19 at 13:34

Holger

2,446
1
14
13

it maybe looks simpler and easy however though at this point I do not know what column names are hence I cannot strongly type the conditions into place. – maxhustle Nov 13 '19 at 13:48
So your IQueryable is not a known Type ? Your source sample started with column names defined as a constant ! Do you use LINQ to objects or LINQ to SQL, cause for SQL the Contains Method needs to be a list, and the entire Condition needs to be an Expression. So the list of Expressions need to be constructed at some place. I'm not sure if you want to use List.Contains or String.Contains with your condition. – Holger Nov 13 '19 at 15:18

score 0 · Accepted Answer · answered Nov 13 '19 at 15:13

0

I have to answer my question with link to another post answered by @Aleks Andreev

LINQ where condition with dynamic column

This is exactly what I was looking for.

answered Nov 13 '19 at 15:13

maxhustle

35
3

Dynamically refer to Entity Framework columns in IQueryable

2 Answers2