Test Runner creates DB in Initializer And tries again on execution of first query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Test Runner creates DB in Initializer And tries again on execution of first query

Post by cResults » Fri 16 Nov 2012 21:43

We have an application that we want to be able to run off of an Oracle database using dotConnect. We are using EF 5.0 & dotConnect 7.2.114.

We are trying to run our integration tests to see if dotConnect can pass them. When Visual Studio Test runner starts, it calls the TestConfig below. An Oracle DB is created and seed data inserted successfully.

The Problem is that when the first test enumerates a query, EF tries to reseed the db and errors when it tries to insert a record that already exists. Each test creates a new EntityConfig based upon the connection string in App.config.

This behavior is not experienced when run on SQL Server.

Please advise.


Integration Test Initializer:

Code: Select all

[TestClass]
class TestSetUp
{
[AssemblyInitialize]
public static void TestConfig(TestContext testContext)
{
    AutoMapperConfiguration.Configure();

    Devart.Data.Oracle.OracleMonitor monitor = 
        new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    // You use the capability for configuring the behavior of the EF-provider:
    var config = OracleEntityProviderConfig.Instance;

    // Now, you switch off schema name generation while generating 
    // DDL scripts and DML:
    config.Workarounds.IgnoreSchemaName = true;
    config.Workarounds.ColumnTypeCasingConventionCompatibility = true;

    //Set DeleteDatabaseBehavior
    config.DatabaseScript.Schema.DeleteDatabaseBehaviour =   
           DeleteDatabaseBehaviour.AllSchemaObjects;
    //--------------------------------------------------------------

    DbConnection oracleConnection = 
       new Devart.Data.Oracle.OracleConnection { 
           UserId = "admin", 
           Password = "admin", 
           Server = "srv08oracle11", 
           Direct = true, 
           Sid = "xe" };

    oracleConnection.StateChange += new StateChangeEventHandler(Connection_StateChange);

    using (var context = new SumoContext(oracleConnection))
    {
        Database.SetInitializer<SumoContext>(new DatabaseInitializerTest(
                                                 new OracleClient(context)));

        // Make sure Code First has built the database schema before we open the connection
        context.Database.Initialize(force: false);
    }
}

// On connection opening, we change the current schema to "EFTEST2":
static void Connection_StateChange(object sender, StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
    {
        DbConnection connection = (DbConnection)sender;
        connection.ChangeDatabase("EFTEST2");
    }
}
DdContext:

Code: Select all

[InjectionConstructor]
public EntityContext()
	: base("Name=EntityContext")
{
}

public EntityContext(DbConnection connection)
    : base(connection, true)
{
}

public IDbSet<Catalog> EntityModel { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //Oracle Specific
    modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.ColumnTypeCasingConvention>();
    modelBuilder.Conventions.Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();


    //Common
    modelBuilder.Configurations.Add(new EntityModelMap());
We have tried both connection strings below in App.config:

Code: Select all

    <add name="EntityContext" connectionString="User Id=EFTest2;Password=EFTEST2;Server=srv08oracle11;Direct=True;Sid=xe" providerName="Devart.Data.Oracle" />
    <!--<add name="EntityContext" connectionString="User Id=admin;Password=admin;Server=srv08oracle11;Direct=True;Sid=xe" providerName="Devart.Data.Oracle" />-->


cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by cResults » Mon 19 Nov 2012 20:47

I've changed the Test Initializer to:

Code: Select all

[TestClass]
class TestSetUp
{
[AssemblyInitialize]
public static void TestConfig(TestContext testContext)
{
    Devart.Data.Oracle.OracleMonitor monitor = 
        new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    // You use the capability for configuring the behavior of the EF-provider:
    var config = OracleEntityProviderConfig.Instance;

    // Now, you switch off schema name generation while generating 
    // DDL scripts and DML:
    config.Workarounds.IgnoreSchemaName = true;
    config.Workarounds.ColumnTypeCasingConventionCompatibility = true;

    //Set DeleteDatabaseBehavior
    config.DatabaseScript.Schema.DeleteDatabaseBehaviour =   
           DeleteDatabaseBehaviour.AllSchemaObjects;
    //--------------------------------------------------------------

    using (var context = new SumoContext())
    {
        Database.SetInitializer<SumoContext>(new DatabaseInitializerTest(
                                                 new OracleClient(context)));

        // Make sure Code First has built the database schema before we open the connection
        context.Database.Initialize(force: false);
    }
}
So that it will always use the same connection and schema and the problem is the same. It drops the db and creates and seeds it. Then when one of the tests creates a new EntityContext with the connection string from app config, it still drops the db, recreates it and then for various reasons it crashes while seeding the data. ( say various, because it doesn't appear to happen in the same place twice, but it is always an integrity conflict) It shouldn't be running through the seeding process again.

I also stepped through the process confirming that it uses the same SID for each connection using:

Code: Select all

select * from v$session WHERE osuser = 'dsmit_000';
I've tried running the integration test process with "DropCreateDatabaseIfModelChanges" it throws:

Code: Select all

Model compatibility cannot be checked because the database does not contain model metadata. Model compatibility can only be checked for databases created using Code First or Code First Migrations.
Also as a sanity check, I changed the connection string back to (LocalDB)\v11.0 with Initializer set to DropCreateDatabaseAlways and all tests passed. I also stepped through my breakpoints and confirmed the Seeding process is only ever called once.

Any assistance on this will be greatly appreciated.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by Shalex » Wed 21 Nov 2012 13:52

Please create a separate console application and run the following code (it works in our environment) with dotConnect for Oracle v 7.2.114 and EF v 5.0:

Code: Select all

using Devart.Data.Oracle.Entity.Configuration;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
           Devart.Data.Oracle.OracleMonitor monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

            // You use the capability for configuring the behavior of the EF-provider:
            var config = OracleEntityProviderConfig.Instance;

            // Now, you switch off schema name generation while generating
            // DDL scripts and DML:
            config.Workarounds.IgnoreSchemaName = true;
            config.Workarounds.ColumnTypeCasingConventionCompatibility = true;

            //Set DeleteDatabaseBehavior
            config.DatabaseScript.Schema.DeleteDatabaseBehaviour = DeleteDatabaseBehaviour.AllSchemaObjects;
            //--------------------------------------------------------------

            using (var context = new EntityContext())
            {
                Database.SetInitializer<EntityContext>(new CreateDatabaseIfNotExists<EntityContext>());

                // Make sure Code First has built the database schema before we open the connection
                context.Database.Initialize(force: false);

                context.MyEntities.Add(new MyEntity { Data = "asfd" });
                context.SaveChanges();

                foreach (var e in context.MyEntities)
                {
                    Console.WriteLine(e.Id);
                }
                 Console.ReadKey();
            }
        }
    }
    public class EntityContext : DbContext
    {
        public EntityContext()
           : base("Name=EntityContext")
        {
        }

        public EntityContext(DbConnection connection)
            : base(connection, true)
        {
        }

        public IDbSet<MyEntity> MyEntities { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //Oracle Specific
            modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.ColumnTypeCasingConvention>();
            modelBuilder.Conventions.Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();
        }
    }

    public class MyEntity
    {
        public int Id { get; set; }
        public string Data { get; set; }
    }
}
How should we modify this code to reproduce the issue in our environment?

cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by cResults » Mon 26 Nov 2012 18:00

It appears to me that Devart's implementation of DropCreateDatabaseAlways executes for every new instance of a DdContext, while Microsoft Entity Framework's implementation executes for every application instance. The code below will Drop and Create the Database twice and it should only do it once.

Code: Select all

using Devart.Data.Oracle.Entity.Configuration;
using Softdocs.Sumo.Entity;
using System.Data.Entity;
using System;
using System.Data.Common;
using Softdocs.Sumo.Entity.Integration.Test;
using Softdocs.Sumo.Plugins.NativeDbClients;
using Softdocs.Sumo.Web;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            Devart.Data.Oracle.OracleMonitor monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };


            // You use the capability for configuring the behavior of the EF-provider:
            var config = OracleEntityProviderConfig.Instance;

            // Now, you switch off schema name generation while generating
            // DDL scripts and DML:
            config.Workarounds.IgnoreSchemaName = true;
            config.Workarounds.ColumnTypeCasingConventionCompatibility = true;

            //Set DeleteDatabaseBehavior
            config.DatabaseScript.Schema.DeleteDatabaseBehaviour = DeleteDatabaseBehaviour.AllSchemaObjects;
            //--------------------------------------------------------------

            using (var context = new EntityContext())
            {
                Database.SetInitializer<EntityContext>(new DatabaseInitializerTest());

                // Make sure Code First has built the database schema before we open the connection

                //THIS WILL CAUSE THE DB TO BE DROPPED AND CREATED AS I WOULD EXPECT IT TO.
                context.Database.Initialize(force: false);

            }

            using (var context = new EntityContext())
            {
                //THIS WILL CAUSES THE DB TO BE DROPPED AND CREATED AND IT SHOULD NOT.
                var foo = context.MyEntities.FirstOrDefault();
                
                foreach (var e in context.MyEntities)
                {
                    Console.WriteLine(e.Id);
                }
                 Console.ReadKey();
            }
        }
    }

    public class EntityContext : DbContext
    {
        public EntityContext()
           : base("Name=EntityContext")
        {
        }

        public EntityContext(DbConnection connection)
            : base(connection, true)
        {
        }

        public IDbSet<MyEntity> MyEntities { get; set; }
        public IDbSet<MyEntity2> MyEntities2 { get; set; }
        public IDbSet<MyEntity3> MyEntities3 { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //Oracle Specific
            modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.ColumnTypeCasingConvention>();
            modelBuilder.Conventions.Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();
        }
    }

    public class MyEntity
    {
        public int Id { get; set; }
        public string Data { get; set; }
    }

    public class MyEntity2
    {
        public int Id { get; set; }
        public string Data { get; set; }
    }

    public class MyEntity3
    {
        public int Id { get; set; }
        public string Data { get; set; }
    }

    //DropCreateDatabaseIfModelChanges
    //DropCreateDatabaseAlways
    public class DatabaseInitializerTest : DropCreateDatabaseAlways<EntityContext>
    {
        protected override void Seed(EntityContext entityContext)
        {
            new List<MyEntity>{
                new MyEntity{ Data = "Record 1"},
                new MyEntity{ Data = "Record 2"},
                new MyEntity{ Data = "Record 3"},
                new MyEntity{ Data = "Record 4"},
                new MyEntity{ Data = "Record 5"},
                new MyEntity{ Data = "Record 6"},
            }.ForEach(x => entityContext.MyEntities.Add(x));

            //SaveChanges 1
            entityContext.SaveChanges();

            new List<MyEntity2>{
                new MyEntity2{ Data = "Record 21"},
                new MyEntity2{ Data = "Record 22"},
                new MyEntity2{ Data = "Record 23"},
                new MyEntity2{ Data = "Record 24"},
                new MyEntity2{ Data = "Record 25"},
                new MyEntity2{ Data = "Record 26"},
            }.ForEach(x => entityContext.MyEntities2.Add(x));

            //SaveChanges 2
            entityContext.SaveChanges();

            new List<MyEntity3>{
                new MyEntity3{ Data = "Record 31"},
                new MyEntity3{ Data = "Record 32"},
                new MyEntity3{ Data = "Record 33"},
                new MyEntity3{ Data = "Record 34"},
                new MyEntity3{ Data = "Record 35"},
                new MyEntity3{ Data = "Record 36"},
            }.ForEach(x => entityContext.MyEntities3.Add(x));

            //SaveChanges 3
            entityContext.SaveChanges();
        }
    }
}
Primary Issue:
I need to know how to configure dotConnect so that it will drop and create an EF DbContext once per instance of the application. This is the behavior we expect based upon our current deployment of EF to Sql Server and it is required to run our integration tests.

