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

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

Postby azabluda » Thu 28 Jul 2016 14:53

The official documentation https://www.devart.com/dotconnect/oracle/docs/EF.html#dataproviderefcore 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!
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

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

Postby Shalex » Mon 01 Aug 2016 08:55

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you when it is fixed.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby azabluda » Mon 01 Aug 2016 09:33

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/dotConnectOracle/blob/3718dbe0cade01cc146601fcd94de54a8b4e45df/Tests/DevArt.Tests.dotConnect/UnitTest.cs

Shall I create a separate topic for it?
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

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

Postby Shalex » Wed 03 Aug 2016 16:06

azabluda wrote: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.

azabluda wrote: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/dotConnectOracle/blob/3718dbe0cade01cc146601fcd94de54a8b4e45df/Tests/DevArt.Tests.dotConnect/UnitTest.cs
We have reproduced the issues and are investigating them. We will notify you about the result as soon as possible.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby Shalex » Thu 11 Aug 2016 15:10

azabluda wrote: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/dotConnectOracle/blob/3718dbe0cade01cc146601fcd94de54a8b4e45df/Tests/DevArt.Tests.dotConnect/UnitTest.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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby azabluda » Sun 14 Aug 2016 15:02

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.
                    }
                }
            }
        }
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

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

Postby Shalex » Mon 15 Aug 2016 11:55

Thank you for your feedback. We will investigate the issues and notify you about the result.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby azabluda » Thu 18 Aug 2016 11:32

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();
            }
        }
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

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

Postby Shalex » Tue 23 Aug 2016 14:41

azabluda wrote:
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.

azabluda wrote:
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.

azabluda wrote:
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
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby Shalex » Wed 31 Aug 2016 12:26

Shalex wrote:
azabluda wrote:
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
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby Shalex » Fri 02 Sep 2016 14:20

New build of dotConnect for Oracle 9.1.97 is available for download now: http://forums.devart.com/viewtopic.php?f=1&t=34216.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby azabluda » Mon 05 Sep 2016 08:24

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.
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45

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

Postby Shalex » Thu 22 Sep 2016 15:16

azabluda wrote: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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby azabluda » Fri 23 Sep 2016 12:05

Good job, thank you!
azabluda
 
Posts: 29
Joined: Thu 10 Sep 2009 14:45


Return to dotConnect for Oracle