-
Notifications
You must be signed in to change notification settings - Fork 113
Open
Description
I have a problem with the following query. It works perfectly on SqlServer provider but on PostgreSql I'm getting an error "Table not found for 't10.Sum(t7.Amount)'." The error is raised in BasicSqlProvider.BuildExpression() method.
I tried to pin-point why the error is happening and I think it is somewhere in BasicSqlProvider.GetAlternativeUpdate() method.
This query is for your tests, it is very simmilar to my production query, just it's somewhat simplified.
( from td in db.FINDocuments.Where( x => documentsIDs.Contains( x.DocumentsID ) )
join td2 in
( from subtd in db.FINDocuments
join subtds in db.FINDocumentsFields on subtd.DocumentsID equals subtds.DocumentsFieldsID
group subtds by new { subtd.DocumentsID } into g
select new
{
g.Key.DocumentsID,
Amount = g.Sum( x => x.Amount )
} ) on td.DocumentsID equals td2.DocumentsID into tempTD2
from td2 in tempTD2.DefaultIfEmpty()
select new
{
Amount = (decimal?)td2.Amount
} )
.Update( db.FINDocuments, x => new Documents
{
Amount = x.Amount ?? 0m
} );
Here is the script to generate test schemas in postgre database:
CREATE SCHEMA "Financial"
AUTHORIZATION postgres;
CREATE TABLE "Financial"."Documents"
(
"DocumentsID" integer NOT NULL,
"Amount" numeric(18,2) NOT NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE "Financial"."Documents"
OWNER TO postgres;
CREATE TABLE "Financial"."DocumentsFields"
(
"DocumentsFieldsID" integer NOT NULL,
"DocumentsID" integer NOT NULL,
"Amount" numeric(18,2) NOT NULL
)
WITH (
OIDS=FALSE
);
ALTER TABLE "Financial"."DocumentsFields"
OWNER TO postgres;
--delete from "Financial"."Documents";
--delete from "Financial"."DocumentsFields";
insert into "Financial"."Documents"("DocumentsID","Amount") values (1,0);
insert into "Financial"."Documents"("DocumentsID","Amount") values (2,0);
insert into "Financial"."Documents"("DocumentsID","Amount") values (3,0);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (1,1,40);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (2,1,50);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (3,2,30);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (4,3,40);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (5,1,50);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (6,2,10);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (7,2,10);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (8,3,20);
insert into "Financial"."DocumentsFields"("DocumentsFieldsID","DocumentsID","Amount") values (9,3,30);
Test models:
[TableName( Name = "Documents", Owner = "Financial" )]
public class Documents
{
#region Members
[MapField( "DocumentsID" )]
public int DocumentsID { get; set; }
[MapField( "Amount" )]
public decimal Amount { get; set; }
#endregion
}
[TableName( Name = "DocumentsFields", Owner = "Financial" )]
public class DocumentsFields
{
#region Members
[MapField( "DocumentsFieldsID" )]
public int DocumentsFieldsID { get; set; }
[MapField( "DocumentsID" )]
public int DocumentsID { get; set; }
[MapField( "Amount" )]
public decimal Amount { get; set; }
#endregion
}
Note that QuoteIdentifiers in postgre provider must be set to true.
Metadata
Metadata
Assignees
Labels
No labels