Support for "MERGE INTO tablename USING dual ON ..." (UPSERT functionality) with Oracle.ManagedDataAccess.Core

[abrasat]

Is it possible to use the extension library to generate a sql query for oracle using Oracle.ManagedDataAccess.Core, that looks like in this example?

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT 
    VALUES (in_val1, in_val2, in_val3)

Can somebody please post a code snippet with the correct syntax? Is it possible to use the extension only for choosen ef core generated queries?

[viceroypenguin]

Try this out; it should work for you.

_context.TableName.ToLinqToDBTable()
  .Merge().Using(new[] { new { in_val1, in_val2, in_val3, }, })
  .On((dst, src) => dst.val3 == src.in_val3)
  .InsertWhenNotMatched(src => new() 
  { 
    Val1 = src.in_val1, 
    Val2 = src.in_val2,
    Val3 = src.in_val3,
  })
  .UpdateWhenMatched((dst, src) => new()
  {
    Val1 = src.in_val1, 
    Val2 = src.in_val2,
  })
  .Merge();

[abrasat]

This worked indeed, thank you very much! Is there also a async version possible? How can I see the generated sql query string?

[viceroypenguin]

Yes, MergeAsync as the final method will be an async method for you.

You can check _context.LastQuery to see the most recently completed statement executed by that db context.

[abrasat]

Thanks for the information. I could not find a LastQuery property in my DbContext

[viceroypenguin]

Sorry, I was thinking of the Linq2Db DataConnection. You'll probably need to add an interceptor that will let you log the query, or attach a profiler to the database.

[abrasat]

Ok, I will look for it, thx.