Skip to content

Error in update with subquery for postgresql #352

@stsrki

Description

@stsrki

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

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions