Create a database relationship between 2 entities in different Entity Framework DbContexts

Question

I have two Objects (UserProfile and Login) that resides in different assemblies and I have to create a relationship between them in SQL Server (I don't care about the DbContext relationship, but I'd like to assure the database referential integrity between the their respective tables).

The easiest way is to update the database (I am using code first) and run a alter table to create the relationship however I think this approach is kind of dirty (even if using SqlResource in Up method).

I tried unsuccessfully to create this relationship using HasOptional and Map methods (fluent api says that ProfileId isn't a reference type when I try something like "HasOptional(a => a.IdUsuario)...").

// UserContext's Classes (Assembly 'A')
public class UserProfile
{
    public int Id { get; set; } // PK
    // some properties omitted
}

public class UserContext : DbContext
{
    DbSet<UserProfile> UsersProfiles { get; set; }
}

// SecurityContext's Classes (Assembly 'B')
public class Login
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public int ProfileId { get; set; } // UserProfile's FK
}

public class SecurityContext : DbContext
{
    DbSet<Login> Logins { get; set; }
}

I tried to reference both classes, include navigation properties and use "modelBuilder.Ignore<>" to select which class I want to really synchronize in each context, but this ends in a circular reference problem.

I receive the message "The navigation property 'UserProfile' is not a declared property on type 'Login'. Verify that it has not been explicitly excluded from the model and that it is a valid navigation property." if try to create fake classes just to mapping purposes.

Am I missing something?

Answer 1

Yes, you are missing some points.

The UserProfile is a weak entity, it is a dependant of Login. The relationship is 1:1. 1 Login has 1 UserProfile.

Your classes should be like this:

public class UserProfile
{
    //UserProfile does not have its own Id,
    //Its Id is the LoginId, which is the primary key and a foreign key
    //It ensures the 1:1 relationship
    public int LoginId { get; set; } // PK and FK
    // some properties omitted
    public Login Login { get; set; }
}

public class Login
{
    public int LoginId { get; set; }
    public string UserName { get; set; }

    //Login does not have UserProfile fk
    //because it is the Principal of the relationship
    public UserProfile Profile { get; set; }
}

Mapping:

modelBuilder.Entity<UserProfile>()
    .HasKey(i => i.LoginId);

modelBuilder.Entity<Login>()
    .HasKey(i => i.LoginId);

modelBuilder.Entity<Login>()
    .HasRequired(i => i.Profile)
    .WithRequiredPrincipal(i => i.Login)
    .WillCascadeOnDelete(false);

Generated Migration:

CreateTable(
    "dbo.Logins",
    c => new
        {
            LoginId = c.Int(nullable: false, identity: true),
            UserName = c.String(),
        })
    .PrimaryKey(t => t.LoginId);

CreateTable(
    "dbo.UserProfiles",
    c => new
        {
            LoginId = c.Int(nullable: false),
        })
    .PrimaryKey(t => t.LoginId)
    .ForeignKey("dbo.Logins", t => t.LoginId)
    .Index(t => t.LoginId);

EDIT

Since Login does not belong to the domain assembly, Login is the weak entity. Login has a dependency of UserProfile.

public class UserProfile
{
    public int UserProfileId { get; set; } // PK
    // some properties omitted
    // we don't have Login property here
}

public class Login
{
    //login pk must be userprofile FK
    //so it ensures the 1:1 relationship
    public int UserProfileId { get; set; } //PK

    public string UserName { get; set; }

    public UserProfile Profile { get; set; }// UserProfile's FK
}

Mapping:

modelBuilder.Entity<Login>()
    .HasKey(i => i.UserProfileId);

modelBuilder.Entity<Login>()
    .HasRequired(i => i.Profile)
    .WithRequiredDependent();

Generated Migration

CreateTable(
    "dbo.UserProfiles",
    c => new
        {
            UserProfileId = c.Int(nullable: false, identity: true),
        })
    .PrimaryKey(t => t.UserProfileId);

CreateTable(
    "dbo.Logins",
    c => new
        {
            UserProfileId = c.Int(nullable: false),
            UserName = c.String(),
        })
    .PrimaryKey(t => t.UserProfileId)
    .ForeignKey("dbo.UserProfiles", t => t.UserProfileId)
    .Index(t => t.UserProfileId);