How to solve LINQ to Entity query duplication when the queries only differ by a property?

Question

I have two DbSets, Foo and Bar. Foo has an identifying string property, FooName, and Bar has an identifying string property, BarName.

I am designing a very simple search feature, where a user's query term can either be equal to, or contained in the identifying name.

So I have two methods (heavily simplified):

public ActionView SearchFoo(string query) 
{
    var equalsQuery = db.Foo.Where(f => f.FooName.Equals(query));
    var containsQuery = db.Foo.Where(f => f.FooName.Contains(query)).Take(10); // Don't want too many or else a search for "a" would yield too many results

    var result = equalsQuery.Union(containsQuery).ToList();
    ... // go on to return a view
}


public ActionView SearchBar(string query) 
{
    var equalsQuery = db.Bar.Where(f => f.BarName.Equals(query));
    var containsQuery = db.Bar.Where(f => f.BarName.Contains(query)).Take(10); // Don't want too many or else a search for "a" would yield too many results

    var result = equalsQuery.Union(containsQuery).ToList();
    ... // go on to return a view
}

Clearly I want some helper method like so:

public IList<T> Search<T>(string query, DbSet<T> set) 
{
    var equalsQuery = set.Where(f => ???.Equals(query));
    var containsQuery = set.Where(f => ???.Contains(query)).Take(10); // Don't want too many or else a search for "a" would yield too many results

    var result = equalsQuery.Union(containsQuery).ToList();
    ... // go on to return a view
}

I originally tried to add a Func<T, string> to the Search parameters, where I could use f => f.FooName and b => b.BarName respectively, but LINQ to Entities doesn't support a lambda expression during the execution of the query.

I've been scratching my head as to how I can extract this duplication.

Are you using CodeFirst? – Farhad Jabiyev Mar 19 '17 at 06:37 — Farhad Jabiyev, Mar 19 '17 at 06:37

Ofir Winegarten · Answer 1 · 2017-03-19T07:37:11.460

You can achieve this with Expression<Funt<T,string>>

public IList<T> Search<T>(string query, DbSet<T> set, Expression<Func<T, string>> propExp)
{
    MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    ConstantExpression someValue = Expression.Constant(query, typeof(string));
    MethodCallExpression containsMethodExp = 
             Expression.Call(propExp.Body, method, someValue);
    var e = (Expression<Func<T, bool>>)
              Expression.Lambda(containsMethodExp, propExp.Parameters.ToArray());
    var containsQuery = set.Where(e).Take(10);

    BinaryExpression equalExpression = Expression.Equal(propExp.Body, someValue);
    e = (Expression<Func<T, bool>>)
                 Expression.Lambda(equalExpression, propExp.Parameters.ToArray());

    var equalsQuery =  set.Where(e);

    var result = equalsQuery.Union(containsQuery).ToList();
}

Then you'll call it:

Search ("myValue", fooSet, foo=>foo.FooName);

if you can have a static method, then you could have it as an extension method:

public static IList<T> Search<T>(this DbSet<T> set, 
                                  string query, Expression<Func<T, string>> propExp)

And call it:

FooSet.Search ("myValue", foo=>foo.FooName);

Farhad Jabiyev · Answer 2 · 2017-03-19T10:49:47.923

0

You can create interface:

public interface IName
{ 
    string Name { get; set; }
}

Then implicitly implement IName interface in both entities.

 public class Bar : IName { ... }
 public class Foo : IName { ... }

And then change your method as:

public IList<T> SearchByName<T>(string query, DbSet<T> set) 
      where T: class, IName
{
    var equalsQuery = set.Where(f => f.Name.Equals(query));
    var containsQuery = set.Where(f => f.Name.Contains(query)).Take(10); // Don't want too many or else a search for "a" would yield too many results

    var result = equalsQuery.Union(containsQuery).ToList();
    ... // go on to return a view
}

edited Mar 19 '17 at 10:49

answered Mar 19 '17 at 06:39

Farhad Jabiyev

26,014
8
72
98

1

Unfortunately this works in EF query only if the classes have `public string Name { get; set; }`. Does not work if you implement interface explicitly and try to map `FooName` for instance. – Ivan Stoev Mar 19 '17 at 10:48
@IvanStoev Thanks for information. I have updated my answer. – Farhad Jabiyev Mar 19 '17 at 10:50

cpr43 · Answer 3 · 2017-03-19T18:09:54.907

0

You could overide your ToString() method and use that in the query

public class foo
{
    public string FooName
    {
        get;
        set;
    }

    public override string ToString()
    {
        return FooName;
    }
}

public class Bar
{
    public string BarName
    {
        get;
        set;
    }

    public override string ToString()
    {
        return BarName;
    }
}

public IList<T> Search<T>(string query, DbSet<T> set)
{
    var equalsQuery = set.AsEnumerable().Where(f => f.ToString().Equals(query));
    var containsQuery = set.AsEnumerable().Where(f => f.ToString().Contains(query)).Take(10); 
    var result = equalsQuery.Union(containsQuery).ToList(); . . . // go on to return a view
}

edited Mar 19 '17 at 18:09

answered Mar 19 '17 at 06:45

cpr43

2,942
1
18
18

I like the simplicity of this approach, although I'm not sure if this is an appropriate use of ToString() in my application. But I suppose if FooName and BarName are identifying strings anyway, then it might make sense ToString() returns their values. – NicholasFolk Mar 19 '17 at 17:42
@NicholasFolk am happy that you liked this approach – cpr43 Mar 19 '17 at 17:47
I tried your approach and it doesn't work as is. I was able to get it working with "AsEnumerable()" but not sure how this effects performance. See: http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities – NicholasFolk Mar 19 '17 at 17:55
@NicholasFolk .. sorry my bad . I forgot that this is a dbcall and its Iqueryable .As you said this will have a huge performance issue. Please dont use this method. Will be deleting this answer soon – cpr43 Mar 19 '17 at 18:11

score 0 · Answer 4 · answered Mar 19 '17 at 08:35

Here's one way to do it. First you need a helper method to generate the Expression for you:

private Expression<Func<T, bool>> GetExpression<T>(string propertyName, string propertyValue, string operatorName)
{
    var parameterExp = Expression.Parameter(typeof(T));
    var propertyExp = Expression.Property(parameterExp, propertyName);
    MethodInfo method = typeof(string).GetMethod(operatorName, new[] { typeof(string) });
    var someValue = Expression.Constant(propertyValue, typeof(string));
    var methodExp = Expression.Call(propertyExp, method, someValue);

    return Expression.Lambda<Func<T, bool>>(methodExp, parameterExp);
}

This is how you can use this method, propertyName would be FooName and BarName:

public IList<T> Search<T>(string propertyName, string query, DbSet<T> set) 
{
    var equalsQuery = set.Where(GetExpression<T>(propertyName, query, "Equals"));
    var containsQuery = set.Where(GetExpression<T>(propertyName, query, "Contains")).Take(10); // Don't want too many or else a search for "a" would yield too many results

    var result = equalsQuery.Union(containsQuery).ToList();
    return result;
}

How to solve LINQ to Entity query duplication when the queries only differ by a property?

4 Answers4