Passing Guid to stored procedure throws Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near '@Id'.'

user_1840421 0
Viewed 480

I have a stored procedure like this:

CREATE PROCEDURE [Owner].GetById
    @LanguageCode CHAR(5) = 'en-us', 
    @Id UNIQUEIDENTIFIER
AS
BEGIN
    SELECT ...
    WHERE LanguageCode = @LanguageCode
      AND Id = @Id;
END

And I am trying something like this to call the stored procedure using Entity Framework Core:

var param1 = new SqlParameter ("@LangaugeCode", "en-us");
var param2 = new SqlParameter ("@Id", System.Data.SqlDbType.UniqueIdentifier);
param2.Value = Guid.Parse("0ec0d169-0a4f-4458-a48b-729e1bb0fea9");

var context = new localContext(); // Auto-generated class for DB first approach
var manufacturers= context.Owner.FromSqlRaw($"EXEC Owner.GetById 
@LanguageCode, @Id", param1, param2).ToList();

It's throwing an error:

Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near '@Id'.'

Is it happening because of the Guid can not be passed as UNIQUEIDENTIFIER or is it a formatting issue?

entity-framework-core stored-procedures c#
3 Answers

I think you need one of :

var p1 = "en-us";
var p2 = Guid.Parse("0ec0d169-0a4f-4458-a48b-729e1bb0fea9");

var owners= context.Owner.FromSqlRaw(
  "Owner.GetById {0}, {1}",
  p1, 
  p2
}.ToList();

var owners= context.Owner.FromSqlInterpolated(
  $"Owner.GetById {p1}, {p2}").ToList();
user_14106640

Can you change the query from

FromSqlRaw($"EXEC Owner.GetById @LanguageCode @Id", param1, param2).ToList();

To:

FromSqlRaw($"EXEC Owner.GetById {0}, {1}", param1, param2).ToList();
user_4954550

you can do this. You do not need EXEC .

FromSqlRaw($"Owner.GetById {0}, {1}", p1, p2)
user_65270490
Related