Entity Framework Multiple DbContexts With Multiple MySql Schemas

Question

I am trying to implement two DbContexts which map to two seperate schemas/databases within MySql with a foreign key between them.

I understand questions similar to this have been asked before, but I can't find an answer in relation to MySql

I am using code first but and I'm getting the following error when I do Update-Database:

MultipleDbContext.ApplicationUser: : EntityType 'ApplicationUser' has no key defined. Define the key for this EntityType.

ApplicationUsers: EntityType: EntitySet 'ApplicationUsers' is based on type 'ApplicationUser' that has no keys defined.

These are my 2 DbContexts:

ApplicationDbContext

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base("ApplicationDBContext") {}

    public DbSet<Application> Applications { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Configurations.Add(new ApplicationConfiguration());
    }
}

public class ApplicationConfiguration : EntityTypeConfiguration<Application>
{
    public ApplicationConfiguration()
    {
        HasKey(x => x.ApplicationID);
        Property(x => x.ApplicationID).IsRequired();
        Property(x => x.ApplicationName).IsRequired();
        HasRequired(x => x.PrimaryUser).WithMany().HasForeignKey(x => x.UserID);
    }
}

ApplicationUserDbContext

public class ApplicationUserDbContext : DbContext
{
    public ApplicationUserDbContext() : base("UserDBContext") {}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ApplicationUserConfiguration());
    }
}

public class ApplicationUserConfiguration : EntityTypeConfiguration<ApplicationUser>
{
    public DbSet<ApplicationUser> ApplicationUsers { get; set; }

    public ApplicationUserConfiguration()
    {
        HasKey(x => x.UserID);
        Property(x => x.UserID).IsRequired();
        Property(x => x.Name).IsRequired();
    }
}

This is my update database statement:

update-database -ConfigurationTypeName MultipleDbContext.Migrations.Configuration

Thanks!

EDIT - Adding Entity Objects

public class Application
{
    public int ApplicationID { get; set; }
    public string ApplicationName { get; set; }
    public int UserID { get; set; }
    public virtual ApplicationUser PrimaryUser { get; set; }
}

public class ApplicationUser
{
    public int UserID { get; set; }
    public string Name { get; set; }
}

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

Problem probably is, that you have string property as a the primary key for your User and the length of this key is too long for MySql. I believe the limitation is 96 characters for keys (767 bytes).

A way to handle this would be to take only a subset of the string property and apply this to the key. To make the point clear, the following shows a key, which is only 4 chars long.

CREATE TABLE IF NOT EXISTS `foo` (
  `id` varchar(128),
  PRIMARY KEY (`id`(4)),
)

I'd suggest going all in with integer primary keys for the Aspnet.Identity stack - and also make tablenames all lowercase, since this would be an issue on mysql server hosted via case-sensitive filesystems.

This GitHub repo is kinda overkill as an example, but im pointing out a few points in the code here

Also this answer has a nice walkthrough

edited May 23 '17 at 11:48

Community

1
1

answered Oct 29 '16 at 03:51

mschr

8,531
3
21
35

Thanks for the reply but I'm using an int for both primary keys. I'm not using Identity, I just named the objects similar names. – SkelDave Nov 01 '16 at 14:50
Ah, i missed the part that you have split up into two different database connections. It is simply not possible to have one context join a query with another context. What youre essentially doing via foreign key mapping is `select * from Application app join User on App.X = User.Y` in *one single query*. You cannot – mschr Nov 03 '16 at 20:57

Entity Framework Multiple DbContexts With Multiple MySql Schemas

1 Answers1