Entity framework map entity to multiple tables?

Question

I'm facing a problem using EF. I have the following situation:

Table User: Username, Password, RoleId, IsActive, CreatedDate, ActivedDate

Table Admin: Username, Name

Table Staff: Username, Name, Position, Phone

From this database schema i'd like to generate the following entity by merge tables data:

public class User
{
    [Key]
    public string Username { get; set; }
    public string Password { get; set; }
    public int RoleId { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime ActivedDate { get; set; }

    public string Name { get; set; }
    public string Phone { get; set; }
    public string Position { get; set; }

    [ForeignKey("RoleId")]
    public Role Role { get; set; }
}

configuration class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Map(map =>
            {
                map.Properties(p => new
                {
                    p.Username,
                    p.Password,
                    p.RoleId,
                    p.IsActive,
                    p.CreatedDate,
                    p.ActivedDate
                });
                map.ToTable("User");
            })
            .Map(map =>
            {
                map.Properties(p => new
                {
                    p.Username,
                    p.Name
                });
                map.ToTable("Admin");
            }).Map(map =>
            {
                map.Properties(p => new
                {
                    p.Username,
                    p.Name,
                    p.Phone,
                    p.Position
                });
                map.ToTable("Staff");
            });

        base.OnModelCreating(modelBuilder);
    }

I've tested it but it doesn't work as expected. I always get this message:

Properties for type 'User' can only be mapped once. The non-key property 'Name' is mapped more than once. Ensure the Properties method specifies each non-key property only once.

Am I missing something?

maybe someone has an actual solution - but I *think* you'll have to go the same road you went with `Role` (foreign-key, ...) — Random Dev, Jun 03 '15 at 05:13
Are the users inherited users of a User class? Or does your code only have a single User for all those entities? You can lookup inheritance strategies here: http://blogs.msdn.com/b/alexj/archive/2009/04/15/tip-12-choosing-an-inheritance-strategy.aspx I see that you have a role entity, I don't think that how I would have solved it... I would have a base Person, and let the User, Admin and Staff inheritate from that abstract class. And then I would have used Table Per Type (TPT) to avoid several null values in my database. — jonas, Jun 03 '15 at 07:15

score 0 · Accepted Answer · answered Jul 04 '15 at 20:44

The problem is that the two Names in Admin and Staff of course can't both be mapped onto one Name property in User.

But you can have two different name properties and map both to the same column names in two tables:

Changes in User:

public class User
{
    [Key]
    public string Username { get; set; }
    ...
    public string AdminName { get; set; }
    public string StaffName { get; set; }
    ...
}

And the mapping fragments:

.Map(map =>
{
    map.Properties(p => new
    {
        p.Username,
        p.AdminName
    });
    map.Property(p => p.AdminName).HasColumnName("Name");
    map.ToTable("Admin");
}).Map(map =>
{
    map.Properties(p => new
    {
        p.Username,
        p.StaffName,
        p.Phone,
        p.Position
    });
    map.Property(p => p.StaffName).HasColumnName("Name");
    map.ToTable("Staff");
});

Entity framework map entity to multiple tables?

1 Answers1

Linked