Configure DmlOptions.BatchUpdates per context instance

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Wed 20 Dec 2017 14:45

Hello

My question is: Can I configure OracleEntityProviderConfig.Instance.DmlOptions.BatchUpdates.Enabled only in the scope of a single query / DbContext instance?

I am using EF6 with dotConnect for Oracle. I will elaborate my scenario a bit in order to provide some context:

I have some larger inserts and update operations in my application, that affect a couple of thousand db entries. For those I see a big performance improvement when using OracleEntityProviderConfig.Instance.DmlOptions.BatchUpdates.Enabled = true.

However, as I do so, I do not see constraint violations or other issues when inserting new entities. DbMonitor reports:

Code: Select all

BEGIN
INSERT INTO SCHEMA.TABLE(C1, C2)
VALUES (:p0, :p1);
INSERT INTO SCHEMA.TABLE(C1, C2)
VALUES (:p2, :p3);
END;

-- -1 row(s) affected.
But states "Completed successfully."

This is Ok for my big insert or update operations, but for all other operations in my application, I would need the exceptions from the database. So is there a way to enable batch operations only for specific queries?

Thanks for any hints or help.
Peter

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Fri 22 Dec 2017 09:46

PeterUser wrote:My question is: Can I configure OracleEntityProviderConfig.Instance.DmlOptions.BatchUpdates.Enabled only in the scope of a single query / DbContext instance?
1. There is no way to do that at the moment. We will investigate the possibility to add this feature. There is no timeframe.
PeterUser wrote:However, as I do so, I do not see constraint violations or other issues when inserting new entities.
2. Please set config.DmlOptions.BatchUpdates.ConcurrencyCheck=true. Refer to http://devart.com/dotconnect/oracle/doc ... dates.html.

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Tue 16 Jan 2018 08:01

Hi Alex,

thanks for your reply. Sorry, that I didn't reply earlier.

I already used the ConcurrencyCheck=true option, but that still swallows all helpful Oracle exceptions. Like p.e. the name of the constraint that got violated.

For me it would be great to switch on Batch Updates on a per-query or per-context basis. Is there any chance for a feature like that? Or is this not compatible with the way EF is working internally?

Greetings
Peter

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Fri 19 Jan 2018 19:04

PeterUser wrote:I already used the ConcurrencyCheck=true option, but that still swallows all helpful Oracle exceptions. Like p.e. the name of the constraint that got violated.
1. We will investigate the possibility to improve the exception messages and notify you about the result.
PeterUser wrote:For me it would be great to switch on Batch Updates on a per-query or per-context basis. Is there any chance for a feature like that? Or is this not compatible with the way EF is working internally?
2. This is too complicated task taking into account the EF6 engine implementation. JIC: EF Core allows to turn on/off batch updates on a per-context basis.

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Wed 24 Jan 2018 06:57

Thanks a lot.

Actually, we started the project using EF Core, but we ran into some bugs and missing features with the Oracle integration there. So we switched to EF 6. So I think we have to develop some workaround.

Thanks for your help so far and please keep us informed if you find something out about the error messages.

Greetings
Peter

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Wed 24 Jan 2018 19:17

PeterUser wrote:Actually, we started the project using EF Core, but we ran into some bugs and missing features with the Oracle integration there.
1. Please specify provider-specific issues you have encountered with EF Core.
PeterUser wrote:So we switched to EF 6. So I think we have to develop some workaround.

Thanks for your help so far and please keep us informed if you find something out about the error messages.
2. We cannot reproduce the issue. Both cases (ConcurrencyCheck = false/true) include inner exception with the name of constraint violated. How should we modify the following code for reproducing?

Code: Select all

            //CREATE TABLE TEST(
            //  ID NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
            //  NAME VARCHAR2(14)
            //);

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

            using (var conn = new OracleConnection("server=orcl1120;uid=alexsh;pwd=alexsh;"))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "delete from test where id > 0 and id <= 10";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "insert into test values (9, 'a')";
                cmd.ExecuteNonQuery();
            }

            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
            config.DmlOptions.BatchUpdates.Enabled = true;
            config.DmlOptions.BatchUpdates.ConcurrencyCheck = false; // true

            using (var context = new ALEXSHEntities())
            {
                TEST test;
                for (int i = 1; i <= 10; i++)
                {
                    test = new TEST();
                    test.ID = i;
                    test.NAME = "a";
                    context.TESTs.Add(test);
                }
                context.SaveChanges();
            }
a) ConcurrencyCheck = false

Code: Select all

System.Data.Entity.Infrastructure.DbUpdateException
  HResult=0x80131501
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at ConsoleApp164.Program.Main(String[] args) in D:\_AlexSh_Projects\ConsoleApp164\ConsoleApp164\Program.cs:line 64

Inner Exception 1:
UpdateException: An error occurred while updating the entries. See the inner exception for details.

Inner Exception 2:
OracleException: ORA-00001: unique constraint (ALEXSH.PK_TEST) violated
b) ConcurrencyCheck = true

Code: Select all

