SCM

[#1010673] syntax error in queries generated by entity framework 2.0.6 ms.net sp1

View Trackers | Bugs | Download .csv | Monitor

Date:
2009-07-13 11:50
Priority:
3
State:
Open
Submitted by:
Andreas Winkler (magicmanone)
Assigned to:
Nobody (None)
Npgsql Version:
None
Category:
Group:
Resolution:
None
Summary:
syntax error in queries generated by entity framework 2.0.6 ms.net sp1

Detailed description
Hallo,

I have this in my program code

var zList = context.SalesOrderHeader.Include("Customer")
.Where(em => em.SubTotal >= 0
&& em.Customer.Active == true
&& em.Customer.NewCustomer == false
&& em.Customer.ExternalNumber == adm.ExternalNumber
)
.Select(em => new { em.SubTotal });

obj.Summe = z.Sum(ep => (decimal?)ep.SubTotal) ?? 0.00m;

The table SalesOrderHeader references the table Customer by the key OrderID.

If I execute this query I'll get the following error in the function NpgsqlDataReader ExecuteReader(CommandBehavior cb)
in NpgsqlCommand.cs:

"ERROR: 42601: syntax error at or near \"INNER\""

Debugging shows me this TSQL

"SELECT \"Project1\".\"C1\" AS \"C1\" FROM (SELECT TRUE) AS \"SingleRowTable1\" LEFT OUTER JOIN (SELECT \"GroupBy1\".\"A1\" AS \"C1\" FROM (SELECT CAST (Sum(\"Extent1\".\"SubTotal\") AS numeric) AS \"A1\" FROM \"buch\".\"SalesOrderHeader\" AS \"Extent1\" INNER JOIN \"buch\".\"Customer\" AS \"Extent2\" ON (\"Extent1\".\"OrderID\"=\"Extent2\".\"OrderID\") AND (((\"Extent1\".\"SubTotal\">=cast(0 as numeric)) AND (TRUE=\"Extent2\".\"Active\")) AND (FALSE=\"Extent2\".\"NewCustomer\")) WHERE \"Extent1\".\"OrderID\"=\"Extent2\".\"OrderID\" INNER JOIN \"buch\".\"Customer\" AS \"Extent3\" ON \"Extent1\".\"OrderID\"=\"Extent3\".\"OrderID\" WHERE \"Extent3\".\"ExternalNumber\"= (((286)))) AS \"GroupBy1\") AS \"Project1\" ON TRUE=TRUE"


If I remove one of the boolean parts in the query zList, the result is as expected.

Any hints?

Thanks,
Andreas


Followup

Message
Date: 2010-01-10 02:31
Sender: Josh Cooley

Some joins are now broken due to a fix for another bug. The wrong results were being returned, so I changed the code to return correct results in some circumstances. But I couldn't generate valid SQL every time. If you want to look through the code it's in SqlBaseGenerator and how variable substitution works. I need to rewrite the variable substitution.
Date: 2010-01-10 01:52
Sender: Andreas Winkler

Hi Josh,

I have entensively tested your new version 2.0.8. I have to
correct my previously message. The join-queries are not
working properly. The order of some argument in the query
fixes this mostly but there is no generally solution to
this problem. May be you could explain to me the way you
generates this kind of queries (+ the position in the
source files) because I would really like to help you to
fix this.

Thanks

Andreas
Date: 2010-01-06 14:33
Sender: Andreas Winkler

Hi Josh,

thanks for your great work.
So far as I have tested it seems to work properly.
If I have any problems I will tell you.

Thanks,
Andreas
Date: 2009-12-21 03:21
Sender: Josh Cooley

I was able to chase this issue down and put a fix for this into CVS. If you can test this, please let me know how it works for you.
Date: 2009-09-11 13:09
Sender: Andreas Winkler

Hi,

this is a part of my database structur:

CREATE TABLE buch."Customer"
(
"OrderID" integer NOT NULL DEFAULT nextval
('buch."Customer_OrderID_seq"'::regclass),
"NameStyle" integer NOT NULL DEFAULT 0,
"FirstName" character varying(50),
"LastName" character varying(50),
"Active" boolean NOT NULL DEFAULT true,
"ModifiedDate" timestamp without time zone NOT NULL
DEFAULT now(),
"TourNumber" integer,
"ExternalNumber" integer,
"MainPhone1" character varying(25),
"MainPhone2" character varying(25),
"PreOrderID" character varying(15),
"LastVisit" date,
"Created" timestamp without time zone NOT NULL DEFAULT
('now'::text)::date,
"ExternalName" character varying(50),
"NewCustomer" boolean NOT NULL DEFAULT true,
"Potential" boolean NOT NULL DEFAULT false,
"CustomerID" integer,
"OldExternalNumber" integer,
"OldTourNumber" integer,
"NewAssigned" boolean NOT NULL DEFAULT false,
"OldActive" boolean NOT NULL DEFAULT false,
"StartDate" date,
CONSTRAINT "PK_OrderID" PRIMARY KEY ("OrderID"),
CONSTRAINT "Customer_ExternalNumber_fkey" FOREIGN KEY
("ExternalNumber")
REFERENCES buch."External" ("ExternalNumber") MATCH
SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE buch."Customer" OWNER TO postgres;

-- Index: buch."idx_OldExternalNumber"

-- DROP INDEX buch."idx_OldExternalNumber";

CREATE INDEX "idx_OldExternalNumber"
ON buch."Customer"
USING btree
("OldExternalNumber" NULLS FIRST);


CREATE TABLE buch."SalesOrderHeader"
(
"OrderDate" timestamp(3) without time zone,
"Status" integer NOT NULL DEFAULT 0,
"BillToAddressID" integer,
"SubTotal" numeric(19,2) NOT NULL DEFAULT 0.00,
"TotalDue" numeric(19,2) NOT NULL DEFAULT 0.00,
"Comment" text,
"ModifiedDate" timestamp(3) without time zone,
"PaymentVersion" integer,
"BillID" character varying(15),
"BookCounter" integer NOT NULL DEFAULT 0,
"SendID" integer,
"SubSubTotal" numeric(19,2) NOT NULL DEFAULT 0.00,
"PriceRabatt15" boolean NOT NULL DEFAULT false,
"Total" numeric(19,2) NOT NULL DEFAULT 0.00,
"Discount" numeric(19,2) NOT NULL DEFAULT 0.00,
"Rabatt" numeric(19,2) NOT NULL DEFAULT 0.00,
"OrderID" integer NOT NULL DEFAULT 0,
"SendDate" timestamp(3) without time zone,
"ID" integer NOT NULL DEFAULT nextval
('buch."SalesOrderHeader1_ID_seq"'::regclass),
"Saison" character(9) NOT NULL DEFAULT '2010-
2011'::bpchar,
CONSTRAINT "SalesOrderHeader_pkey" PRIMARY KEY ("ID"),
CONSTRAINT "SalesOrderHeader_OrderID_fkey" FOREIGN KEY
("OrderID")
REFERENCES buch."Customer" ("OrderID") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE buch."SalesOrderHeader" OWNER TO postgres;

-- Index: buch."IDX_Header_Saison"

-- DROP INDEX buch."IDX_Header_Saison";

CREATE INDEX "IDX_Header_Saison"
ON buch."SalesOrderHeader"
USING btree
("Saison");

CREATE TABLE buch."Customer"
(
"OrderID" integer NOT NULL DEFAULT nextval
('buch."Customer_OrderID_seq"'::regclass),
"NameStyle" integer NOT NULL DEFAULT 0,
"FirstName" character varying(50),
"LastName" character varying(50),
"Active" boolean NOT NULL DEFAULT true,
"ModifiedDate" timestamp without time zone NOT NULL
DEFAULT now(),
"TourNumber" integer,
"ExternalNumber" integer,
"MainPhone1" character varying(25),
"MainPhone2" character varying(25),
"PreOrderID" character varying(15),
"LastVisit" date,
"Created" timestamp without time zone NOT NULL DEFAULT
('now'::text)::date,
"ExternalName" character varying(50),
"NewCustomer" boolean NOT NULL DEFAULT true,
"Potential" boolean NOT NULL DEFAULT false,
"CustomerID" integer,
"OldExternalNumber" integer,
"OldTourNumber" integer,
"NewAssigned" boolean NOT NULL DEFAULT false,
"OldActive" boolean NOT NULL DEFAULT false,
"StartDate" date,
CONSTRAINT "PK_OrderID" PRIMARY KEY ("OrderID"),
CONSTRAINT "Customer_ExternalNumber_fkey" FOREIGN KEY
("ExternalNumber")
REFERENCES buch."External" ("ExternalNumber") MATCH
SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE buch."Customer" OWNER TO postgres;

-- Index: buch."idx_OldExternalNumber"

-- DROP INDEX buch."idx_OldExternalNumber";

CREATE INDEX "idx_OldExternalNumber"
ON buch."Customer"
USING btree
("OldExternalNumber" NULLS FIRST);



and this is my query:

var zList = context.SalesOrderHeader.Include("Customer")
.Where(em => em.SubTotal >= 0
&& em.Customer.Active == true
&& em.Customer.NewCustomer == false
&& em.Customer.ExternalNumber ==
adm.ExternalNumber
)
.Select(em => new { em.SubTotal });

obj.Summe = z.Sum(ep => (decimal?)
ep.SubTotal) ?? 0.00m;



I hope this can help you to reproduce my problem. It seems
that your implementation of INNER JOIN, LEFT JOIN etc.
cannot handle this kind of queries.

Best regards

Andreas
Date: 2009-07-18 18:10
Sender: Josh Cooley

I've created a very similar linq query, but the SQL generated is missing the "WHERE Extent1.OrderID=Extent2.OrderID" part.

var qry = ctx.store.Include("address")
.Where(s => s.store_id > 0 &&
s.staff1.active == true &&
s.staff1.first_name == "Mike")
.Select(s => new { s.store_id });
var sum = qry.Sum(si => si.store_id);

Have you altered any of the metadata files that are generated from edmgen? If not, could you create a self contained sample that reproduces this?

Incidentally, here's the SQL I get for the above linq.

SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT TRUE) AS "SingleRowTable1" LEFT OUTER JOIN (SELECT CAST (Sum("Extent1"."store_id") AS int4) AS "A1" FROM "public"."store" AS "Extent1" INNER JOIN "public"."staff" AS "Extent2" ON ("Extent1"."manager_staff_id"="Extent2"."staff_id") AND (("Extent1"."store_id">0) AND (TRUE="Extent2"."active")) INNER JOIN "public"."staff" AS "Extent3" ON "Extent1"."manager_staff_id"="Extent3"."staff_id" WHERE 'Mike'="Extent3"."first_name") AS "GroupBy1" ON TRUE=TRUE
Date: 2009-07-18 09:14
Sender: Andreas Winkler

this query works :

var z = context.SalesOrderHeader.Include("Customer")
.Where(em => em.SubTotal >=
0
&& em.Customer.ExternalNumber == adm.ExternalNumber
&& em.Customer.NewAssigned
&& em.Customer.Active
)
.Select(em => new {em.SubTotal});
Date: 2009-07-17 03:35
Sender: Josh Cooley

Thanks for reporting this. I'll look to see if I can reproduce it with a similar linq query. Can you post the linq query and resulting sql for one that works?
Date: 2009-07-13 13:13
Sender: Andreas Winkler

Hi,

it seems to be that the order of the parameter plays a role.
If you order the boolean expressions to the end of the
query, the generator produces a correct TSQL translation.

Thanks,

Andreas

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge