SQL query with params values

[rbalagangadharan]

Hi All,

I am working on dynamic OData with EF core. I have use case to get the SQL query from the OData options. I achieved this by

var sql = queryOptions.ApplyTo(queryable, querySettings).ToQueryString()

It gives me below result

-- @__TypedProperty_0='2'
-- @__TypedProperty_1='ABC'
SELECT EmpId, EmpName, EmpAge FROM public."Employee"
WHERE EmpId = @__TypedProperty_0 and EmpName = @__TypedProperty_1

is it possible to get the SQL query with param values like below

SELECT EmpId, EmpName, EmpAge FROM public."Employee" WHERE EmpId = 2 and EmpName = 'ABC'

[julealgon]

This is not an OData question but an EntityFrameworkCore question. The translation between IQueryable and the SQL query is performed by EF and OData has no say about it.

Having said that, what you want to do is a really bad practice that opens you up for SQL injection. I don't immediately see a way to "configure" this in EFCore but I wouldn't open up that possibility if I was them either.

Why do you need inlined parameters like that?

[rbalagangadharan]

Agreed on that. Just for my information.

[julealgon]

@rbalagangadharan I realized this yesterday when looking at a different problem, but there may be something you can play around with here.

The ODataQuerySettings object (the querySettings variable in your example) has a property called EnableConstantParameterization. It is set to true by default, but you could try setting it to false and see if that produces the effect you want.

AspNetCoreOData/src/Microsoft.AspNetCore.OData/Query/ODataQuerySettings.cs

Lines 91 to 96 in 1918b56

	/// <summary>
	/// Gets or sets a value indicating whether constants should be parameterized. Parameterizing constants
	/// would result in better performance with Entity framework.
	/// </summary>
	/// <value>The default value is <c>true</c>.</value>
	public bool EnableConstantParameterization { get; set; }

I've never used that option and wasn't aware of it when I first answered this, so apologies for the confusion if it ends up working.

[rbalagangadharan]

@julealgon Yes, I tried that, and it works fine. Also, I see one more issue when giving the alias name in $compute. If user request for
http://localhost:64771/v1/Department?$compute=DepartmentId add 2 as NewColumn&$select=NewColumn,DepartmentName

The Sql query generated is not coming with proper alias name. It generates below

SELECT d."DepartmentName", d."DepartmentId" + 2, d."DepartmentId"
FROM public."Department" AS d

In above, the alias name NewColumn is not appending in SQL Query