Table splitting - is it possible to split a table into two entirely independent entities?

Question

I have a simple user table:

CREATE TABLE User
 (
UserId int,
UserName nvarchar(35),
Password nvarchar(size),
 );

I want to split this up into two entities in EF6. User and UserPassword. These represent two very different business needs, but happen to live on the same table.

So I create two entities.

public class User
{
 public int Id { get; set; }
 public string Username { get; set; }
}

public class UserPassword
{
 public int Id { get; set; }
 public string Password{ get; set; }
}

I have maps like so

class UserMap : EntityTypeConfiguration<User>
{
    public UserMap()
    {
        Property(p => p.Id).HasColumnName("UserId");
    }
}

class PasswordMap : EntityTypeConfiguration<UserPassword>
{
    public PasswordMap()
    {
        ToTable("User");
        Property(p => p.Id).HasColumnName("UserId");
    }
}

But when I use these entities I get the following error:

Message = "The entity types 'UserPassword' and 'User' cannot share table 'User' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them."

I do not want to create a relationship. I am having trouble setting up table per hierarchy. I don't want a discriminator. I really just want to have two separate ways to get to the same table that are discreet. Is there a way to do this other than creating two contexts?

"I do not want to create a relationship." But you already did when they were made to live in the same table. What happens when you insert a new user? — Jon Hanna, Jun 05 '15 at 12:44
The password is nullable. Adding a new user does not effect the password. — Price Jones, Jun 05 '15 at 12:45
I think I'd just workaround the problem by having an updatable view in the database for `UserPassword` and then using that as the table. — Jon Hanna, Jun 05 '15 at 12:47
Awesome. Exactly the kind of solution I was looking for. Thank you. — Price Jones, Jun 05 '15 at 13:05

score 1 · Accepted Answer · answered Jun 05 '15 at 13:08

1

Ah. As per my comment, if you created an updatable view for UserPassword then you could use that as the table for the entity. EF will neither know nor care that updating that is actually updating the users table, and vice versa.

answered Jun 05 '15 at 13:08

Jon Hanna

110,372
10
146
251

Table splitting - is it possible to split a table into two entirely independent entities?

1 Answers1