System.Data.Entity.Infrastructure.CommitFailedException
  HResult=0x80131501
  Message=An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.Commit(DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityTransaction.Commit()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at ConsoleApp164.Program.Main(String[] args) in D:\_AlexSh_Projects\ConsoleApp164\ConsoleApp164\Program.cs:line 64

Inner Exception 1:
OracleException: ORA-00001: unique constraint (ALEXSH.PK_TEST) violated
ORA-06512: at line 20

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Thu 25 Jan 2018 12:43

Hello

That is exactly what I need! Sorry for the work and confusion I caused. I didn't realize, that the information was right there inside the InnerException property. So obvious ...

The showstopper with EF Core was string filtering. We make heavy use of OracleFunctions.Like with different options for wildcards. You told me at the time, that the OracleFunctions class is not supported for EF Core, yet. Since I have quite some experience and base classes for EF6 and Oracle, I decided to use it for that project. Maybe I have another shot at EF Core again for the next project.

Sorry again for the stirrup and thanks for the help
Peter

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Fri 26 Jan 2018 07:46

Hello

I understand now where my problem is. I am using Transactions, and with those, the exception does not occur when I call context.Save(), but only on the next Select I do. I extended your example to show that behavior:

Code: Select all

static void Main(string[] args)
{
	var monitor = new OracleMonitor {IsActive = true};

	using (var conn = CreateConnection(connectionString))
	{
		conn.Open();
		var cmd = conn.CreateCommand();
		cmd.CommandText = "delete from test where id > 0 and id <= 10";
		cmd.ExecuteNonQuery();

		cmd.CommandText = "insert into test values (9, 'a')";
		cmd.ExecuteNonQuery();
	}

	var config = OracleEntityProviderConfig.Instance;
	config.DmlOptions.BatchUpdates.Enabled = true;
	config.DmlOptions.BatchUpdates.ConcurrencyCheck = true; // true

	using (var context = new TestContext(CreateConnection(connectionString), true))
	{
		context.Database.BeginTransaction();

		for (var i = 1; i <= 10; i++)
		{
			var test = new Test();
			test.ID = i;
			test.NAME = "a";
			context.TestItems.Add(test);
		}

		context.SaveChanges();

		// The exception is thrown here
		context.TestItems.ToList();
	}
}
Is there a way to make dotConnect/EF commit the changes when I call SaveChanges with batch updates enabled? While I can see the potential for performance improvement by combining write operations across multiple calls to SaveChanges, it makes exception handling very cumbersome when errors in write operations show up in read operations.

Greetings
Peter

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Tue 30 Jan 2018 17:15

1. If you opened a transaction manually, you should also commit it in your code:

Code: Select all

            using (var context = new ALEXSHEntities())
            {
                var transaction = context.Database.BeginTransaction();

                for (int i = 1; i <= 10; i++)
                {
                    var test = new TEST();
                    test.ID = i;
                    test.NAME = "a";
                    context.TESTs.Add(test);
                }
                context.SaveChanges();

                transaction.Commit(); // The exception is thrown here

                context.TESTs.ToList();
            }
2. It makes sense to open a transaction manually when several context.SaveChanges() calls should be within the same transaction. If there is the only context.SaveChanges(), EF engine will execute its operations within a transaction by default.

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Thu 01 Feb 2018 14:38

Hello

I know, that I should commit a transaction and, as you pointed out, the whole point of the transaction is to have several context.SaveChanges() succeed or fail in one go.

You might have guessed that the example is a simplified version. So actually I have something similar to that:
  • Open a transaction
  • Call function A that
    - Reads some data from the DB
    - Writes data to the DB
    - Calls SaveChanges()
  • Call function B that
    - Reads some other data from the DB
  • Call function C that
    - Writes data to the DB, depending on B's return value
    - Calls SaveChanges()
  • Do more stuff
  • Commit transaction
If function A wants to save data that is invalid (e.g. violates constraints), I will receive an exception when function B "Reads some other data from the DB". That is completely out of context and hard to handle. More specific: I cannot provide the user any meaningful exception, since I have no clue what function was called before B and how the hell a constrain violation can happen in a read from the database.

To have any chance of exception handling, I would need dotConnect to execute the write when I call SaveChanges.

Is there a way to achieve that?

Greetings
Peter

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Tue 06 Feb 2018 18:26

Thank you for the detailed explanation. We will investigate the question and notify you.

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Mon 19 Feb 2018 12:16

The implementation of the EF1/EF4/EF5/EF6 engines doesn't allow us to change this behavior.

The error is thrown in the following cases:
1) on a transaction commit after context.SaveChanges();
2) on context.SaveChanges() if its total amount of insert/update/delete oprations is a multiple of BatchSize
3) on the next SELECT operation

We will add this information to our documenation.

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Tue 20 Feb 2018 07:05

Thanks for the information.

You do not mention EF Core. Is there a chance to get the desired behavior using EF Core?

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

Re: Configure DmlOptions.BatchUpdates per context instance

Post by Shalex » Wed 21 Feb 2018 12:05

PeterUser wrote:Is there a chance to get the desired behavior using EF Core?
1. EF Core throws error on context.SaveChanges() now.

2. Is OracleFunctions.Like the only function you need to migrate to EF Core?

PeterUser
Posts: 36
Joined: Mon 27 Feb 2017 07:45

Re: Configure DmlOptions.BatchUpdates per context instance

Post by PeterUser » Thu 22 Feb 2018 13:57

I don't think we will mirgate to EF Core now. I put 2 weeks effort into moving away from it a couple of month ago. My database makes heavy use of composite ids and there is quite a difference in how they are handled by EF Core and EF 6.

I will try to call a Select after calling SaveChanges in my base class, if an explicit transaction is opened. That might work. I will give it a shot.

Post Reply