Possible Bug: Parameter Name '@' Convention

[ChrisMH]

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

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();

The above will generate command SQL "SELECT id FROM overview.asset_event_type WHERE name=@p0" with the
appropriate PgSqlParameter parameter attached.

[AndreyR]

Thank you for the report, however, we are already aware of this problem.
The reason for the issue is the fact that PostgreSQL server does not provide built-in support for the "@" placeholder. We are investigating the possibility to add the support for this placeholder.