C# Linq Query on database using field name as a variable

Question

Here is my code. Can anyone help me refactor this piece of code. i'm trying to use field name as variable so I wouldn't have to use switch case. Something like from dr in db.Users where dr.FieldName.Contains(value).

public static List<User> GetUser(string value, string fieldName)
{
    var db = new CCPTDataContext();
    var users = new List<User>();

    switch (fieldName)
    {
        case "FirstName":
            users = (from dr in db.Users where dr.FirstName.Contains(value)
                     select new User
                     {
                         Username = dr.Username,
                         FirstName = dr.FirstName,
                         LastName = dr.LastName
                     }).ToList();

            break;
        case "LastName":
            users = (from dr in db.Users where dr.LastName.Contains(value)
                     select new User
                     {
                         Username = dr.Username,
                         FirstName = dr.FirstName,
                         LastName = dr.LastName
                     }).ToList();
            break;
    }
    return users;
}

Take a look at this similar question

Wonko the Sane
– Wonko the Sane

2014-01-16 18:12:04 +00:00
Commented Jan 16, 2014 at 18:12 — Wonko the Sane
– Wonko the Sane, Commented Jan 16, 2014 at 18:12

MarcinJuraszek · Accepted Answer · 2014-01-16 18:16:52Z

4

You have to create the Expression Tree manually using classes from System.Linq.Expression namespace:

public static Expression<Func<TSource, bool>> GetPropertyContainsValueExpression<TSource>(string propertyName, string value)
{
    var param = Expression.Parameter(typeof(TSource), "x");
    var prop = Expression.Property(param, propertyName);
    var valueExp = Expression.Constant(value, typeof(string));
    var contains = Expression.Call(prop, "Contains", null, valueExp);
    return Expression.Lambda<Func<TSource, bool>>(contains, param);
}

then you can use it like that:

public static List<User> GetUser(string value, string fieldName)
{
    var db = new CCPTDataContext();

    return db.Users.Where(GetPropertyContainsValueExpression<User>(fieldName, value)).ToList();
}

answered Jan 16, 2014 at 18:16

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

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

4 Comments

felipe Over a year ago

Some providers (I think EF 5) might not correctly parameterize the query in the above snippet. In order to get the parameterized queries, what I do is wrap the value in a Tuple and get the Item1 property:

var container = new Tuple<string>(value); var containerExpression = Expression.Constant(container, typeof(Tuple<string>)); var valueExp = Expression.Property(containerExpression, "Item1");

MarcinJuraszek Over a year ago

@Kosar you're welcome! You should consider marking the answer as accepted if it solved your problem. That's how SO works!

Ali Over a year ago

@Marcin, I tried to mark as accepted, couldn't find any link, button or something like that for it. Can you guide me how to do that.

MarcinJuraszek Over a year ago

@Kosar Right below post score the should be gray mark you can click. Here is a great question about that: meta.stackexchange.com/questions/5234/…

Obi · Accepted Answer · 2014-01-16 18:13:14Z

0

This should work:

users = (from dr in db.Users 
where (fieldName.Equals("FirstName") && dr.FirstName.Contains(value))
      || (fieldName.Equals("LastName") && dr.LastName.Contains(value))
                 select new User
                 {
                     Username = dr.Username,
                     FirstName = dr.FirstName,
                     LastName = dr.LastName
                 }).ToList();

answered Jan 16, 2014 at 18:13

Obi

3,0914 gold badges38 silver badges58 bronze badges

Collectives™ on Stack Overflow

C# Linq Query on database using field name as a variable

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related