Secondary Issue:
Since we use EF for extensive integration test, we have a large body of seed data that is entered via DatabaseInitializerTest.Seed (see above). If there is an error in a block of code that violate db integrity, for example adding MyEntities, I would expect Visual Studio to break on the SaveChanges() that is committing the change. This is based upon how EF behaves connnecting to SQL Server 2008 & 2012.

While using dotConnect for Oracle, I have had Visual Studio break on a specific context.SaveChanges() with an integrity violation that actually took place in a prior context.SaveChanges() call. To use the code above as an example, the code will break on the line following //SaveChanges 3, and if I continue through the Visual Studio errors, DbMonitor will eventually tell me that the error was in the insert of MyEntities, which took place on the line following //SaveChanges 1.

Based upon this observation, it appears to me that dotConnect for Oracle behaves asynchronously while EF5 to Sql Server behave synchronously.

I believe this behavior is associated with my primary issue above. After the Integration Test is initialized, it drops and creates the database and the DatabaseInitializer successfully enters the seed data. Then, as I've stated in prior post, when the first test enumerates a query it drops and creates the database and calls the Seed overload again, only this time it will throw a foreign key violation stating that the seed data being entered already exists.

My interpretation of this behavior is that whatever code was sent to Oracle to delete the database consistently doesn't complete prior to the second DbContext's DatabaseInitializer's attempt to reseed the data.

