"Only arguments that can be evaluated on the client are supported for the String.Contains method"

Question

public static void MyFunction(MyErrorClass err)
{
    var query = from filter in DataContext.ErrorFilters select filter;
    query = query.Where(f => err.ErrorMessage.Contains(f.ErrorMessage));
    List<ErrorFilter> filters = query.ToList();
    //...more code
}

So I'm having some issues with the above code, and I'm getting the error from the subject line at the line with query.ToList(). Here's what I'm trying to do:

First off, I have a custom error class, MyErrorClass. Whenever an error occurs on my site, I create a MyErrorClass object from the exception, store all the data from the exception in that object, and store the information in the database.

One of the exception properties I am keeping track of is the message for the error (ErrorMessage). I have an ErrorFilters table set up in the database where the user can filter errors based on the ErrorMessage. So say you are getting a ton of errors that say "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.", and you want to ignore them. You just add a filter to the database with the ErrorMessage as "timeout expired", and set it to ignore.

Now, my class above is set to take an error, and decide if the error should be filtered. I'm trying to get a list of all filters that have an ErrorMessage matching that of the error.

I'm sure this is an easy fix, I just don't know how to fix it.

Answer 1

Hmm... It seems the Linq2SQL IndexOf translation is smarter than for Contains. This should work:

public static void MyFunction(MyErrorClass err)
{
    var query = DataContext.ErrorFilters;
    query = query.Where(f => err.ErrorMessage.IndexOf(f.ErrorMessage)>=0);
    List<ErrorFilter> filters = query.ToList();
    //...more code
}

In LinqPad it can be seen this uses CHARINDEX, because we've asked for more than just "contains", rather "where is it", but it is happy to work with server-side expressions.

Answer 2

It seems you should be using f.ErrorMessage.Contains(err.ErrorMessage) - linq to sql should then convert this to WHERE ErrorFilter.ErrorMessage LIKE %err.ErrorMessage%. The problem with the way you have it is that the generated SQL would need a dynamic string to match in the where clause, and hence could only be filtered on the client.

Incidently, the var query = from filter in DataContext.ErrorFilters select filter; line is not required and you can just do:

var filters = DataContext.ErrorFilters.Where(f => f.ErrorMessage.Contains(err.ErrorMessage)).ToList();

EDIT:

Ok I see what you're trying to do now, but I'm not sure if this is possible in linq2sql. You could create a stored procedure and add that to your datacontext and do the mapping from the output to a sequence of ErrorFilter objects:

create procedure GetMatchingFilters @message varchar(500)
as
begin
    select  *
    from ErrorFilter
    where @message LIKE '%'+ErrorMessage+'%'
end

Then in your datacontext you can do:

DataContext
    .GetMatchingFilters(err.ErrorMessage)
    .Select(result => new ErrorFilter {...})
    .ToList();