Update fails when using results of a flatten groupjoin (lambda)

Question

I'm trying to update a whole table to modify some columns and store Id from another table in there.

The following code fails at the update statement and I can't figure out why.

db.gt_s_one
    .GroupJoin(db.access_mode,
        l => l.col3.Replace("auth.", "").ToUpper(),
        am => am.code.ToUpper(),
        (l, am) => new {
            l,
            am
        }
    )
    .SelectMany(
        x => x.am.DefaultIfEmpty(),
        (x1, y1) => new {
            gt = x1.l,
            theAM = y1.id
        }
    )
    .Update(db.GetTable<gt_s_one>(),
        s => new gt_s_one
        {
            col1 = s.gt.col1,
            col2 = s.gt.col2,
            col3 = s.gt.col3.Replace("auth.", ""),
            col4 = s.gt.col4,
            col5 = s.gt.col3 == "empty" ? "1" : "0",
            col6 = s.gt.col3 == "empty" ? "" : s.theAM.ToString()
        }
    );

I've isolated the first part (groupjoin and selectmany) in a var and executing it works great but when I execute the Update part I get:

LinqToDB.SqlQuery.SqlException
  HResult=0x80131500
  Message=Table '[public].[access_mode]' not found.
  Source=linq2db

In my SelectMany I've tried brining y1 as a whole instead of y1.id but that didn't make any difference.

Answer 1

Queries like that currently supported by linq2db only for SQL Server, Sybase and MySql. I see that you are running it against PostgreSQL. Support for PostgreSQL (and most of other databases) already implemented and will be released as part of linq2db version 3 release. Example of generated SQL for your query:

UPDATE
    gt_s_one
SET
    col1 = x.col1,
    col2 = x.col2,
    col3 = Replace(x.col3, 'auth.', ''),
    col4 = x.col4,
    col5 = CASE
        WHEN x.col3 = 'empty' THEN '1'
        ELSE '0'
    END,
    col6 = CASE
        WHEN x.col3 = 'empty' THEN ''
        ELSE Cast(am.id as VarChar(11))
    END
FROM
    gt_s_one x
        LEFT JOIN access_mode am ON Upper(Replace(x.col3, 'auth.', '')) = Upper(am.code)
WHERE
    gt_s_one.id = x.id