I'm grateful for the promptness and level of support I have received so far and I look forward to your solution.

Respectfully,

David C

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by Shalex » Thu 29 Nov 2012 16:33

cResults wrote:Primary Issue:
I need to know how to configure dotConnect so that it will drop and create an EF DbContext once per instance of the application. This is the behavior we expect based upon our current deployment of EF to Sql Server and it is required to run our integration tests.
We have reproduced the problem and are investigating it. We will post here about the results.
cResults wrote:If there is an error in a block of code that violate db integrity, for example adding MyEntities, I would expect Visual Studio to break on the SaveChanges() that is committing the change.
We cannot reproduce the issue. Please post here or send us a small test project.
cResults wrote:when the first test enumerates a query it drops and creates the database and calls the Seed overload again, only this time it will throw a foreign key violation stating that the seed data being entered already exists.
We cannot reproduce the issue. Please post here or send us a small test project.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by Shalex » Tue 04 Dec 2012 16:31

cResults wrote:Primary Issue:
I need to know how to configure dotConnect so that it will drop and create an EF DbContext once per instance of the application. This is the behavior we expect based upon our current deployment of EF to Sql Server and it is required to run our integration tests.
There are special "optimizations" for System.Data.SqlClient at the general level of Entity Framework. Some of them have side effects for third-party providers. This is exactly the case which caused the unexpected behaviour in the test code.

Possible workarounds:
1. (not recommended) Do an initialization twice. There would be no initializations third time and further.
2. (recommended) Add the "Persist Security Info=True" parameter to your connection string, and only one initialization would be done.

cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Test Runner creates DB in Initializer And tries again on execution of first query

Post by cResults » Wed 05 Dec 2012 17:43

:D
Primary Issue is resolved utilizing "Persist Security Info=True"

Tests are all running.

Thank you.
:D

Post Reply