CODEWITHSUNDEEP
c#entity-framework-core
scotty3
scotty3

Reputation: 153

EF Core FromSqlRaw with column variable

Attempting to set the column as a parameter in FromRawSql. Query always returns null, I understand this is because the SQL is compiled before parameter values are set. How can I pass the column name into the query as a parameter?

What I'm trying:

var param = new SqlParameter[] {
                        new SqlParameter("@col", dataDiscriminator),
                        new SqlParameter("@value", itemDiscValue)
                    };

var thisq = context.Devices.FromSqlRaw("SELECT * FROM Devices WHERE @col = @value", param);                        
var thisDevice = thisq.SingleOrDefault();

Will produce the SQL:

DECLARE @col nvarchar(4) = N'Name';
DECLARE @value nvarchar(26) = N'Registration Template';

SELECT * FROM Devices WHERE @prop = @value

Upvotes: 0

Views: 1840

Answers (1)

Serge
Serge

Reputation: 43890

you can not use parameter for table or column name. Try this

var param = new SqlParameter[] {
                        new SqlParameter("@value", itemDiscValue)
                    };
var thisq = context.Devices.FromSqlRaw($"SELECT * from Devices 
WHERE [{dataDiscriminator}] = @value", param).ToList();                        
 var thisDevice = thisq.SingleOrDefault();

I don' t know where dataDiscriminator data from but always remember about the sql script injections.

Upvotes: 1

Related Questions