In the sample, if I type "Mar" it finds start dates but "March" does not. Why is this? about datatables.queryable HOT 1 CLOSED

@VictorioBerra this is because of different formatting of dates used on server and client side. In the example above, there are 2 formats:

• JavaScript: MMMM dd, yyyy
• SQL Server Compact: MMM d yyyy hh:mmtt

When DataTables.Queryable builds an expression tree, by default it uses ToString() method for any non-string property of model. This C# expression:

.Where(p => p.StartDate.ToString().Contans("Mar"))

is translated to following SQL query:

...
WHERE ( CAST( [Extent1].[StartDate] AS nvarchar(4000)) IS NOT NULL) AND ( CAST( [Extent1].[StartDate] AS nvarchar(4000)) LIKE N'%Mar%')
...

Casting to nvarchar causes date to be formatted like: Mar 9 2011 12:00AM.
That's why searching for "Mar" works but "March" does not. You should use custom search predicate to tell DataTables.Queryable to use correct format.
Because EntityFramework is used, some limitations are exist. For example, you can not use StartDate.ToString("MMMM dd, yyyy") because it can not be translated to SQL query correctly.

Instead you should write:

var request = new DataTablesRequest<Person>(Request.QueryString);
var dateColumn = request.Columns[p => p.StartDate];
var searchValue = dateColumn.SearchValue;

// Force searching by StartDate with format "MMMM dd, yyyy"
dateColumn.ColumnSearchPredicate = p => (
    SqlCeFunctions.DateName("month", p.StartDate) + " " + 
    SqlCeFunctions.Replicate("0", 2 - SqlCeFunctions.DateName("dd", p.StartDate).Length) + 
    SqlCeFunctions.DateName("dd", p.StartDate) + ", " + 
    SqlCeFunctions.DateName("yyyy", p.StartDate)).Contains(searchValue);

using (var ctx = new DatabaseContext())
{
    var persons = ctx.Persons.Include("Office.Address").ToPagedList(request);
    return JsonDataTable(persons);
}

Of course if DataTables.Queryable is used with in-memory data, you can simply write:

dateColumn.ColumnSearchPredicate = p => p.StartDate.ToString("MMMM dd, yyyy").Contains(searchValue);

from datatables.queryable.