3

I am trying to pass a string parameter to a SQL Query , Receiving error below. How can I resolve this? Currently utilizing answer EF Core 2.2, Passing String Parameter to FromSql Statement

Input String was not in a correct format. 

public async Task<IEnumerable<Product>> GetProduct(string productKey) { var productParameter = new SqlParameter("@productKey", SqlDbType.VarChar); productParameter.Value = productKey; var productIdList = db.TestDb .FromSql($"select ProductId from dbo.Product product" + " (where product.ProductKey = {productParameter})" ) .Select(c => c.ProductId).ToList();

It is type varchar(6) from ProductKey

Using Net Core 2.2

Improve this question
7
  • You need to identify what columns to select., and use the keyword FROM before the table name. SELECT something FROM table WHERE stuff Commented Jun 18, 2020 at 21:35
  • Do you mean to assign productKey as the parameter value? you have productNumber, and I don't see where that's defined. Commented Jun 18, 2020 at 21:39
  • What is the data type of ProductKey in your database? Commented Jun 18, 2020 at 21:42
  • The accepted answer to the question you linked to is wrong, so your code is wrong too. Commented Jun 18, 2020 at 21:45
  • Side note: You don't need the .Select(c => c.ProductId) since you specify the column name in the query and it's the only column you're returning. Perhaps '.ToList()' ? Commented Jun 18, 2020 at 21:46

3 Answers 3

Reset to default
5

If you are using FromSql you should construct your code like this to properly apply the SqlParameter:

var productIdList = db.TestDb .FromSql($"select ProductId from dbo.Product product where product.ProductKey = @productKey",productParameter ) .Select(c => c.ProductId).ToList();

Depending on the version of EF you are using, you could also use FromSqlInterpolated instead of FromSql and do away with the SqlParameter altoghter.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

1) Query is not correct: you don't have FROM statement, which is required. I presume that you wanted something like

select productKey from dbo.Product product where product.Product = <paramName>

2) When using FromSQL: in the query string you have to input the name of the parameter, not the instance. So change {productParameter} to @productKey

3) Pass the SqlParameter instance as second argument to FromSql method.

var productParameter = new SqlParameter("@productKey", SqlDbType.VarChar); productParameter.Value = productNumber; var product= db.Tra .FromSql($@" select productKey from dbo.Product product where product.ProductKey = {productParameter.Name}", productParameter);
Improve this answer

5 Comments

Your code does not match your explanation. You are not using @productKey in the sql.
You can use @productKey instead of {productParameter.Name}. In fact productParameter.Name is @productKey. You can check it during debug session. I just used here productParameter.Name to avoid duplication in the code.
I know you can use it but what is the point of the sql parameter if you are just going to use interpolation to build sql?
If I understood your question correctly: 1) SqlParameter prevents SQL injections 2) SqlParameter prevents SQL Server to build execution plans again and again when you make a query with different product id in where clause.
You didn't understand. I'm not arguing that you shouldn't use parameters. I'm saying your code is not technically using one. By using {productParameter.Name} you are using string interpolation, you are not using the SqlParameter that is passed to the Command. It should be: where product.ProductKey = @productKey". And you can drop the $ on the string.
1

This might do the trick:

var productParameter = new SqlParameter("@productKey", productKey); var productIdList = db.TestDb .FromSql("select ProductId from dbo.Product where ProductKey = @productParameter",productParameter ) .ToList();
Improve this answer

1 Comment

You forgot to add the parameter to the command. Just putting "@productKey" in the string won't work.