No supported translation to SQL

Question

We have this code:

private IList<InfoRequest> GetBy(Func<InformationRequest, string> func, string searchby)
{
    var requests = _dc.InformationRequests
                      .Where(x => func.Invoke(x).Contains(searchby))
                      .OrderBy(y => y.RequestDate);

    return Mapper.Map<InformationRequest[], InfoRequest[]>(requests.ToArray());
}

It continues to throw the no supported translation to SQL error. Any ideas on the problem or how to resolve it?

Answer 1

I would take the advice found in this link, Convert your func to an expression and that will result in a differnt overloaded .Where method getting called.

 private IList<InfoRequest> GetBy(Expression<Func<InformationRequest, string>> exp, string searchby)
 {

      var requests = _dc.InformationRequests
                  .Where(x => exp(x).Contains(searchby))

Answer 2

I ended up with this:

 private static Expression<Func<T, bool>> StartsWith<T>(Func<string, string> func)
{
    var searchBy = func.Method.GetParameters()[0].Name;
    var search = Expression.Constant(func(null), typeof(string));

    var searchByParam = Expression.Parameter(typeof(T), searchBy);
    var searchByExp = Expression.Property(searchByParam, searchBy);

    var methodInfo = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });//, typeof(StringComparison)});
    var containsExpression = Expression.Call(searchByExp, methodInfo, search);

    return Expression.Lambda<Func<T, bool>>(containsExpression, searchByParam);
}

If you want more details, I blogged it here: http://derans.blogspot.com/2010/05/building-l2s-expression-with-net-35.html