CODEWITHSUNDEEP
c#asp.net-mvcasp.net-mvc-3datatables
Rory McCrossan
Rory McCrossan

Reputation: 337560

ToString() & Contains() have no supported translation to SQL

I have an action which returns JSON to a jQuery datatable. The data returned can be filtered depending on the params passed to it. Here's the code:

public ActionResult IndexAjaxHandler(jQueryDataTableParamModel parameters) {
    var data = _mailingListRep.GetAllWithinAccount();
    IEnumerable<MailingList> filtered;

    // Filtering - THIS DOES NOT WORK!
    if (!string.IsNullOrEmpty(parameters.sSearch)) {
        filtered = data.Where(c => 
            c.MailingListName.Contains(parameters.sSearch) ||
            ((c.IsDoubleOptIn) ? "Yes" : "No").Contains(parameters.sSearch, StringComparison.OrdinalIgnoreCase) ||
            c.Created.ToString("dd MMM yyyy").Contains(parameters.sSearch) ||
            c.ActiveMembers.Count().ToString().Contains(parameters.sSearch) ||
        c.AvailableSegments.Count().ToString().Contains(parameters.sSearch)
        );
    }
    else {
        filtered = data;
    }

    // create json object
    // return;
}

The problem is this gives me the following error:

Method 'Boolean Contains(System.String, System.String, System.StringComparison)' has no supported translation to SQL.

BUT

In the same controller is another action which also returns JSON to a datatable, which uses the exact same filtering method and it works fine! 

public ActionResult ViewDeletedAjaxHandler(int id, jQueryDataTableParamModel parameters) {
    var list = _mailingListRep.GetByIDWithinAccount(id);
    var data = list.DeletedMembers;
    IEnumerable<MailingListMember> filtered;

    // Filtering - THIS WORKS, NO ERRORS!
    if (!string.IsNullOrEmpty(parameters.sSearch)) {
        filtered = data.Where(c => 
            c.EmailAddress.Contains(parameters.sSearch, StringComparison.OrdinalIgnoreCase) ||
            c.UnsubscribedDate.ToString("dd MMM yyyy").Contains(parameters.sSearch, StringComparison.OrdinalIgnoreCase)
        );
    }
    else {
        filtered = data;
    }

    // create json object
    // return;
}

Does anyone know why this is?

For reference, Contains(string, comparison) is an extension method:

public static bool Contains(this string source, string toCheck, StringComparison comp) {
    return source.IndexOf(toCheck, comp) >= 0;
}

I understand what the error means, it is the fact that the code works in 1 action, but not the other than has me completely confused.

Thanks for any help in advance.

Upvotes: 1

Views: 3424

Answers (2)

Rune FS
Rune FS

Reputation: 21742

One way you could have this is if the runtime type of the data reference is different in those two scenarios.

E.g. if they both have the compile time type IEnumerable<T> but one has a runtime type that makes the LINQ into a LINQ2SQL and the other makes it into a LINQ2Object. Contains works in the latter but not the former

IEnumerable<Entity> data = db.GetMyLazilyEvaluatedListOfEntities();
//this will fail
data.Where(e => e.Foo.Contains("bar"));
data = new List<Entity>();
//this will work
data.Where(e => e.Foo.Contains("bar"));

where db.GetMyLazilyEvaluatedListOfEntities returns an object that implements IQueryable<Entity> that when evaluated will be translated into SQL

you can avoid the runtime exception if you call data.ToList() before the where. However this will fetch the entire table from the database and do the filtering in memory. If the table is with fewish rows that should be too much of a concern but if you expect a large number of records in the database table backing data you should rewrite into something that can be translated into SQL

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

Your first filter that does not work is applied to a IQueryable<T> and your second that works is applied to an IEnumerable<T>. When applied to an IQueryable<T>, the filter must be translateable to SQL, whereas when it is used on an IEnumerable<T> the filter is evaluated in memory.

To make the first one work, you need to either call ToList() to force the results into memory before applying the filter, or use the helper methods available from the EntityFunctions class.

Upvotes: 2

Related Questions