LinqToDB4iSeries / Linq2DB4iSeries

LinqToDB provider for the DB2 database on IBM iSeries
MIT License
18 stars 5 forks source link

Select Into/Insert with Identity with OVERRIDING SYSTEM VALUE #80

Open tb-mtg opened 2 months ago

tb-mtg commented 2 months ago

When I execute the following select into insert:

var q = from p35 in db.Product.IsCompanyCode("S1").IsItemCode("74249")
        from x35 in db.ProductExtension.Where(x=> x.Code == p35.Code).DefaultIfEmpty()
        where x35.Code == null
        select new { p35.Code };

var records = q.Into(db.ProductExtension)
       .Value(x=> x.Id, x=> int.Parse(x.Code))
       .Value(x=> x.Code, x=> x.Code)        
       .Insert();

It generates the SQL below:

INSERT INTO Product(ID, Code)
SELECT Int(p.Code), p.Code
FROM Product
LEFT JOIN ProductExtension x ON x.Code = p.Code
WHERE x.Code IS NULL 

The following exception is thrown

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0798 - Value cannot be specified for GENERATED ALWAYS column ID.

Using OVERRIDING SYSTEM VALUE is supported to override the generated identity value with a custom one.

I originally raised this issue here and it was suggested that:

This is actually provider-specific issue. We have mechanisms to define query hints. iSeries provider just need to implement specific table hint API and add sql generation for it.

Is there any way to add OVERRIDING SYSTEM VALUE to the insert generated SQL statement, with table hints as suggested above?