Exception during DbContext.SaveChanges()

[cht]

Hello, I have a problem with a query, using Devart.Data.MySql 8.4.543.0. I have this method:

Code: Select all

        public void RemoveSupplierArea(long _suppliersID, string _symbolOfArea)
        {
            byte area_id = this.GetAreaBySymbol(_symbolOfArea).id;
            var sup_a = from t in Entity.suppliers_areas
                                    where (t.areas_id == area_id) && (t.suppliers_id == _suppliersID)
                                    select t;
            List<suppliers_areas> sal = sup_a.ToList();
                         
            foreach(suppliers_areas sa in sal)
            {
                Entity.suppliers_areas.Remove(sa);
            }
            Entity.SaveChanges();
        }

and calling it gives me an System.Data.Entity.Infrastructure.DbUpdateException while calling SaveChanges(). Same thing happens if I use RemoveRange(). I added the exception detail at the end.

The selection of the records to delete works well. Am I doing something wrong, or is this a bug?

The table suppliers_areas is very simple, just this:

Code: Select all

CREATE TABLE `suppliers_areas` (
	`suppliers_id` INT(10) UNSIGNED NOT NULL,
	`areas_id` TINYINT(3) UNSIGNED NOT NULL,
	UNIQUE INDEX `suppliers_id` (`suppliers_id`, `areas_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
;

MySQl is quite old, 5.0.67, but it is not in my power to change that, so I'll have to get along with it.

Code: Select all

System.Data.Entity.Infrastructure.DbUpdateException was unhandled by user code
  HResult=-2146233087
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
       bei System.Data.Entity.Internal.InternalContext.SaveChanges()
       bei System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
       bei System.Data.Entity.DbContext.SaveChanges()
       bei WipsModelManager.WipsModelManager.RemoveSupplierArea(Int64 _suppliersID, String _symbolOfArea) in C:\Users\Administrator\Documents\Visual Studio 2015\Projects\WinWipsProject\WipsModelManager\WipsModelManager.cs:Zeile 372.
       bei WipsUnitTests.UnitTest1.DeleteSuppliersArea() in C:\Users\Administrator\Documents\Visual Studio 2015\Projects\WinWipsProject\WipsUnitTests\UnitTest1.cs:Zeile 41.
  InnerException: 
       HResult=-2146233087
       Message=An error occurred while updating the entries. See the inner exception for details.
       Source=EntityFramework
       StackTrace:
            bei System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
            bei System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
            bei System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
            bei System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
            bei System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
            bei System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
            bei System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
            bei System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
            bei System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
            bei System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
            bei System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
            bei System.Data.Entity.Internal.InternalContext.SaveChanges()
       InnerException: 
            Code=1064
            ErrorCode=-2147467259
            HResult=-2147467259
            Message=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT 
suppliers_areas.suppliers_id,
suppliers_areas.areas_id
FROM wips.suppli' at line 1
            Source=Devart.Data.MySql
            SqlState=42000
            StackTrace:
                 bei Devart.Data.MySql.bi.a()
                 bei Devart.Data.MySql.bi.i()
                 bei Devart.Data.MySql.af.a(ab[]& A_0, Int32& A_1, Boolean A_2)
                 bei Devart.Data.MySql.af.a(Byte[] A_0, Int32 A_1, Boolean A_2, String A_3)
                 bei Devart.Data.MySql.h.e()
                 bei Devart.Data.MySql.h.m()
                 bei Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
                 bei Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
                 bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
                 bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
                 bei Devart.Common.DbCommandBase.ExecuteNonQuery()
                 bei Devart.Data.MySql.Entity.ab.g()
                 bei System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
                 bei System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
                 bei System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
                 bei System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
                 bei System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
                 bei System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
            InnerException: 

[Shalex]

We cannot reproduce the issue at the moment. Please specify:
1) the exact SQL statement which fails to execute (for this, enable dbMonitor)
2) send us a small complete test project with the corresponding DDL/DML script for reproducing

[cht]

Hello,

thanks for your fast reply. The failing SQL statement is:

Code: Select all

DELETE FROM (SELECT 
suppliers_areas.suppliers_id,
suppliers_areas.areas_id
FROM wips.suppliers_areas AS suppliers_areas)
WHERE suppliers_id = :p0 AND areas_id = :p1

This also fails (with the same error) sending it directly to the DB with HeidiSQL. (Parameters substituted by hand, of course). It seams to me, that incompatible SQL is generated on a quite general level in this case, so I hope, a test project won't be necessary. If I'm wrong with that, please say so.

And: Do you have an idea how to rewrite the LINQ to generate different SQL as a workaround?

[Shalex]

Please check the SSDL definition of your suppliers_areas entity. Looks like it has a defining query:

Code: Select all

          <EntitySet Name="suppliers_areas" EntityType="Model.Store.suppliers_areas" store:Type="Tables">
            <DefiningQuery>SELECT 
suppliers_areas.suppliers_id,
suppliers_areas.areas_id
FROM wips.suppliers_areas AS suppliers_areas</DefiningQuery>
          </EntitySet>

You should replace it with the following definition:

Code: Select all

          <EntitySet Name="suppliers_areas" EntityType="Model.Store.suppliers_areas" store:Type="Tables" Schema="wips" Table="suppliers_areas" />

Now it should work.

JIC:
We recommend you to use Entity Developer (Devart Entity Model, *.edml) instead of EDM Designer (ADO.NET Entity Data Model, *.edmx) because it is adjusted for working with MySQL and has an advanced functionality: http://www.devart.com/entitydeveloper/ed-vs-edm.html. In particular, Entity Developer allows editing SSDL in design time.

[cht]

This works, thanks. Except that it was "Self.suppliers_areas" instead of "Model.Store.suppliers_areas", just for the case somebody else falls into the same pit.

But now I wonder, what will happen if I have to recreate the model? Will it be recreated the wrong way again?

Regarding using Devart Entity Model: I'll have a look into that, but maybe you'll have a short answer: My situation is I have this old MySQL DB, which is currently used by a bunch of legacy code. My primary goal is to get rid of this code, to be able to make changes to the DB schema again. The second step would be being able to switch the DB entirely, and I guess I will be stuck to ADO.NET Entity Data Model if MS SQL should be an option at that point. Or am I wrong with that?

[Shalex]

But now I wonder, what will happen if I have to recreate the model? Will it be recreated the wrong way again?

Your suppliers_areas table doesn't have primary key. This makes EDM Wizard to create defining query for it.

JIC (about Entity Developer):
Our tool doesn't create defining queries automatically. There are the following rules of creating EntityKey basing on the availability 1-3 in the table/view:
1. primary key
2. unique index
3. non-nullable columns
If a table/view includes nothing from the listed options, EntityKey is not created.

Regarding using Devart Entity Model: I'll have a look into that, but maybe you'll have a short answer: My situation is I have this old MySQL DB, which is currently used by a bunch of legacy code. My primary goal is to get rid of this code, to be able to make changes to the DB schema again. The second step would be being able to switch the DB entirely, and I guess I will be stuck to ADO.NET Entity Data Model if MS SQL should be an option at that point. Or am I wrong with that?

You can use Entity Developer with SQL Server. For this, you should install Entity Developer as a separate product of the Entity Framework or Professional edition (editions matrix). It works with SQL Server via standard System.Data.SqlClient provider.

If you have *.edml (Devart.Data.MySql), you can easily switch it to *.edml (System.Data.SqlClient) by changing connection string via Database Explorer and then right click designer surface > Regenerate Storage and Mapping. That's all.