Craig Stuntz’s Weblog

If you spend enough time with the PagedList class that I’ve been using for paging in the method which supplies data to jqGrid, it’s a near-certainty that sooner or later you will see a LINQ error with Count in the call stack. The error may seem confusing, because it has nothing to do with Count. Commentor Graeme has been experimenting with my demo solution, and has run into just this issue:

Finding your blogs very helpful - superbly written - just found out about jqgrid last night and have integrated your code into my project.

One thing that didn’t work for me though was the dates fix above…

adding

.ToString(System.Globalization.CultureInfo.CurrentUICulture)

onto the end of my DateTime values makes everything render Ok but when I try doing a search, it fails. Debugging shows the error is

Method ‘System.String ToString(System.IFormatProvider)’ has no supported translation to SQL.

at the TotalItemCount = source.Count(); line of the PagedList.cs file.

Any ideas?

In order to understand this error, we need to remember several important things about LINQ:

• LINQ is lazy. Creating or modifying an IQueryable with methods like Where, Take, OrderBy, etc. does not actually execute the query. The query will only be executed when you call a LINQ method which requires execution to produce its results — like Count.
• When a LINQ query expression includes something which looks like a method call, the method is not invoked directly. Instead, what is actually produced is an Expression representing that method. If the LINQ provider happens to be LINQ to Objects, the method will eventually be invoked when the query is executed. But if it happens to be LINQ to SQL or LINQ to Entities, the method may never be invoked, and instead might be replaced (if possible) with equivalent SQL.
• Most LINQ providers do not support every possible LINQ query expression. Most providers don’t even support the entire LINQ API, and only LINQ to Objects can be presumed to support all method calls on an object in a query. In some cases, as with LINQ to Entities, this is documented. With other providers, you may have to guess/experiment. Any LINQ provider which has to translate a LINQ query expression into SQL syntax can only really support methods which it is hard-coded to recognize. Worse, it is not even uncommon for providers to support one overload of a particular method but not a different overload of the same method. See, for example, the LINQ to Entities document I just linked or the ToString method, which is generally supported in LINQ to Entities/SQL, but has a special overload on the DateTime type which is not supported. Worse still, method calls may be supported in some contexts but not others. Graeme reports that LINQ to SQL accepted the ToString overload in the context of a Select, but not in the context of a Where. This makes sense from an implementation point of view, but means programmers have to be extraordinarily careful about what we include in our LINQ queries, especially when they are constructed dynamically.

By now it should be obvious what is going on here: The particular overload of ToString which I used in my example is supported in LINQ to Objects but not in LINQ to SQL in a Where expression. My demo solution used a repository based on LINQ to Objects, so the query I showed worked correctly there. But it does not work with LINQ to SQL. Because LINQ is lazy, you will not actually see an error about this until the query is translated into SQL by the LINQ provider, and that does not happen until the call to Count in PagedList.

This means that when you write a LINQ query expression using LINQ to SQL, LINQ to Entities, etc., you are essentially working in a partially dynamic language. Unless you study the documentation very, very carefully, you will not know for certain until runtime if your query will be accepted by the provider.

So how could we work around this problem?

• Use a different method or a different overload which is supported by the LINQ provider used in your project. This is fine if an appropriate method/overload exists.
• Give up on doing the formatting in C# and use JavaScript instead. This will work, but it’s a bit like using beheading to cure acne. C# is, after all, a general-purpose programming language. We should not have to give up on formatting a date.
• Bring the query into LINQ to Objects using a method such as AsEnumerable. Obviously, we would want to apply the paging and ordering first. In this method, we would execute one query (using LINQ to SQL or something) which would retrieve an ordered list of records for the specified page, using AsEnumerable to transform the results into a List. We would then execute a LINQ to Objects query against this list in order to perform the formatting. This will actually work OK, although it can end up being a good bit of code to write for what should be a fairly simple task.
• Write a new LINQ provider which understands the method in question. This seems like an enormous amount of work, but I’ll include it for completeness.
• Separate the concerns of serialization and querying. You can tell by the 50-cent words that this is the solution I prefer.

Remember, the original query worked just fine. It is only the serialization to JavaScript which caused unexpected results. One way to fix this is to hide the members which are problematic to serialize and replace them with a string version of same:

private class JavaScriptDateFixer
{
    [NonSerialized]
    internal DateTime _date;
    public int Id { get; set; }
    public int IntProperty { get; set; }
    public string StringProperty { get; set; }
    public string DateProperty { get { return _date.ToString(System.Globalization.CultureInfo.CurrentUICulture); } }
}

public ActionResult GridDemoData(int page, int rows, string search, string sidx, string sord)
{
    var repository = new Repository();
    var model = from entity in repository.SelectAll().OrderBy(sidx + " " + sord)
                select new JavaScriptDateFixer
                {
                    Id = entity.Id,
                    IntProperty = entity.IntProperty,
                    StringProperty = entity.StringProperty,
                    _date = entity.DateProperty
                };
    return Json(model.ToJqGridData(page, rows, null, search,
        new[] { "IntProperty", "StringProperty", "DateProperty" }));
}

Note that even though this code looks quite different, this does almost exactly the same thing as the code I’ve replaced. The only real difference is that I’ve delayed the call to ToString until after the query is executed. That’s enough to keep LINQ to SQL or LINQ to Entities happy.

So with this fix is everything fine? Maybe. I really like LINQ; I think it’s an extraordinarily powerful tool. But here we have hit upon what I consider to be the single biggest problem with LINQ. The combination of very limited support for all possible query expressions in common LINQ providers like LINQ to SQL and building queries dynamically can be very dangerous. Unless you test all possible query permutations, you cannot be completely confident that any query you build will actually execute successfully. In typical dynamic language programming, you mitigate this problem by writing unit tests. But unit tests are not supposed to connect to a database. Unfortunately, the limitation here is that the point of translation to SQL. So any unit test which mocked the LINQ provider, say, using LINQ to Objects or some other in-memory representation would not actually encounter the limitations we are trying to test.

I have yet to see a really good solution for unit testing LINQ to SQL or LINQ to Entities, one which would combine the desired attributes of not actually connecting to the database while surfacing any potential problems in SQL generation. I suspect that such a beast may never exist for LINQ to SQL, but for LINQ to Entities it seems at least possible. One could write an Entity Framework provider which returns mocked instances of entities. Any LINQ syntax not understood by the Entity Framework would be caught before the mock provider was ever invoked. This is a feature I would like to see in a future version of the Entity Framework.

Share This | Email this page to a friend