Correct usage of linq2db along ef core

[neistow]

I'm trying to adopt linq2db in a project because it supports pessimistic locking. What is the right way to use linq2db along with regular EfCore?

Right now code like this throws an exception:

var book = await dbContext.Books
    .AsSqlServer()
    .WithUpdLockInScope()
    .WithRowLockInScope()
    .FirstOrDefaultAsync(); // or .FirstOrDefaultAsyncEF()

Unhandled exception. System.InvalidOperationException: The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'. Only providers that implement 'IAsyncQueryProvider' can be used for Entity Framework asynchronous operations.
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Only the LinqToDB extension method versions seem to be working.

Is calling .ToLinqToDBTable better? It seems to change method names for locking.

var book = await dbContext.Books
    .ToLinqToDBTable()
    .AsSqlServer()
    .WithUpdLock()
    .WithRowLock()
    .FirstOrDefaultAsyncLinqToDB();

[sdanyliv]

You have to use FirstOrDefaultAsyncLinqToDB(), otherwise EF calls it's own FirstOrDefaultAsync, which expects EF Core's query provider.

ToLinqToDBTable changes QueryProvider to linq2db, and you cannot use EF Core extensions after that.

[neistow]

ToLinqToDBTable changes QueryProvider to linq2db, and you cannot use EF Core extensions after that.

Does it mean that calling any .FirstOrDefaultAsyncLinqToDB(); or any LinqToDB method will set query provider to linq2db even if ToLinqToDBTable was not specified?

---

I noticed that this type of query yields an invalid sql. Only first With statement is added (whichever is first). While if .ToLinqToDBTable() is used everything works as expected.

var book = await dbContext.Books
    .AsSqlServer()
    .WithUpdLockInScope() // only WITH (UPDLOCK) will be present in generated query
    .WithRowLockInScope()
    .FirstOrDefaultAsyncLinqToDB();

Will it be best practice to always use ToLinqToDBTable before utilizing any lib-specific methods?

Last but not least, will the linq2db query provider affect EF core behavior and functionality: like entity change tracking, JSON columns support, owned/value types, optimistic concurrency support, etc? Are there any cases when switching to the linq2db query provider will break existing behavior?