Commit in IServerService.Execute or IServerService.Execute without transactions?

[Kobus-Smit]

I want to create some aggregate roots and then call Postgres function to process them, but the Postgres function does not see them because Execute function is still InTransaction

I've tried wrapping it in a serviceProvider.DoWork and calling uow.Commit but that did not work because it is still in the other transaction.

How do I commit the transaction from within the Execute function or is there a way to execute a IServerService without a transaction?

public class ImportThings: IServerService<(session session, List<thing> things), List<thing>> {
  protected readonly IDataContext db;
  protected readonly IDatabaseQuery qry;

  public ImportThings(IDataContext db, IDatabaseQuery qry) { 
    this.db = db;
    this.qry = qry;
  }
  public List<thing> Execute((session session, List<thing> things) arg) {
    // session contains some defaults set by the user
    arg.session.Create(db);

    foreach (var thing in arg.things)
      thing.session_id = arg.session.id;

    db.Create(arg.things);

    // The import_things Postgres function does not see the newly created session and things because the thingsaction is not yet committed
    qry.Execute($"select import_things('{arg.session.id}')");  

    return db.Query<thing>().Where(x => x.session_id == arg.session.id).ToList();
  }
}

[zapov]

I would expect IDatabaseQuery to share the same transaction so it should see stuff. If its really a different transaction thats a bug to fix.

But if you've used UnitOfWork and committed stuff, the other connection should certainly see it since it's not running in serializable mode.

So my first assumption would be that your function doesn't work correctly :)

As far as the Execute is concerned, ideally there could be some alternative APIs which could control more arguments and in the end specify kind of connection it uses.

So try with some simpler SQL to verify that you can see the data, either with this code, or certainly with UOW.

[Kobus-Smit]

Yep I had a typo in the qry.Execute :)