[EF.Core] How to create DbContext for existing DbConnection?

[azabluda]

The official documentation https://www.devart.com/dotconnect/oracl ... iderefcore only describes one overload of 'UseOracle' which takes a connection string and creates a new 'DbConnection' for 'DbContext'. In our application we are going to create and destroy instances of 'DbContext' between client-server calls, yet we need to keep certain connections alive during transactions which span multiple client-server requests. In this respect another overload of 'UseOracle' which takes an instance of 'DbConnection' looks promising. Unfortunately the documentation falls short there.

We only managed to get that far:

Code: Select all

class MyDbContext : DbContext
{
    private readonly DbConnection connection;

    public MyDbContext(DbConnection connection)
    {
        this.connection = connection;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Undocumented
        optionsBuilder.UseOracle(this.connection);
    }
}

Code: Select all

void Test()
{
    var dbConnection = new Devart.Data.Oracle.OracleConnection
    {
        ConnectionString = "User Id=user;Password=pass;Server=demo11g;Direct=True;Sid=testdb";
    };

    using (DbContext dbContext = new MyDbContext(dbConnection))
    {
        dbContext.Database.EnsureDeleted(); <<< InvalidCastException
        dbContext.Database.EnsureCreated();
    }
}

Code: Select all

System.InvalidCastException occurred
  HResult=-2147467262
  Message=Unable to cast object of type 'Devart.Data.Oracle.OracleConnection' to type 'Devart.Common.Entity.az'.
  Source=Devart.Data.Oracle.Entity.EFCore
  StackTrace:
       at Devart.Common.Entity.b1.l()
  InnerException: 

Please document the proper way of sharing a connection between multiple DbContexts.

Thank you!

[Shalex]

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you when it is fixed.

[azabluda]

Thank you! Meanwhile I have created a few more tests addressing various issues in the EF.Core's provider, not all of them are related to .UseOracle(DbConnection)

https://github.com/azabluda/dotConnectO ... nitTest.cs

Shall I create a separate topic for it?

[Shalex]

In this respect another overload of 'UseOracle' which takes an instance of 'DbConnection' looks promising. [...]

Code: Select all

  Message=Unable to cast object of type 'Devart.Data.Oracle.OracleConnection' to type 'Devart.Common.Entity.az'.

The bug is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

Meanwhile I have created a few more tests addressing various issues in the EF.Core's provider, not all of them are related to .UseOracle(DbConnection)

https://github.com/azabluda/dotConnectO ... nitTest.cs

We have reproduced the issues and are investigating them. We will notify you about the result as soon as possible.

[Shalex]

Meanwhile I have created a few more tests addressing various issues in the EF.Core's provider, not all of them are related to .UseOracle(DbConnection)

https://github.com/azabluda/dotConnectO ... nitTest.cs

Please upgrade to the newest (9.1.82) build of dotConnect for Oracle: 6 (from 7) tests pass successfully. We will notify you when the issue with the only failed test is fixed.

[azabluda]

Thank you! I tried version 9.1.82 but it still doesn't seem to associate my external DbConnection with DbContext. As a consequence it is impossible to use one DbTransaction object for different DbContexts. I added two more failing tests

Code: Select all

        [TestMethod]
        public void DbContext_With_DbConnection_GetDbConnection()
        {
            using (var dbConnection = new OracleConnection { ConnectionString = ConnectionString })
            {
                using (var dbContext = new TestDbContext(dbConnection))
                {
                    Assert.AreSame(dbConnection, dbContext.Database.GetDbConnection());
                }
            }
        }

        [TestMethod]
        public void DbContext_With_DbConnection_UseTransaction()
        {
            using (var dbConnection = new OracleConnection { ConnectionString = ConnectionString })
            {
                dbConnection.Open();
                using (var dbTransaction = dbConnection.BeginTransaction())
                {
                    using (var dbContext = new TestDbContext(dbConnection))
                    {
                        dbContext.Database.UseTransaction(dbTransaction);
                        // InvalidOperationException: The specified transaction is not associated with the current connection.
                        // Only transactions associated with the current connection may be used.
                    }
                }
            }
        }

[Shalex]

Thank you for your feedback. We will investigate the issues and notify you about the result.

[azabluda]

Also DbUpdateConcurrencyException problem is only fixed for synchronous API. The async counterpart still throws no exception

Code: Select all

        [TestMethod]
        [ExpectedException(typeof(DbUpdateConcurrencyException))]
        public void DbContext_DbUpdateConcurrencyException_Async()
        {
            using (var dbContext = new TestDbContext(ConnectionString))
            {
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();

                var user = new User { Name = "John" };
                dbContext.Add(user);
                dbContext.SaveChanges();

                using (var dbContext2 = new TestDbContext(ConnectionString))
                {
                    User user2 = dbContext2.Set<User>().Single(u => u.Name == "John");
                    user2.Name = "Oliver";
                    dbContext2.SaveChanges();
                }

                user.LongDescription = "hello";
                dbContext.SaveChangesAsync().Wait();
            }
        }

[Shalex]

