Pass property name into method as a string .NET

Question

I'm using LINQ to SQL for a .NET MVC app.

In my db i have a bunch of columns that have "Y" or "N" values in them.

I'm trying to write a general purpose method that will return all the records in the db where columnName == 'Y'

So far i've got this

public IQueryable<Approved> ReturnRecordsByObjectiveFlag(string columnName)
{       
    return from approved in db.Approved
           where approved.GetType().GetProperties().Where(x => x.Name.Equals(columnName)).Equals("Y")
           select approved;
}

But that gives me this error

Member access 'System.String Name' of 'System.Reflection.MemberInfo' not legal on type 'System.Reflection.PropertyInfo[].

Any idea where i may be going wrong?

Thank you.

Answer 1

public IQueryable<Approved> ReturnRecordsByObjectiveFlag(string columnName)
{   
    var param = Expression.Parameter(typeof(Approved), "x");
    var predicate = Expression.Lambda<Func<Approved,bool>>(
        Expression.Equal(
            Expression.PropertyOrField(param, columnName),
            Expression.Constant("Y")
        ), param);
    return db.Approved.Where(predicate);
}

---

Edit; if the properties are actually char?, i.e. 'Y' not "Y", then:

public IQueryable<Approved> ReturnRecordsByObjectiveFlag(string columnName)
{
    var param = Expression.Parameter(typeof(Approved), "x");
    Expression prop;
    var predicate = Expression.Lambda<Func<Approved, bool>>(
        Expression.Equal(
            (prop = Expression.PropertyOrField(param, columnName)),
            Expression.Constant(prop.Type == typeof(string) ?
                (object)"Y" : (object)'Y', prop.Type)
        ), param);
    return db.Approved.Where(predicate);
}

(this now copes with all of string, char and char?

Answer 2

• GetType() returns Type

• GetProperties() returns PropertyInfo[]

• Where(..) returns IEnumerable<PropertyInfo>

so you calls

IEnumerable<PropertyInfo>.Equals(string)

that seems to be wrong. Doesn't it?

Answer 3

That is because the last Equals method runs on a filtered IQueryable collection, instead of the property value itself.

Try this instead:

return from approved in db.Approved
               where approved.GetType().GetProperties().Where(x => x.Name.Equals(columnName) && (string)x.GetValue(db.Approved, null) == "Y")
               select approved;

Answer 4

What is going wrong is that you can't use reflection inside a LINQ query.

How many columns are there - is it a fixed relatively small number? If so use a switch statement to choose between a number of LINQ queries. This is cleaner and much quicker.

If it's a large number of columns, or they are dynamic, you could construct a dynamic SQL statement, but be careful to check for possible injection attacks.

Answer 5

approved.GetType().GetProperties()

will return a PropertyInfo[] object. Even when you filter it using a Where it's still not the value of the property, it's basically a collection of properties that satisfy the critirion, even if that collection has only one member.

To get a specific property by name, you better use the GetProperty() method of the Type class, like this:

PropertyInfo pi = approved.GetType().GetProperty(columnName);

but, you're not done yet. You still have to filter the objects by the value of the property, and that is done by using the GetValue() method on the property, like this:

bool isApproved = pi.GetValue(approved, null).Equals("Y");

or to put everything in your context:

return from approved in db.Approved
where approved.GetType().GetProperty(columnName)
                     .GetValue(approved, null).Equals("Y")
select approved;