Possible Bug: Parameter Name '@' Convention

Possible Bug: Parameter Name '@' Convention

Postby ChrisMH » 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

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.
ChrisMH
 
Posts: 14
Joined: Tue 15 Mar 2011 18:11

Postby AndreyR » Fri 15 Jul 2011 14:12

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL