MVC Linq Query with dynamic column name in WHERE clause [duplicate]

Question

I have a number of calls for the same query but with slightly different WHERE clause, does anyone know if it's possible to pass a variable as the column name as I can't seem to achieve it.

I know the below isn't correct but just to give you an idea of what i'm trying to achieve.

public EmailUserListViewModel EmailUserListData(int CaseId, string ColumnName)
{
    CaseId = CaseId,
    EmailUserList = (from u in efContext.PgsUsers
                        where ColumnName == true
                        orderby u.FirstName, u.LastName
                        select new EmailUserListModel
                        {
                            UserId = u.Id,
                            Name = ((u.FirstName == null) ? "" : u.FirstName) 
                                   + ((u.LastName == null) ? "" : " " + u.LastName),
                            Email = u.Email,
                            Checked = false
    
                        }).ToList()
    };
}

You need to use the 'u' variable in the Where. So something like u == ColumnName — jdweng
– jdweng, Commented Jan 19, 2017 at 12:48

Zaphod Fox · Accepted Answer · 2017-01-19 12:47:02Z

2

I suppose you could use Reflection to dynamically retrieve the value of the property

from u in efContext.PgsUsers where (typeof(PgsUser).GetProperty(ColumnName).GetValue(u) as bool) == true

or

from u in efContext.PgsUsers where (u.GetType().GetProperty(ColumnName).GetValue(u) as bool) == true

answered Jan 19, 2017 at 12:47

Zaphod Fox

581 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jammer Over a year ago

I've tried something similar to this and get the same error for them: 'System.Reflection.PropertyInfo GetProperty(System.String)' has no supported translation to SQL.

Zaphod Fox Over a year ago

The Reflection filter is not SQL compatible, you have to apply it to a ToList()ed version of your query.

Maksim Simkin · Accepted Answer · 2017-01-19 12:48:24Z

1

You could write such method:

public Expression<Func<T, bool>> getExpression<T>(string columnName)
{
    var param = Expression.Parameter(typeof(T));
    var equal = Expression.Equal(Expression.Property(param, columnName), Expression.Constant(true));
    return (Expression<Func<T, bool>>)Expression.Lambda(equal, param);
}

and use it in where:

where getExpression<YourType>("ColumnName")

answered Jan 19, 2017 at 12:48

Maksim Simkin

9,7084 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

MVC Linq Query with dynamic column name in WHERE clause [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related