sql server - Stored procedure with multiple parameters in EF Core 8 - Stack Overflow

I am trying to use a stored procedure with multiple parameters but I keep getting a 500 response from my API. The stored procedure returns a string result and takes 3 varchar(30) parameters.

Stored procedure call:

string Item1 = "123";
DateTime Item2 = DateTime.Now.AddDays(-3);
DateTime Item3 = DateTime.Now;

var result = await context.Set<string>().FromSqlInterpolated
            ($"EXECUTE proc @item1={Item1}, @item2={Item2.ToString()}, @item3={Item3.ToString()}")
            .FirstOrDefaultAsync();

Any help would be greatly appreciated, thank you!

I am trying to use a stored procedure with multiple parameters but I keep getting a 500 response from my API. The stored procedure returns a string result and takes 3 varchar(30) parameters.

Stored procedure call:

string Item1 = "123";
DateTime Item2 = DateTime.Now.AddDays(-3);
DateTime Item3 = DateTime.Now;

var result = await context.Set<string>().FromSqlInterpolated
            ($"EXECUTE proc @item1={Item1}, @item2={Item2.ToString()}, @item3={Item3.ToString()}")
            .FirstOrDefaultAsync();

Any help would be greatly appreciated, thank you!

• sql-server
• asp.net-core-mvc
• ef-core-8.0

Share Improve this question Follow edited Jan 2 at 18:15 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 2 at 17:25 Queen MochiQueen Mochi 5499 bronze badges 2

• 4 What's the actual error (not the 500 - an internal error has occurred)? Does the stored procedure work in your native sql client? – Andrew Commented Jan 2 at 17:42
• Gives me a "Cannot create a DbSet for 'string' because this type is not included in the model for the context." It does work in ssms, gives me a single value – Queen Mochi Commented Jan 2 at 17:51

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

For scalar results, you don't use the Set<> function as it's not an entity set. Just use context.Database.SqlQuery<>

var result = await context.Database.SqlQuery<string>()
            ($"EXECUTE proc @item1={Item1}, @item2={Item2.ToString()}, @item3={Item3.ToString()}")
            .FirstOrDefaultAsync();

Note that you should only do ToString if the parameter is a string, otherwise pass the value as the correct data type directly.

This would work just fine if you were passing in numeric values, but you are passing in strings within a SQL statement for your varchar arguments. Where are your quotation marks?

string Item1 = "123";
DateTime Item2 = DateTime.Now.AddDays(-3);
DateTime Item3 = DateTime.Now;

var result = await context.Set<string>().FromSqlInterpolated
            ($"EXECUTE proc @item1='{Item1}', @item2='{Item2.ToString()}', @item3='{Item3.ToString()}'")
            .FirstOrDefaultAsync();