The queries are done using a DbContext's Database object.
When placing parameters in SQL strings, the convention I have seen has been to prefix the parameter name with '@' or ':'.
This convention does not work with '@'. The @ convention when used with the parameter '@p0' in the SQL implies the parameter name 'p0'.
The '@' convention is important because some of EF's queries use that convention when automatically generating SQL strings and parameters (shown below).
This query does not work, failing with the error: column "p0" does not exist
Code: Select all
var result = context.Database
.SqlQuery( "SELECT id FROM overview.asset_event_type WHERE name=@p0",
new PgSqlParameter( "p0", PgSqlType.VarChar ) { Value = assetEventTypeName } )
.SingleOrDefault();
These queries do work. Note that I've explicitly set the parameter name to '@p0'.
The ':p0' convention works as I would expect with ':p0' in the SQL and 'p0' as the parameter name.
Code: Select all
var result = context.Database
.SqlQuery( "SELECT id FROM overview.asset_event_type WHERE name=@p0",
new PgSqlParameter( "@p0", PgSqlType.VarChar ) { Value = assetEventTypeName } )
.SingleOrDefault();
var result = context.Database
.SqlQuery( "SELECT id FROM overview.asset_event_type WHERE name=:p0",
new PgSqlParameter( "p0", PgSqlType.VarChar ) { Value = assetEventTypeName } )
.SingleOrDefault();
As an example of why this is an issue, EF allows you to query using this format and will automatically generate appropriately typed parameters for you:
Code: Select all
var result = context.Database
.SqlQuery( "SELECT id FROM overview.asset_event_type WHERE name={0}",
assetEventTypeName } )
.SingleOrDefault();
appropriate PgSqlParameter parameter attached.