SqlPersistence with SqlTransport and EF (Core)

[Tobias-08]

Hi Simon,

I appreciate your efforts, very promising project!

Does SQLPersistence support the following scenario?

-Persistence: SqlPersistence (SQL Server)
-Transport: SqlTransport
-Business data: Entity Framework Core (on .net 4.61 runtime)
-all data in one database; no DTC, no Outbox but local/native single TransactionScope

If yes:
-Do I have to/Should I use SqlPersistenceSession (https://docs.particular.net/nservicebus/sql-persistence/ ) to get the context for the business data or is it sufficient to use the same connection string? Could you provide a sample how to use SqlPersistenceSession with EF Core?
-Why is Outbox used in this sample https://docs.particular.net/samples/outbox/sqltransport-sqlpersistence-ef/ ? In my understanding Outbox is not necessary because all data is in one database.

Thanks!
Tobias

[SzymonPobiega]

Hi @Tobias-Gr

Yes, SQLPersistence should support this scenario. This is an integration tests that SQLPersistence works with SQL Transport by sharing the database connection and transaction.

Yes, if you want to use EF for your data access you need to use the persistence session as described here. It is not enough to have the same connection string because sql persistence/will hold the connection during the handler execution. Two concurrent connections within the same transaction scope cause DTC escalation.

[Tobias-08]

Hi @SzymonPobiega

thanks!

Why is Outbox used in this sample although the persistence session is used?

https://docs.particular.net/samples/outbox/sqltransport-sqlpersistence-ef/

[SzymonPobiega]

Let me give you some examples how the persistence session works in different cases. I guess I should then move this to the documentation...

Assumption: NServiceBus always requires a persistence to be configured so there is always some kind of synchronized storage session in the context.

SQL Server Transport in native tx mode + NHibernate persistence

Not possible because NHibernate won't accept an externally created DbTransaction

SQL Server Transport in TransactionScope mode + NHibernate persistence

Transport opens a TransactionScope which is present during entire message processing. The transport connection is closed as soon as message is received. The persistence opens its own connection. If both connections share same connection string, no DTC is involved. User data access code has to get the connection/session from the synchronized storage session in order to avoid DTC escalation

SQL Server Transport in native tx mode + Outbox + NHibernate persistence

Transport opens and holds its DbConnection and DbTransaction. The outbox opens a TransactionScope that suppresses all incoming transaction context and, inside that scope, opens its own connection/session. This session is used to ensure exactly once processing so all user data access code has to go through that session. Otherwise the outbox won't guarantee idempotence.

SQL Persistnce

SQL persistence works similarly to the NHibernate persistence but has the advantage of accepting an external DbTransaction so it can work in the native mode (first one described above). Other modes of operation work the same as with NHibernate persistence.

[Tobias-08]

Thanks, things begin to clear up.

SQL Server Transport in TransactionScope mode + NHibernate persistence

As TransactionScope is the default for SqlTransport and session is reused for user data via EF: Why is outbox used/necessary in these samples?
https://docs.particular.net/samples/outbox/sqltransport-nhpersistence-ef/
https://docs.particular.net/samples/outbox/sqltransport-sqlpersistence-ef/

SQL Server Transport in native tx mode + Outbox + NHibernate persistence

In assumption of all data (transport, persistence, business) sharing one database this scenario makes sense for me only if my user data access technology is not able to reuse the session. Am I correct?

Entity Framework Core
As Entity Framework Core does not support TransactionScope at this moment (dotnet/efcore#5595 ): Neither the TransactionScope mode nor the native mode + Outbox would work with EF Core. Am I correct? Or is reusing the session enough (https://docs.microsoft.com/en-us/ef/core/saving/transactions )?

SQL Persistnce

So SqlPersistence does not require TransactionScope in native mode? Would I be able to use EF Core with SqlPersistence in native mode?

[Tobias-08]

Small update regarding EF Core:

In the NHibernate-TransactionScope-scenario I get an AmbientTransactionWarning-Exception in EF Core.

If Outbox is enabled I don't get an exception in EF Core (but I don't know why).

[SzymonPobiega]

As TransactionScope is the default for SqlTransport and session is reused for user data via EF: Why is outbox used/necessary in these samples?

The recommended usage of SQL Server transport is either to:

• Store everything (all queues and all user/saga data) in a single catalog (database)
• Have all queues in a dedicated catalog (possibly on a dedicated instance) and data in a catalog-per-endpoint manner

Having a catalog/instance per endpoint (with both queues and user/saga data) is not a recommended design.

The first option is good only for very small solutions. The second one requires either an outbox or DTC. Of these two Outbox is more easy of configure and performs better, that's why we used it in the samples. Using TransactionScope would be simpler though.

Neither the TransactionScope mode nor the native mode + Outbox would work with EF Core. Am I correct? Or is reusing the session enough

Using an external DbTransaction obtained from NHibernate context should work (as described in "Using external DbTransactions (relational databases only)" section). For this to work you need to enabled the outbox. This will put the transport in native transaction mode and NHibernate will also use its native transactions. TL;DR; it is OK to get the DbTransaction from NHibernate. It is not OK (can't do it) to pass an existing instance of DbTransaction to NHibernate (without arcane magic).

If Outbox is enabled I don't get an exception in EF Core (but I don't know why).

When Outbox is enabled the SQL transport uses the native transaction. NHibernate probes for Transaction.Current but since it is not there, it creates it's session and own native transaction (which you can access via the context).

[SimonCropp]

wow. sorry i missed this one. somehow i was not subscribed to notifications.

@Tobias-Gr has @SzymonPobiega answered your questions? any other followups?

@SzymonPobiega is there any of the above we should add to doco?

[Tobias-08]

Hi,
sorry for the delay. I was out of office.
Szymon answered my questions, thank you.
In my opinion it would be valuable adding the above to the documentation.