Dynamic LINQ Builder

[Emrah]

Has Syncfusion framework an extension for dynamic linq builder? For example passing DataManager model to controller method and this extension make a new query from existing query with DataManager properties (sorting, where etc.) 

This feature is available on Telerik Kendo UI MVC, Extension name is ToDataSourseResult(). It's very usefull.

[Thavasianand Sankaranarayanan]

Hi Emrah, 

 

Thanks for contacting Syncfusion support. 

 

We have analyzed your query and we suspect that you want to return the query from model to controller. So, we suggest you to use the UrlAdaptor in Grid. 

 

While using UrlAdaptor, you can handle grid operation (paging/filtering/searching/sorting) in server side. When performing paging, filtering, sorting, editing operations on the grid queries are passed to the server side using DataManager class and using DataOperations class methods you perform server-side operations. 

 

Refer the below code example. 

 

 

[GridFeatures.cshtml] 

@(Html.EJ().Grid<OrdersView>("FlatGrid") 

   .Datasource(ds => ds.URL("/Grid/DataSource").Adaptor(AdaptorType.UrlAdaptor)) 

   .AllowPaging(true).PageSettings(page => page.PageSize(8)) 

   .AllowGrouping(false)                              

   .AllowSorting()    

 

    --------------- 

 

  .Columns(col => 

            { 

                ----------- 

 

            }) 

                            ) 

 

 

 

[GridController.cs] 

public ActionResult DataSource(DataManager dm) 

        { 

            IEnumerable DataSource = new NorthwindDataContext().OrdersViews.ToList(); 

            DataResult result = new DataResult(); 

            DataOperations operation = new DataOperations(); 

            result.result = DataSource; 

 

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 

            { 

                result.result  = operation.PerformSorting(result.result, dm.Sorted); 

            } 

 

            result.count = result.result.AsQueryable().Count(); 

 

            if (dm.Skip > 0)  // for paging  

 

                result.result = operation.PerformSkip(result.result, dm.Skip); 

 

            if (dm.Take > 0) 

 

                result.result = operation.PerformTake(result.result, dm.Take); 

 

            return Json(new { result = result.result, count = result.count });        } 

 

 

 

 

We have prepared a sample and it can be downloadable from the below location. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/129418/ze/SyncfusionMvcApplication123-1210684995 

Refer the screen shot. 

 

 

The response from server should be wrapped in an object with properties named result to hold the data and count to hold the total records count. To handle the server-side operations, we have Server-side APIs which has been found in the DataOperations class of Syncfusion Libraries. Refer to the following KB.  

Note: skip/take are the queries responsible for handling the paging action and it returns current page records to the Grid. 

 

Kb Link: https://www.syncfusion.com/kb/4300/server-side-api-for-datamanager-operations  

 

Refer the help documentation. 

 

Link: https://help.syncfusion.com/aspnetmvc/grid/data-adaptors#url-adaptor   

 

NOTE:  

 

In given code example of yours, having the CrudUrl, BatchUrl, insert, delete, remove Urls. No need to use all at the same time in Grid . 

 

CRUD URL: Crud Url is used instead of specifying methods for Crud (Insert, Update, Delete) operations. 

 

BATCH URL: BatchURL property supports only for batch editing mode. 

 

Refer the below documentations for the Insert, Update, Delete, Crud, Batch URL’s using in Grid. 

 

Link: https://help.syncfusion.com/aspnetmvc/grid/editing#url-adaptor 

 

Link: https://help.syncfusion.com/aspnetmvc/grid/editing#crud-url 

 

Link: https://help.syncfusion.com/aspnetmvc/grid/editing#batch-url 

 

 

Regards, 

Thavasianand S. 

[Emrah]

Hi,

Thank you for answer.

This way you offer I execute query server side but some tables include over billion rows, so I can't get all data to application server, I need to do paging table on database.

How can I do this?

Regards.

[Thavasianand Sankaranarayanan]

Hi Emrah, 

We have analyzed your query and we suspect that you want to retrieve only the initial page record from the datatable. Currently, we don’t have inbuilt support for IQueryable with Data Adaptors and DataOperation class. We have already added it to our feature request list. It will be included in any of our upcoming release. 

 

Regards, 

Thavasianand S. 

[Emrah]

Hello again,

I've written this extension and for now my problem seem to be resolved. I can do paging and other query options on database with DataManager class.

Thank you

public static DataResult ToResult<TEntity>(this IQueryable<TEntity> query, DataManager dm)
        {
            var result = new DataResult();
            var op = new DataOperations();
 
            if (dm.Sorted == null)
            {
                query = SetSorting(query, new List<Sort> { new Sort { Name = "Id", Direction = "descending" } });
            }
 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
            {
                query = (IQueryable<TEntity>)op.PerformSorting(query, dm.Sorted);
            }
            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                query = (IQueryable<TEntity>)op.PerformWhereFilter(query, dm.Where, dm.Where[0].Operator);
            }
 
            result.count = query.AsQueryable().Count();
 
            if (dm.Skip > 0)
                query = (IQueryable<TEntity>)op.PerformSkip(query, dm.Skip);
            if (dm.Take > 0)
                query = (IQueryable<TEntity>)op.PerformTake(query, dm.Take);
 
            result.result = query.ToList();
 
            return result;
        }

[Emrah]

And that is SetSorting method below:

private static IQueryable<TEntity> SetSorting<TEntity>(IQueryable<TEntity> query, List<Sort> sorted)
        {
            var parameter = Expression.Parameter(typeof(TEntity), "p");
            Expression resultExpression = null;
            foreach (var item in sorted)
            {
                string command = "OrderBy";
 
                if (item.Direction.ToLower() == "descending")
                {
                    command = "OrderByDescending";
                }
                var property = typeof(TEntity).GetProperty(item.Name);
                var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                var orderByExpression = Expression.Lambda(propertyAccess, parameter);
                resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { typeof(TEntity), property.PropertyType },
               query.Expression, Expression.Quote(orderByExpression));
                query = query.Provider.CreateQuery<TEntity>(resultExpression);
            }
            return query;
        }

[Thavasianand Sankaranarayanan]

Hi Emrah, 

We are happy that the problem has been solved. 

 

Please get back to us if you need any further assistance.  

 

Regards, 

Thavasianand S.