Linq query FromSql where passing string value

Question

Have a SQL table example column Id-Pk-int, Name-varchar(250), ...

Using EF Core

when I execute

 var data= (from c in _db.Table.FromSql($"select * from Emp where id=1")
                     select c).FirstOrDefault();

returns successfully

but when I execute using where clause name (string)

 var data = (from c in _db.Table.FromSql($"select * from Emp where Name=Mike")
                     select c).FirstOrDefault();

I get an exception, System.Data.SqlClient.SqlException: 'Invalid column name 'Mike'.'

I tested if I changed the data in the database column: Name to 123 (number) it works but fails when value is string

Batur · Accepted Answer · 2021-04-20 23:07:52Z

1

I think the problem is occured because of you forgot to use ' symbol. Because exception says there is no column named Mike. And column name should be "Name" as i see from the code.
Check if Name column is VARCHAR. If the column is VARCHAR you need use ' symbols. If not you can use like WHERE ID=1

Use that;

   var data = (from c in _db.Table.FromSql($"select * from Emp where Name='Mike'")
                     select c).FirstOrDefault();

Edit 2: I see that you said Id-int, Name-varchar. Can you use the code i wrote? Thanks.

edited Apr 20, 2021 at 23:07

answered Apr 20, 2021 at 22:46

Batur

481 silver badge9 bronze badges

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

5 Comments

Ben Over a year ago

You absolutely correct! Good catch! Thanks for your help!

Batur Over a year ago

You are welcome. I am glad that i helped :)

Charlieface Over a year ago

Use parameters, don't concat/interpolate data into a query

juharr Over a year ago

@Charlieface That's not concating or interpolating, it's a hard coded value.

Charlieface Over a year ago

@juharr OP is clearly using interpolation in their real code, as indicated by $ and the fact that it doesn't make sense to hard-code a search value like that, even though in the question they have edited the value straight in

Charlieface · Accepted Answer · 2021-04-20 23:41:31Z

0

You should use proper parameterization, do not concatenate or interpolate data into a query, or you will leave yourself open to injection attacks and syntax errors.

var data = (from c in _db.Table.FromSql("select * from Emp where Name = @name",
                   new SqlParameter("@name", SqlDbType.VarChar, 50){Value = "Mike"})
                     select c).FirstOrDefault();


var data = (from c in _db.Table.FromSql("select * from Emp where Id = @id",
                   new SqlParameter("@id", SqlDbType.Int){Value = 1})
                     select c).FirstOrDefault();

In newer versions of EF Core, use FromSqlRaw in the same way.

answered Apr 20, 2021 at 23:41

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Linq query FromSql where passing string value

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related