Using jqGrid with ASP.NET MVC: Understanding LINQ Errors
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 myDateTimevalues 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 theTotalItemCount = 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 — likeCount. - 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
Expressionrepresenting 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
ToStringmethod, which is generally supported in LINQ to Entities/SQL, but has a special overload on theDateTimetype which is not supported. Worse still, method calls may be supported in some contexts but not others. Graeme reports that LINQ to SQL accepted theToStringoverload in the context of aSelect, but not in the context of aWhere. 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, usingAsEnumerableto 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.
Comments
-
Tuesday, 30 June 2009Thank you for the tutorials that you have posted so far. They are very helpful. Are you still planning on continuing with the series or have you decided to stop? Thanks.
-
Monday, 5 October 2009
You might want to look at the good work jason dentler has done for making sqlite a good provider for an in memory database for unit testing.
-
Sunday, 18 October 2009
This has been a *great*series of articles and just what I needed to get some "real" (read: not HTML) grids into my MVC applications.
I have a question about a problem I am having with a simple Entity Framework implementation I am doing with MVC 2.0 P2. I think/hope this is something simple, and I don't think (though I could be wrong) that this is related to the issues you describe above.
I have a simple controller action that does the following:
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
{
IntProperty = entity.IntProperty,
StringProperty = entity.StringProperty,
DateProperty = entity.DateProperty.ToString(System.Globalization.CultureInfo.CurrentUICulture)
};
*/
var dc = new InvestorEntities();
var model = dc.Investor
//.Include("Organization")
//.Include("LastModifiedUser")
.Where(i => !i.IsDeleted);
JsonResult r = Json(model.ToJqGridData(page, rows, sidx + " " + sord, search,
new[] { "InvestorID", "IsDeleted" }));
return r;
}
And here is my complete jqGrid definition:
$(function() {
$('#investorGrid').jqGrid({
url: '',
imgpath: '',
datatype: 'json',
height: 'auto',
jsonReader: {
root: 'Rows',
page: 'Page',
total: 'Total',
records: 'Records',
repeatitems: false,
userdata: 'UserData',
id: 'InvestorID'
},
loadui: 'block',
mtype: 'GET',
multiboxonly: true,
rowNum: 5,
rowList: [5, 10, 15, 20],
viewrecords: true,
multiselect: false,
caption: 'Investors',
colNames: ['Investor ID', 'Is Deleted'],
colModel: [
{ name: 'InvestorID', index: 'InvestorID' },
{ name: 'IsDeleted', index: 'IsDeleted' }
],
pager: $('#investorPager'),
sortname: 'InvestorID',
sortorder: 'asc'
}).navGrid('#investorPager', { edit: false, add: false, del: false, search: false });
$('#investorSearch').filterGrid('#' + grid.attr('InvestorID'), {
gridModel: false,
filterModel: [{
label: 'Search',
name: 'search',
stype: 'text'
}]
});
});
The problem I run into here is that my data gets blocked/lost somewhere between "return r" - at which point I have a filled object hierarchy - and showing up on the screen. I don't get any error - I just get no rows.
For a while, I thought that perhaps the default JsonRequest.JsonRequestBehavior of "DenyGet" was getting me, but explicitly setting this to "AllowGet" right before "return r" in my action doesn't change anything.
I tried swapping out my datatype of json for local and using a javascript array and it worked fine, so I think my jqGrid definition is good. I am just at my wit's end here and I am hoping you have seen this before.
Thanks in advance for your time and once again - great articles!
Tony -
Sunday, 18 October 2009
Ah, I love Fiddler. Turns out that the objection here is that the example is using a GET and MVC (or is it ASP.NET?) blocks that by default as a cross-site security risk. Now I just have to figure out how to get the jqGrid to send it's parameters as a POST. :-)
-
Sunday, 18 October 2009
And, armed with that data, it's easier to find the cause. Turns out this is a known, breaking change by the MVC team in 2.0 P2 - which I am using.
ASP.NET MVC Preview 2 Changes to Prevent JSON Hijacking
Phil Haack recently blogged about a very subtle JSON vulnerability and a related JSON Hijacking problem that can hijack sensitive data returned by a JSON GET. He outlines the problem very well and in concrete detail - and even supplies a sample project you can use to see the vulnerabilities in action yourself. So I won't detail the issue here, other than to say that he raises a very serious specter, which is that GETs for JSON data can be prone to a very subtle, yet vicious, security vulnerability.
Given Phil's role in defining the direction of ASP.NET MVC, it's therefore not surprising to see that Preview2 of ASP.NET MVC 2 (up on codeplex.com) introduces a breaking change to address this. Specifically, as the release notes outline, an ASP.NET MVC JsonResult will now only respond to HttpPost requests.
http://www.devproconnections.com/tabId/180/itemId/4587/Security-Changes-in-ASPNET-MVC-V2-Good-News-for.aspx
http://haacked.com/archive/2008/11/20/anatomy-of-a-subtle-json-vulnerability.aspx
Now I just have to figure out how to get all these GET examples converted to working POST examples. :-) -
Wednesday, 20 July 2011
[...] Using jqGrid with ASP.NET MVC: Introduction Using jqGrid with ASP.NET MVC: LINQ Extensions Using jqGrid with ASP.NET MVC: Finally, A Solution Using jqGrid with ASP.NET MVC: Search and Formatting Using jqGrid with ASP.NET MVC: Deleting Records Using jqGrid with ASP.NET MVC: Understanding LINQ Errors [...]
-
Please login first in order for you to submit comments
- Page :
- 1
First of all i would like to thank you for your tutorial. It really helped me to get started using jqGrid with ASP.NET MVC.. I would like to see more scenarios using jqGrid and one of them that i couldn't figure out is using subgrid.. it will be of real help to see a tutorial on this..
thanks