C# stored procedure with dynamic select values an Calling using Entity

Ramy Atef

I want to make a stored procedure to filter by passing columns and conditions in run time

t use this code this run but when i calling by Entity FrameWork must passing all columns in table to run but I do not like this i want to select a column different every executes how do this?

```ALTER proc [dbo].[spDynamicFilteration](

@ColumnsNames nvarchar(MAX),

@Conditions nvarchar(MAX)=null)

begin

EXEC ('select '+@ColumnsNames+' from TableName where ' +@Conditions+' ')

return

end ```

Calling by EF i used this

``` string data = "Id,TunnelId,VehicleTypedId,CategoryId,TotalCash,VisaCount,TunnelId,ShiftId,Date,LaneId,TotalCash,CashCount,TotalVisa,VisaCount";

string a = "ShiftId = 1 And Id = 78";

var d = entities.Filteration(data, "Id = " + a).FirstOrDefault();```

and this ` var orders = entities.Database.SqlQuery("EXEC spDynamicFilteration {0},{1}", data, a).FirstOrDefault();

but stale same problem required all columns in table pass to run

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kiran Mohanty5y ago
Select Id,TunnelId,VehicleTypedId,CategoryId,TotalCash,VisaCount,TunnelId,ShiftId,Date, LaneId,TotalCash,CashCount,TotalVisa,VisaCount from TableName where ShiftId = 1 And Id = 78.

is this query running for you in your database ? If yes , try changing type to object as below.
entities.Database.SqlQuery<object>("EXEC spDynamicFilteration {0},{1}", data, a).FirstOrDefault();
Is it returning any data ?
Ramy Atef5y ago
the proble when i calling stored procedures using Entity Framework request passing all columns name to run if i not send column name ocure error

this error show
System.Data.Entity.Core.EntityCommandExecutionException: 'The data reader is incompatible with the specified 'ZuezCanalTunnelsModel.PaidReport'. A member of the type, 'VehicleTypedId', does not have a corresponding column in the data reader with the same name.'
Kiran Mohanty5y ago
can you please replace your store procedure code as below and try once ?
begin
SET @sql = 'select '+@ColumnsNames+' from TableName where ' +@Conditions
EXEC sp_executesql @sql
return
end
Ramy Atef5y ago
ok when i calling and run it if i passing one or more column in table error ocurre must pass all coulmns to run bu i wont pass dynamic columns name
Sachin Singh5y ago
yes then simply select the stored procedure while generating entities as you have already generated them then now
open edmx -->Right click and update model from database---> select stored procedures.
it will bring all SP to the context class and you won't need any Database.Query() method to execute them.
Ramy Atef5y ago
i using Console Application and Database First
Sachin Singh5y ago
At first if you are using database first workflow then all your stored procedures come into the context class as a normal c# method then you can simply call them using the context object as a normal method
second if the parameters are optional in SP then Entity Framework make method arguments optional so you don't need to provide all parameters.

please visit https://www.sharpencode.com/article/EntityFramework/database-first-approach/what-about-store-procedure-and-functions