linq2db update with CTE on MySql 8

210 Views Asked by At

This question is about linq2db.MySql 3.1.5 and MySQL 8.0.21

I am getting "You have an error in your SQL syntax..." when trying to run an update (marking duplicate emails) using CTE on MySql as per below

                var cte = (
                        from x in db.GetTable<PEmailCleanupJobItem>()
                        group x by x.EmailAddress into eGroup
                        where eGroup.Count() > 1
                        select new { EmailAddress = eGroup.Key }
                ).AsCte();

                var qry = (
                    from e in db.GetTable<PEmailCleanupJobItem>()
                    from d in cte
                        .InnerJoin(d => e.EmailAddress == d.EmailAddress)
                    select e
                );

                await qry
                    .Set(e => e.IsDuplicate, true)
                    .UpdateAsync();

This generates and sends the following to MySQL

--  MySql.Official MySql (asynchronously)
DECLARE @IsDuplicate UByte -- Boolean
SET     @IsDuplicate = 1

UPDATE `CTE_1` (`EmailAddress`)
AS
(
        SELECT
                `t1`.`email_address`
        FROM
                `emails` `t1`
        GROUP BY
                `t1`.`email_address`
        HAVING
                Count(*) > 1
)
FROM
        `emails` `d`
                INNER JOIN `CTE_1` `e` ON (`d`.`email_address` IS NULL AND `e`.`EmailAddress` IS NULL OR `d`.`email_address` = `e`.`EmailAddress`)
SET
        `d`.`is_duplicate` = @IsDuplicate

Note how UPDATE `CTE_1` (`EmailAddress`) is generated instead of WITH `CTE_1` (`EmailAddress`)

I would manually write this query (which works) as

WITH `CTE_1` (`EmailAddress`)
AS
(
        SELECT
                `t1`.`email_address`
        FROM
                `emails` `t1`
        GROUP BY
                `t1`.`email_address`
        HAVING
                Count(*) > 1
)
UPDATE 
    `emails` `d`
            INNER JOIN `CTE_1` `e` ON (`d`.`email_address` IS NULL AND `e`.`EmailAddress` IS NULL OR `d`.`email_address` = `e`.`EmailAddress`)
SET
    `d`.`is_duplicate` = 1

Is this a bug with linq2db or am I missing something?

0

There are 0 best solutions below