Entity Framework Core raw SQL issue

Question

I have a History table with columns C0, C1, C2, C3, TimeStamp.

I want to select a specific column based on input along with corresponding TimeStamp.

Let channelId be 'C2'

var context = new DalModels.DbContext();
string command = "SELECT TimeStamp, @channelId FROM dbo.History";
var user = new SqlParameter("@channelId", channelId);
var result = context.History.FromSql(command, user).ToList();

But instead of a result, I get an exception:

No column name was specified for column 2 of 'h'.

Invalid column name 'C0'.

Invalid column name 'C1'.

Invalid column name 'C2'.

Invalid column name 'C3'.

Invalid column name 'TimeStamp'.

what is channelId?

SpiritBob
– SpiritBob

2019-08-30 14:36:51 +00:00
Commented Aug 30, 2019 at 14:36 — SpiritBob
– SpiritBob, Commented Aug 30, 2019 at 14:36
You need : string channelId = "C2";

jdweng
– jdweng

2019-08-30 14:39:14 +00:00
Commented Aug 30, 2019 at 14:39 — jdweng
– jdweng, Commented Aug 30, 2019 at 14:39
@SpiritBob channelID is a column name which can vary

amplifier
– amplifier

2019-08-31 12:08:51 +00:00
Commented Aug 31, 2019 at 12:08 — amplifier
– amplifier, Commented Aug 31, 2019 at 12:08

prinkpan · Accepted Answer · 2019-08-30 14:54:37Z

6

Parameterized query cannot be used for the dynamic column name. Parameters are to be used only on the right-hand-side of expression, e.g. after an =.

To solve your problem, convert the query to use a variable.

var context = new DalModels.DbContext();
string command = $"SELECT TimeStamp, {channelId} FROM dbo.History";
var result = context.History.FromSql(command, user).ToList();

Note that $ is used for string interpolation

You will then have to whitelist the values that users can pass to the field to prevent SQL injection attacks.

edited Aug 30, 2019 at 14:54

answered Aug 30, 2019 at 14:45

prinkpan

2,2661 gold badge23 silver badges39 bronze badges

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

2 Comments

Ivan Stoev Over a year ago

True, but that's just the (SQL) half of the story. FromSql requires selecting all the entity columns, so cannot be used in such scenario anyway.

prinkpan Over a year ago

@IvanStoev is correct, there is a limitation in FromSql. In this case, the approach would be then to fetch all the columns using context.History.ToList(); & then do some manipulation in the code.

Collectives™ on Stack Overflow

Entity Framework Core raw SQL issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related