Code: Select all

        [TestMethod]
        public void DbContext_With_DbConnection_GetDbConnection()
        {
            using (var dbConnection = new OracleConnection { ConnectionString = ConnectionString })
            {
                using (var dbContext = new TestDbContext(dbConnection))
                {
                    Assert.AreSame(dbConnection, dbContext.Database.GetDbConnection());
                }
            }
        }

We think that the result of this test doesn't identify any significant issue. Could you please describe some use case when the instance passed to the DbContext constructor must be the same as the one returned by dbContext.Database.GetDbConnection()?
Our implementation of dbContext.Database.GetDbConnection() returns a wrapper-descendant of DbConnection (instead of OracleConnection itself). But (!) the DatabaseFacade.GetOracleConnection() extension method, which returns OracleConnection, will be implemented in EF Core starting from the next public build of dotConnect for Oracle.

Code: Select all

        [TestMethod]
        public void DbContext_With_DbConnection_UseTransaction()
        {
            using (var dbConnection = new OracleConnection { ConnectionString = ConnectionString })
            {
                dbConnection.Open();
                using (var dbTransaction = dbConnection.BeginTransaction())
                {
                    using (var dbContext = new TestDbContext(dbConnection))
                    {
                        dbContext.Database.UseTransaction(dbTransaction);
                        // InvalidOperationException: The specified transaction is not associated with the current connection.
                        // Only transactions associated with the current connection may be used.
                    }
                }
            }
        }

The bug with using the DatabaseFacade.UseTransaction() method in EF Core is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

Code: Select all

        [TestMethod]
        [ExpectedException(typeof(DbUpdateConcurrencyException))]
        public void DbContext_DbUpdateConcurrencyException_Async()
        {
            using (var dbContext = new TestDbContext(ConnectionString))
            {
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();

                var user = new User { Name = "John" };
                dbContext.Add(user);
                dbContext.SaveChanges();

                using (var dbContext2 = new TestDbContext(ConnectionString))
                {
                    User user2 = dbContext2.Set<User>().Single(u => u.Name == "John");
                    user2.Name = "Oliver";
                    dbContext2.SaveChanges();
                }

                user.LongDescription = "hello";
                dbContext.SaveChangesAsync().Wait();
            }
        }

We have reproduced the issue and are investigating it. We will notify you about the result.

[Shalex]

Code: Select all

        [TestMethod]
        [ExpectedException(typeof(DbUpdateConcurrencyException))]
        public void DbContext_DbUpdateConcurrencyException_Async()
        {
            using (var dbContext = new TestDbContext(ConnectionString))
            {
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();

                var user = new User { Name = "John" };
                dbContext.Add(user);
                dbContext.SaveChanges();

                using (var dbContext2 = new TestDbContext(ConnectionString))
                {
                    User user2 = dbContext2.Set<User>().Single(u => u.Name == "John");
                    user2.Name = "Oliver";
                    dbContext2.SaveChanges();
                }

                user.LongDescription = "hello";
                dbContext.SaveChangesAsync().Wait();
            }
        }

We have reproduced the issue and are investigating it. We will notify you about the result.

The bug with not throwing concurrency check exception on invoking DbContext.SaveChangesAsync() is fixed, but the code should throw AggregateException instead of DbUpdateConcurrencyException. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New build of dotConnect for Oracle 9.1.97 is available for download now: viewtopic.php?f=1&t=34216.

[azabluda]

The bug with not throwing concurrency check exception on invoking DbContext.SaveChangesAsync() is fixed, but the code should throw AggregateException instead of DbUpdateConcurrencyException.

Agree. I corrected the test in my github repo.

DatabaseFacade.GetOracleConnection() extension method, which returns OracleConnection, will be implemented in EF Core starting from the next public build of dotConnect for Oracle.

This one is fine as well for our needs.

The bug with using the DatabaseFacade.UseTransaction() method in EF Core is fixed.

Confirmed. Thank you!

Still waiting for the last outstanding problem DbContext_Include_OrderBy_First.

[Shalex]

Still waiting for the last outstanding problem DbContext_Include_OrderBy_First.

The bug with paging in EF Core, when working with Oracle 11g and below, is fixed in the newest (9.1.111) build of dotConnect for Oracle.

[azabluda]

Good job, thank you!

[azabluda]

In this respect another overload of 'UseOracle' which takes an instance of 'DbConnection' looks promising. [...]

Code: Select all

  Message=Unable to cast object of type 'Devart.Data.Oracle.OracleConnection' to type 'Devart.Common.Entity.az'.

The bug is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

I'm afraid the original problem has been reintroduced in dotConnect for Oracle 9.5.381. Tested with EFCore 2.0.0. Still the same InvalidCastException exception.

Code: Select all

System.InvalidCastException : Unable to cast object of type 'Devart.Data.Oracle.Entity.an' to type 'Devart.Data.Oracle.OracleConnection'.

at Devart.Data.Oracle.Entity.av.b(RelationalOptionsExtension A_0)
   at Devart.Common.Entity.cp.a()
   at System.Lazy`1.CreateValue()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Lazy`1.get_Value()
   at Devart.Common.Entity.cp.b()
   at Devart.Data.Oracle.Entity.ap..ctor(RelationalTypeMapperDependencies A_0, cq A_1)