CODEWITHSUNDEEP
c#sql-serverstored-proceduresodataasp.net-web-api2
Mashtani
Mashtani

Reputation: 641

How to apply OData query to stored procedures

I have a webapi project that use OData for query the result of api and I have a stored procedure in SQL Server like this.

CREATE procedure [dbo].[Something]
    (@select nvarchar(500)='select *', @query nvarchar(max)='')
as
begin
    declare @q nvarchar(max)

    set @q = @select + ' from #prs1 ' + @query;
    print @q

    EXECUTE sp_executesql @q
end

So I don't want to execute this stored procedure without any filter because of performance issue.

I want to know how to get translated OData query to SQL Server or query expression and pass them to my stored procedure.

My C# (Entity Framework) code:

var res = Context.Database.SqlQuery<Product>("Something @select,@query" new SqlParameter("@select", slct), new SqlParameter("@query", query));

Any help will be appreciated.

Upvotes: 0

Views: 4493

Answers (1)

snow_FFFFFF
snow_FFFFFF

Reputation: 3301

To add to @marc_s's comment above. The generic execute sql procedure is a bad idea. Also, the Web API/OData libraries are not generating SQL - EntityFramework is. If you want to intercept the odata query and use it to generate appropriate parameters for a stored procedure, look at adding ODataQueryOptions...

https://msdn.microsoft.com/en-us/library/jj890615(v=vs.118).aspx

...to your controller method. Use this to understand the request and generate an appropriate call to a procedure.

Upvotes: 1

Related Questions