RadGrid model binding calling SQL twice

[Mihai]

Hello,

ISSUE: Every time I call the Rebind() method my select method is called two times. 

GIVEN:
- using Telerik ASP.NET Ajax Q1 2013.
- using RadGrid with model binding.
- select method is GetList().

EXTRA: From the method's description (Rebind) I understand that it calls two methods: 1. NeedDataSouce & 2. DataBind

QUESTION: How can this behavior be avoided?(select method should always be called once)

[Jayesh Goyani]

Hello,

When the page load first time it will automatically called the needdatasource event. (no need to call this event manually to bind your grid at page load at first time).

Please check below link for reference.
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/needdatasource/defaultcs.aspx


In-addition also check below link, that will call rebind() automatically.
http://www.telerik.com/help/aspnet-ajax/grid-commands-that-invoke-rebind-implicitly.html


Thanks,
Jayesh Goyani

[Mihai]

Thanks Jayesh!

Unfortunatelly none of your solutions is valid.

I'll explain again:
The select method has the folowing signature: public IQueriable<Student> GetList()
The select method is called two times whenever the rebind() method is called(no matter the caller).
Example:

1. Call radgrid.Rebind() from any method
2. GetList is called(automatically)
3. GetList is called(automatically, second time)

My idea was that the rebind method is calling the select method two times because it internally calls two methods(radgrid.NeedDataSource and radgrid.DataBaind)..so one call to the select method for each of the methods it internally calls.

I want to avoid the double call to the select method.

[Jayesh Goyani]

Hello,

RadGrid.Rebind() Method called the needdatasource event.

Needdatasource event called the DataBind() Method at the end of code/ add Sender.Databind() code at the end of code.

DataSource :  storage and retrieval of dynamic data -->Ex : this will get data from from database

DataBind() : Calling the DataBind method has the effect of triggering the DataBinding event for all the controls in scope  --> Ex: this method is responsible to create rows and column in Grid By using assign Datasource. it will not get data from database.

Thanks,
Jayesh Goyani

[Mihai]

After researching the web I found the folowing Telerik link: http://www.telerik.com/help/aspnet-ajax/grid-model-binding-and-strongly-typed-data-controls-support.html

It explains the following: RadGrid calls its SelectMethod twice (once for the grid and once for the MasterTableView) although SelectMethod for the MasterTableView is not declared. The select method must be called twice because the RadGrid calls its base.DataBind() and then the MasterTableVeiw calls its base.DataBind() method. So the framework calls SelectMethod twice, however only one database query is executed to get all data for building items in the MasterTableVeiw.

The statement only one database query is executed to get all data  is not entirely true. By using MSSQL Profiler one can notice that the SQL Select query(used in the select method) is indeed called two times.

Any suggestions?

[Maria Ilieva]

Hello Mihai,

The mentioned help topic statement is exactly what we are experiencing on our end and namely only one database query is executed for the MasterTableView. I suppose that the issue on your end is cause by the profiler setting which could show the same query part of different processes. You could run the profile through SQL data source or NeedDataSource to observe the same result.

Regards,
Maria Ilieva
the Telerik team

[Mihai]

Thanks Maria,

The thing I found weird was that on changing the current page the select method(and the sql SP) was called only once.

[Jon]

I'm seeing two calls to my SelectMethod as well. I set a break point in the debugger and it's hit twice every time I do a fresh load of the page. At first I thought it was because I had if (!IsPostBack) DataBind(); in my Page_Load method. However, I commented that out and found that it is still calling the method twice. Unless I can stop this from happening, it will for all practical purposes make using model binding unacceptable. I have lots of grids all over the place in my application. I can't afford to be executing two queries everywhere only one query is needed. I think it's unfortunate that Telerik doesn't seem to place much importance on getting model binding to work with the grid.

 Another thing I've noticed is that although the SelectMethod returns an IQueryable<T>, it doesn't apply sorting, filtering, and paging at the database-level. IMHO, this should all happen transparently. Instead, RadGrid reads the entire data set into memory and then does the filtering. Model binding with RadGrid could work much better IMHO. I really wish Telerik would prioritize this and put some resources into making it work the way it should work.

[Jon]

Also, Microsoft is going to be dropping support for EntityDataSource in Entity Framework 7. This is one reason why I think Telerik needs to get their act together and make RadGrid work properly with model binding. EntityDataSource will automatically apply the filtering, sorting, and paging at the database-level. IMHO, using model binding should work equally easy and well. I figured out how to implement this using System.Linq.Dynamic. I had to do some tweaking to the RadGrid.MasterTableView.FilterExpression to get it to work though. IMHO, Telerik needs to document custom paging better. The example given on the web site, disables custom paging when filtering is enabled. It took me awhile to figure out how to do it due to the documentation not being as good as it should have been. Also, if there is any documentation still around using LINQ to SQL, etc., it really needs to be updated. I don't know if it's still the case, but, I know there were a lot of examples, using LINQ to SQL and old ways of doing things while the documentation lacked for Entity Framework (the current standard database API for .NET).

[Cyril Iselin]

Hello jon

 

i have the same issues... You wrote about linq dynamic how do you get it working? Because the grid wont return the filter linq expression, it returns the sql expression 😢 can you share your solution?

[Jon]

The first thing you need to do is go under "Manage NuGet Packages..." and add a reference to System.Linq.Dynamic. There is documentation for it at http://msdn.microsoft.com/en-US/vstudio/bb894665.aspx. For the documentation on dynamic LINQ expressions, see "Dynamic Expressions.html" in the DynamicQuery sample.

