Problems With Code First Model Changes

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Problems With Code First Model Changes

Post by captaincrash » Thu 18 Aug 2011 12:10

Hiya, I've been using the library for a while fine however recently I've implemented some more complex model relationships using code first, but the connector can't seem to drop this type of model when it changes due to MySQL key constraints. For example;

Code: Select all

public class ClassA {
    public int Id {get;set;}
    public virtual ClassB {get;set;}
 }

public class ClassB {
    public int Id {get;set;}
}
Results in an inner exception; Cannot delete or update a parent row: a foreign key constraint fails

Presently the only way for me to get around this issue is to drop into MySQL, disable foreign key checks, then manually drop the table;

Code: Select all

SET FOREIGN_KEY_CHECKS = 0;
drop project_ClassA
SET FOREIGN_KEY_CHECKS = 1;
At which point the model can then be-recreated by the connector. So what am I missing?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 22 Aug 2011 16:29

Thank you for the report, we have reproduced the issue. We will analyze it and inform you about the results.

captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Post by captaincrash » Tue 20 Sep 2011 11:47

Hi guys - do you have any update on this yet?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 22 Sep 2011 08:59

The issue is fixed in the latest 6.50.214 version of dotConnect for MySQL. You can download the new build from
http://www.devart.com/dotconnect/mysql/download.html
(the trial only) or from Registered Users' Area (provided that you have an active subscription).

captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Post by captaincrash » Thu 22 Sep 2011 10:17

Thanks guys, I'll give it a go!

captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Post by captaincrash » Thu 22 Sep 2011 20:08

Hi - I've tried this out today - still doesn't work sorry, seems to be with this type of relationship;

Code: Select all

 modelBuilder.Entity()
                .HasMany(x => x.InternalTransactions)
                .WithMany();
Exception details;

Code: Select all

Devart.Data.MySql.MySqlException was unhandled by user code
  Message=Cannot delete or update a parent row: a foreign key constraint fails
  Source=Devart.Data.MySql.Entity
  ErrorCode=-2147467259
  Code=1217
  SqlState=23000
  StackTrace:
       at Devart.Common.Entity.r.a(DbConnection A_0, Nullable`1 A_1, a7 A_2)
       at Devart.Common.Entity.r.a(DbConnection A_0, Nullable`1 A_1)
       at Devart.Common.Entity.cq.w()
       at Devart.Data.MySql.Entity.MySqlEntityProviderServices.DbDeleteDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
       at System.Data.Common.DbProviderServices.DeleteDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
       at System.Data.Objects.ObjectContext.DeleteDatabase()
       at System.Data.Entity.Internal.DatabaseOperations.DeleteIfExists(ObjectContext objectContext)
       at System.Data.Entity.Database.Delete()
       at System.Data.Entity.DropCreateDatabaseIfModelChanges`1.InitializeDatabase(TContext context)
       at System.Data.Entity.Database.c__DisplayClass2`1.b__0(DbContext c)
       at System.Data.Entity.Internal.InternalContext.c__DisplayClass5.b__3()
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
       at System.Data.Entity.Internal.LazyInternalContext.b__4(InternalContext c)
       at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
       at System.Linq.Queryable.Where[TSource](IQueryable`1 source, Expression`1 predicate)
       at Cloudthing.Tutorsdirect.Web.Controllers.AccountController.LogOn() in C:\devProj\tutorsdirect\Application\Cloudthing.Tutorsdirect.Web\Cloudthing.Tutorsdirect.Web\Controllers\AccountController.cs:line 65
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.c__DisplayClass15.b__12()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
  InnerException: 

captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Post by captaincrash » Thu 22 Sep 2011 20:33

To anyone else that is suffering this problem regularly, this might help you out;

Code: Select all

SET @dropStatement = '';
SELECT group_concat(' ', table_name)
INTO
  @dropStatement
FROM
  information_schema.tables
WHERE
  table_schema = '{your table}'
  AND table_name LIKE '{your_prefix}%';
SET @dropStatement = concat('DROP TABLE ', @dropStatement);
PREPARE stmt FROM @dropStatement;
SET FOREIGN_KEY_CHECKS = 0;
EXECUTE stmt;
SET FOREIGN_KEY_CHECKS = 1;

captaincrash
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Post by captaincrash » Thu 22 Sep 2011 23:04

After using this this evening, it appears as though if you name a 'lookup' table in the model builder, you get the above error every time the model changes (previously it just become un-stuck when you changes the FK schema)....e.g. this always generates an error;

Code: Select all

modelBuilder.Entity()
                .HasMany(x => x.InternalTransactions)
                .WithMany()
                .Map(m => m.ToTable("td_z_facc2itrans"));

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 23 Sep 2011 10:55

We have reproduced the problem with many-to-many associations. We will analyze it and inform you about the results.

As a temporary workaround, please try configuring this association with no defaults, e.g.,

Code: Select all

modelBuilder.Entity() 
  .HasMany(x => x.InternalTransactions) 
  .WithMany(x => x.[FinancialAccounts]) 
    .Map(m => m
      .ToTable("td_z_facc2itrans")
      .MapLeftKey("[FinancialAccountId]")
      .MapRightKey("[InternalTransactionId]"));
    );
(replace the entries in square brackets with actual names of the second table and columns used for the association).

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 09 Dec 2011 16:59

We have fixed the problem with dropping the database schema. The fix is available in the latest 6.60.258 build of dotConnect for MySQL. The new build can be downloaded from
http://www.devart.com/dotconnect/mysql/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only).

For the detailed information about the fixes and improvements available in dotConnect for MySQL 6.60.258, please refer to
http://www.devart.com/forums/viewtopic.php?t=22837

Post Reply