Bad sql (single quoted) generated using EF with dynamic linq

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
atamgp
Posts: 9
Joined: Thu 30 Dec 2010 21:59

Bad sql (single quoted) generated using EF with dynamic linq

Post by atamgp » Thu 30 Dec 2010 22:14

SELECT count(*)
FROM dbo.someTable
WHERE 'parameter ' = '3'

This is not valid sql for postgre, but executes without an error.
However, when i put the parameter in double quotes, i get the needed results:
"parameter" = '3'

The query is generated using EF code only.

DbModel.Set().AsQueryable().Where(" @0 == @1 ", paramName = "parameter ", paramValueAsString = "3").Count();

The where is an Extension method from:
http://weblogs.asp.net/scottgu/archive/ ... brary.aspx

I'm using Devart provider 5.00.28 icw postgresql 9.0.

Withing the dynamic where a CreateQuery is done by scottgu.
Here is some debug information:

source.Expression.DebugView:
.Call .Constant(System.Data.Objects.ObjectQuery`1[FundaDomain.Domain.WoningObject]).MergeAs(.Constant(AppendOnly))


lambda.Body.DebugView:
"aantalkamers" == "3"

But still the parameter (here: aantalkamers) in the generated sql is put in single quotes.

If one needs more information please let me know.
Kind regards.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 04 Jan 2011 14:04

Apparently, string parameters of the Where method are interpreted as literals, hence they are single-quoted in the generated SQL. Please try specifying the column name explicitly in the predicate instead of substituting it to the parameter collection; i.e., use a query like

Code: Select all

MyEntities.EntitySet.Where(" parameter == @1 ", "3")
Please tell us if this helps.

atamgp
Posts: 9
Joined: Thu 30 Dec 2010 21:59

Post by atamgp » Tue 04 Jan 2011 14:49

Hi, thank you for your post.

Indeed that is the problem. I already solved it by what you are suggesting.
When you pass the parameterName as a parameter it is indeed transformed as a literal.

The "3" should be passed as a just 3 (int instead of string), and it should be @0 instead of @1.

Regards,

Atam

Post Reply