Page 1 of 1

Problems With Code First Model Changes

Posted: Thu 18 Aug 2011 12:10
by captaincrash
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?

Posted: Mon 22 Aug 2011 16:29
by StanislavK
Thank you for the report, we have reproduced the issue. We will analyze it and inform you about the results.

Posted: Tue 20 Sep 2011 11:47
by captaincrash
Hi guys - do you have any update on this yet?

Posted: Thu 22 Sep 2011 08:59
by StanislavK
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).

Posted: Thu 22 Sep 2011 10:17
by captaincrash
Thanks guys, I'll give it a go!

Posted: Thu 22 Sep 2011 20:08
by captaincrash
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: 

Posted: Thu 22 Sep 2011 20:33
by captaincrash
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;

Posted: Thu 22 Sep 2011 23:04
by captaincrash
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"));

Posted: Fri 23 Sep 2011 10:55
by StanislavK
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).

Posted: Fri 09 Dec 2011 16:59
by StanislavK
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