Hello,
I need to do remote paging on a dynamic result set (both SQL & the selected columns are dynamic), but the JSON returned from server side is:
{"Records":[{},{},{},{},{},{},{},{},{},{},{},
{},{},{},{},{},{},{},{},{}],
"TotalRecordsCount":41,"Metadata":{"timezoneOffset":28800000}}
"TotalRecordsCount":41,"Metadata"
:{"timezoneOffset":28800000}}
Razor code:
@( Html.Infragistics().Grid<MVC3EntityFramework.Customer>()
.AutoGenerateColumns(true)
.Columns(column =>
{
column.For(x => x.CustomerID).DataType("string").HeaderText("Customer ID");
column.For(x => x.CompanyName).DataType("string").HeaderText("Company Name");
column.For(x => x.ContactTitle).DataType("string").HeaderText("Contact Title");
column.For(x => x.ContactName).DataType("string").HeaderText("Contact Name");
column.For(x => x.Country).DataType("string").HeaderText("Country");
})
.Features(features =>
features.Paging().PageSize(20).PrevPageLabelText("Previous").NextPageLabelText("NEXT");
features.Sorting().Mode(SortingMode.Single).ColumnSettings(settings =>
settings.ColumnSetting().ColumnKey("AccountNumber").AllowSorting(true);
});
features.Selection().MouseDragSelect(true).MultipleSelection(true).Mode(SelectionMode.Row);
.DataSourceUrl(Url.Action("CustomerList"))
.Width("100%")
.Height("350px")
.DataBind()
.Render()
)
Controller method:
[GridDataSourceAction]
public ActionResult CustomerList()
return View(_dataContext.Database.SqlQuery<dynamic>("select * from customers").AsQueryable() );
}
Hello yuanmai,
Try using your model type "MVC3EntityFramework.Customer" as a type argument of System.Data.Entity.Database.SqlQuery method instead of "dynamic" keyword.For example your controller method should look like this: [GridDataSourceAction]public ActionResult CustomerList(){ return View(_dataContext.Database.SqlQuery<MVC3EntityFramework.Customer>("select * from customers").AsQueryable() );}
Hope this helps,Martin PavlovInfragistics
Martin,
The SQL"select * from customers" is actually generated by my client's module at runtime based on user's input, it could be any table or any columns or with a bunch of joins, so _dataContext.Database.SqlQuery<MVC3EntityFramework.Customer> won't meet my need.
Thanks
Yuan
Hello Yuan,
To answer you I'll cite our documentation "All data binding in the MVC grid wrapper is done using LINQ. Therefore the DataSource property only accepts instances of IQueryable."
Here is the source topic:
http://help.infragistics.com/Help/Doc/jQuery/2011.2/CLR4.0/html/igGrid_Developing_ASP_NET_MVC_Applications_with_igGrid.html
Best regards,
Martin Pavlov
Infragistics
The root cause is System.Data.Entity.Database.SqlQuery<dynamic> result could not be converted to JSON, can I do remote paging in igGrid without using IQueryable?
Thanks!
If I understand you correctly you want System.Data.Entity.Database.SqlQuery<dynamic> to return anonymous object with properties based on columns in the SQL which your program dynamically generate based on user input. This won't work with "dynamic". (At least I didn't manage to make it work.) You should pass strongly typed object there.
From what I see if I pass strongly typed object to System.Data.Entity.Database.SqlQuery and enlist only subset of the columns in SQL which describe that object then the method throws an error.
One workaround I can think of to avoid the error is that in SQL you should list all the columns of the mapped object and for each column you don't want data for you should put "null" or ''.
Example:
"select CustomerID, CompanyName, null as ContactTitle, null as ContactName, null as Country from customers".
This query will be optimized by the database and the columns "ContactTitle", "ContactName", "Country" won't be searched in the database because they are constants. JSON data however will contain entries for this columns, so it won't be optimal for the network traffic.
Hope this helps,