raw sql binging request.Filters

[j]

Hello Telerik team

For some reason，I want use raw sql get data by entity framework。

like this :  IQueryable<model> data =DbContext.model.SqlQuery("select * from models").AsQueryable();  //dbcontext  DbSet  

binding request filters : data = data.Where(ExpressionBuilder.Expression<model>(request.Filters,false));

 

Can someone help me why there is no effective?
 Thank you.

 

 

 

[Nencho]

Hello, 

I'm not completely sure about the issue with the demonstrated binding approach that are you experiencing and which is the component you aim to supply with data? Could you please elaborate a bit more on the matter?

I'm looking forward to your reply.

Regards,
Nencho
Progress Telerik

[j]

thank you for your replies very much

the component is   Grid.

This code is used to get the data source for  kendoGrid.

I get sql as well as raw sql （select * from models） when execute this line of code

" data = data.Where(ExpressionBuilder.Expression<model>(request.Filters,false));"

I don't understand how does the   expressionBuilder object work. 

If you make sure this method is feasible ，please give me a example for my requirement.

 

thank you very much!!!!

 

 

 

 

[Ianko]

Hello,

The Filters passed from the DataSource utility cannot be passed to the ExpressionBuilder. They are not designed to be used that way. This case is also addressed here: https://www.telerik.com/forums/expressionbuilder-expression(filters)-in-kendo-mvc-grid-not-working-for-navigation-collection#ITFMcVWPSU2ff_RctEDgVA. 

It is suggested to use the ToDataSourceResult method. As shown in this example:  https://demos.telerik.com/aspnet-mvc/grid/filter-row. That way the data will be properly queried and where possible the queries will be translated to SQL queries. However, not all operations are possible for SQL translations. This is why we recommend using the  ToDataSourceResult method.

Regards,
Ianko
Progress Telerik

[j]

thank you for your replies very much.

I got same problem using ToDataSourceResult method 

original code：

data = data.Where(ExpressionBuilder.Expression<VListOutStock>(request.Filters,false)); 

 

now code：

var result = data.ToDataSourceResult(request);

I use this code get raw sql

                oracleDbContext.Database.Log = (sql) =>
                {
                    if (string.IsNullOrEmpty(sql) == false)
                    {
                        Trace.WriteLine("************sql start*************");
                        Trace.WriteLine(sql);
                        Trace.WriteLine("************sql end************");
                    }
                }; 

 

the sql  is same as before.

but use “dbcontext .DbSet ”  replace “DbContext.model.SqlQuery("select * from models")”

the request's property   （filters 、page)  can reflect in sql  。

like this 

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

select * 

FROM ( SELECT *
FROM  "table" "Extent1"
 ((((LOWER("Extent1"."DATASTATUS")) = (LOWER('XX'))) AND ( NOT ((LOWER("Extent1"."DATASTATUS") IS NULL) OR (LOWER('XX') IS NULL)))) OR ((LOWER("Extent1"."DATASTATUS") IS NULL) AND (LOWER('XX') IS NULL)))
)  "Filter1"
WHERE ("Filter1"."row_number" > 0)
ORDER BY "Filter1"."BILLNO" ASC
)
WHERE (ROWNUM <= (100) )

[Ianko]

Hi,

Can you please elaborate more on the case as I am not sure what exactly is the requirment here or the issue with the ToDataSourceResult call? 

I suggest you providing a simple, locally runnable example that illustrates the case (you can use a very simple SQL DB) so that I can properly evaluate the case you have.

Regards,
Ianko
Progress Telerik