linq lambda expression for sql contains

Question

I am using a generic repository, like this:

 itemsList = (from myrow in UoW.FileRepository.Get()
 select new FileModel()
 {record_id = myrow.type_id,
  descr = myrow.descr}).ToList();});

And this is the Get method:

 public virtual IEnumerable<TEntity> Get()
    {
        //  _aQuery = _theDbContext.Set<TEntity>();
        IEnumerable<TEntity> query = _aQuery;
        return query;
    }

How would I implement a generic linq lambda expression if I wanted to create a similar query to search for a particular string in a particular field? In my viewmodel I would like to call something like:

     from myrow in UoW.FileRepository.Srch(nameofFieldToSearch, searchString). 

The query would look something like this?

   public IEnumerable<TEntity> Srch(Expression<Func<TEntity, bool>> expression)
    {
        IEnumerable<TEntity> srchList = _aQuery.ToList();
        return srchList.Where(????);

    }

Thank you for your suggestions.

EDIT----------------------- I have all my queries like Get and Srch in a general repository class and for now just need to know how to declare the query in the repository class and how to call it with the search string from my viewmodel. I am not sure if there is a consensus as to where/when to materialize and compile? I saw another discussion http://www.fascinatedwithsoftware.com/blog/post/2012/01/10/More-on-Expression-vs-Func-with-Entity-Framework.aspx and I quote from it below to inquire whether that is the same approach being suggested here? Thank you again.

"The profiler told us that LoadMyEntities was being called many, many times and it was taking a large fraction of our CPU time. The simple change below solved the problem. Can you guess why?"

public IEnumerable<MyEntity> LoadMyEntities(Func<MyEntity, bool> predicate)
{return Context.MyEntities.Where(predicate);}

"The parameter is now a Func<> instead of an Expression>. The reason this makes a difference is that a predicate that's in the form of an Expression is passed to SQL server, but a predicate that's passed as a Func is not. Normally, you'd want SQL Server to do as much for you as possible, and an Expression would be the right choice, but in this case we'd like to pre-load the entire table in the context -- which is exactly what a Func will do.

1. The Where extension method has two flavors. One extends IQueryable and takes an Expression parameter. The other extends IEnumerable and takes a Func.
2. Because 'predicate' is now a Func, the Where that extends IEnumerable is used.
3. The Entity Framework's fluent interface for constructing SQL queries is based on IQueryables, not IEnumerables. Therefore, the fluency stops just before the Where. The part of the statement that gets passed to the Entity Framework is just Context.MyEntities.
4. Context.MyEntities therefore returns the entire table to the context.
5. The entire table is now filtered with the predicate, and the value we really want is returned.
6. The next time the method is called, the Entity Framework realizes that the record we want is already in the context. (In my case, we were querying by the primary key, and EF is apparently smart enough to know that if there's a record in the context with that ID, it won't find an additional such record in the database.) Since we don't go out to SQL Server, we save lots of time. Obviously there are occasions when you would not want this, but in our case it was exactly what we wanted. The table was relatively small, and the same context was queried hundreds of times.

In the original version, the predicate was an Expression, so the compiler used the Where that extends IQueryable. The predicate was thus passed to SQL Server, which dutifully returned just one row to the context. The next time we called LoadMyEntities, Entity Framework had to call SQL Server again."

Answer 1

Please take a look at msdn repository pattern

 public virtual IEnumerable<TEntity> Get(
        Expression<Func<TEntity, bool>> filter = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
        string includeProperties = "")
    {
        IQueryable<TEntity> query = dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
        {
            query = query.Include(includeProperty);
        }

        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }

This will provide more complete Generic Get method.

For search you could Try

public IEnumerable<TEntity> GlobalSearch(Expression<Func<TEntity, bool>> expression)
{
   return Get(expression);
}

In DataProvider you could try something like this

Note This is not may be exact implementation, but will give you basic idea, how to invoke.

public List<Users> Search(List<string> userList)
{
      Expression<Func<User, bool>> expression = x=>UserList.Contains(x.UserName);

      return GlobalSearch(expression);

}

Example of Repository pattern

Answer 2

This would be a simple implementation using the Expression trees. Following is the complete solution:

Method to fetch the Expression for Srch method:

Public Expression<Func<TEntity, bool>> SrchExpression(string nameofFieldToSearch, string searchString)
{
  var parameterType = Expression.Parameter(typeof(TEntity), "obj");

  var memberExpression = Expression.Property(typeof(string), nameofFieldToSearch)

 // Calls Extension method created underneath
 var filtersMethodInfo = typeof(StringExtensions).GetMethod("Contains", new[] { typeof(string), typeof(string) });

 var filtersConstantExpression = Expression.Constant(searchString, typeof(string));

 var finalExpression = Expression.Call(null, filtersMethodInfo, memberExpression, filtersConstantExpression)

 return Expression.Lambda<Func<TEntity, bool>>(finalExpression, parameterType)
}

// Create a String extension method for Contains

public static class StringExtensions
{        
    public static bool Contains(this string source, string searchString)
    {
        return source?.IndexOf(subString, StringComparison.OrdinalIgnoreCase) >= 0;
    }
}

Now your Srch method shall look like:

public IEnumerable<TEntity> Srch(Expression<Func<TEntity, bool>> expression)
{
    Func<TEntity, bool>> func = expression.Compile();
    IEnumerable<TEntity> srchList = _aQuery.Where(o => func(o));
    return srchList;
}