Problems With Code First Model Changes

Problems With Code First Model Changes

Postby 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?
captaincrash
 
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby captaincrash » Tue 20 Sep 2011 11:47

Hi guys - do you have any update on this yet?
captaincrash
 
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Postby 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).
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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

Postby 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

Postby 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

Postby 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"));
captaincrash
 
Posts: 10
Joined: Wed 22 Jun 2011 17:18

Postby 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

Postby 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
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for MySQL