You need to set EnableLinqExpressions="true" in the grid. Assuming you are trying to get this to work with LINQ to Entities and you want paging, filtering, and sorting to be performed at the database-level, you need to set AllowCustomPaging="true". I'm using the following.

<p><telerik:RadGrid ID="AccountsRadGrid" runat="server" AutoGenerateColumns="false" AllowSorting="true" AllowFilteringByColumn="true" GroupingSettings-CaseSensitive="false" AllowPaging="true" AllowCustomPaging="true" PageSize="100" EnableLinqExpressions="true" OnNeedDataSource="AccountsRadGrid_NeedDataSource" OnItemCommand="AccountsRadGrid_ItemCommand"> </p>

In the code behind file, I'm using the following. 

protected void AccountsRadGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
var _s = Stopwatch.StartNew();
var q = from a in libraryContext.Accounts
select new
{
a.Id,
a.Name,
a.Number,
a.Code,
a.CreationTime,
a.CreationUserName,
a.LastWriteTime,
a.LastWriteUserName
};
if (AccountsRadGrid.MasterTableView.FilterExpression != "") q = q.Where(Global.ConvertLinqExpressionToLinqToEntitiesExpression(AccountsRadGrid.MasterTableView.FilterExpression));
q = AccountsRadGrid.MasterTableView.SortExpressions.Count > 0 ? q.OrderBy(AccountsRadGrid.MasterTableView.SortExpressions[0].ToString()) : q.OrderBy("Id");
AccountsRadGrid.VirtualItemCount = q.Count();
q = q.Skip(AccountsRadGrid.PageSize * AccountsRadGrid.CurrentPageIndex).Take(AccountsRadGrid.PageSize);
AccountsRadGrid.DataSource = q.ToArray();
traceSource.TraceEvent(TraceEventType.Verbose, 0, $"{_s.Elapsed} elapsed");
} 

There are a couple things in the LINQ expression that the grid generates that are incompatible with LINQ to Entities. So, I had to create a function change it around a little. There were two main issues. 1. DateTime fields were using DateTime.Parse() which doesn't work with LINQ to Entities. I converted that into the syntax that dynamic LINQ uses for expressions. 2. It was using the ToUppper() method to make comparisons case insensitive. I'm using a database that does comparisons insensitive by default. So, I stripped that out of there. Otherwise, it would have prevented it from using indexes in the database and was causing it to generate SQL that was a lot more complicated than necessary.

public static string ConvertLinqExpressionToLinqToEntitiesExpression(string expression)
{
expression = Regex.Replace(expression, @"iif\((?<Property>.+?) == null, """", .+?\).ToString\(\)", "${Property}");
expression = expression.Replace(".ToUpper()", "");
foreach (Match m in Regex.Matches(expression, @"DateTime.Parse\(""(?<DateTime>.*?)""\)"))
{
var dt = DateTime.Parse(m.Groups["DateTime"].Value);
expression = expression.Replace(m.Value, $"DateTime({dt.Ticks})");
}
return expression;
}

 

[Cyril Iselin]

Thanks !!

 

I allready know this solution. But with modelbinding EnableLinqExpression don't have any effect :(, as FilterExpression you will recive a SQL Expression. But yes with need-Datasource it works.

 

Thanks

[Jon]

Good catch. I just tried it myself and noticed the same thing. Seems like a bug to me. There is another problem with model binding with RadGrid. You can't sort on navigation property columns. Personally, I think that needs to be fixed. Also, I think as long as you return an IQuerable<T> from the SelectMethod, RadGrid should add the filters on automatically and apply them at the database-level. It is pretty lame IMHO how it works now. Namely, reading everything into RAM first and then filtering. This doesn't seem like something that would be difficult to implement and it would make things a lot easier for users of the control. I don't think Telerik cares much about model  binding. I think they added it when Microsoft added support for it and that was about it. Personally, I wish Telerik would focus more on things like this than adding more features to an HTML editor, etc.

[Jon]

The good thing about NeedDataSource is that you can do projections and only pull in the data you need. With model binding you are pulling in full entities. However, I prefer model binding for inserts and updates because you don't have to read out all the values out of a Dictionary.

[Cyril Iselin]

Hello Jon

 

Seems like a bug to me​

 I wrote a ticket about this some weeks ago. The answer was like "it's a Feature not a bug" ;)

 When you have Iqueryable it Looks like the EnableLinqExpression don't have any effect, it only have an effect if you bind to IEnumerable.

It is pretty lame IMHO how it works now

That's also my opinion. When we Need a Grid ? When we have to handle a big amount of data. But there are only 2 ways to handle this correctly with radgrid I think.

=> EntityDataSource (works well, but I won't use it because I also think the Support will be dropped on EF 7)

=> Need_DataSource Event in combination with DynamicLinq (some issues with DateTime and not very "nice" but "works")

 

I think they added it when Microsoft added support for it and that was about it

Telerik wrote me, ModelBinding don't Support filtering (internal "view") and if they will implement it, it isn't modelbinding anymore. I wish there will be a Method like RadGridInstance.ApplyGridSettingsToIqueryable(myQueryable), where sorting filtering and paging will be applyed.

 

Best Greetings

 

 

 

 

 

[Simone]

Hello!

Any news on this topic? Telerik RadGrid still call the SelectMethod two times.

Thanks.