Entity Framework no COUNT(*) and Top

[Terry Burns-Dyson]

I was previously using LinqToSql with the telerik grids and even though I was mapping the data out to view objects, the grid was working nicely in that it was selecting count(*) and then selecting top x records from that set.

Now, we've switched our software to use Entity Framework with Automapper doing those mappings. The grid is no longer requesting the top x results from the query and this is causing an 1100 item recordset to take almost 4 seconds to show. The actual query is taking 3.6 seconds of that because of all the join data I'm pulling through for the model. Is there something I'm missing preventing the grid from paging the data?

[Terry Burns-Dyson]

Any ideas on this?

I've run the software through EFProfiler, and even though I see that the enumeration isn't happening until the screen is being rendered, there's no Skip(x).Take(x) occurring. I'm getting 1400 items back and the grid is only displaying 10. 

I have to map the objects coming out of the database, so I don't know what is going wrong.

[Atanas Korchev]

Hello Terry Burns-Dyson,

 The grid will uses the IQueryable extension methods to page the data. If you however pass it an IList or force execution of the IQueryable by calling Count() or ToList() or ToArray() - the grid  won't hit the database at all after that. Without seeing your code we cannot be sure what is really happening.

 You can try calling Take() and Skip() manually and see what happens with the profiler.

All the best,
Atanas Korchev
the Telerik team

[Terry Burns-Dyson]

I was not returning them as IQueryable, with a lot of changes, I can get get it to SELECT COUNT(*) and SELECT TOP x, which is amazingly fast! But I now have another issue from this.

I read a recommendation by telerik on not returning entities right up to the screens, because the moment you use the Ajax select, you get a circular reference in the serialization. The recommendation was to map the objects. So, how do I map, yet still allow IQueryable from Entity Framework? 

If I use automapper, I get an error about not being able to convert Map to a store expression. If I hand code the objects, I'm ok, however if my database stores a integer and I want a timespan on the view, I can't do this because I can't use a new TimeSpan in the select clause of the EF. 

[Atanas Korchev]

Hello Terry Burns-Dyson,

I am afraid your only option is to implement custom binding. Manual mapping will work as long as you don't add additional properties.

Regards,
Atanas Korchev
the Telerik team

[Terry Burns-Dyson]

We chose to manually map out the objects we want to display. Custom Binding looked a little too involved and we've got short timescales in which to do some of this stuff. The performance, once we got the IQueryable up to the front end was extremely fast!

I thought I should update this post in order that someone else should read it with the same question.