Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error in update with subquery for postgresql #352

Open
stsrki opened this issue Mar 7, 2015 · 0 comments
Open

Error in update with subquery for postgresql #352

stsrki opened this issue Mar 7, 2015 · 0 comments

Comments

@stsrki
Copy link
Contributor

stsrki commented Mar 7, 2015

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant