Possible Bug: Parameter Name '@' Convention
Posted: Thu 14 Jul 2011 21:09
I am using Entity Framework 4.1 code first and dotConnect 5.30.185.
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
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.
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:
The above will generate command SQL "SELECT id FROM overview.asset_event_type WHERE name=@p0" with the
appropriate PgSqlParameter parameter attached.
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.