How to map EF object through mapping table

Question

I have two database tables -- Customer_Values and Customer_Attributes... and it's... dumb. Terrible.

Customer_Values has things like

CustomerId | AttributeId | Value
--------------------------------
        01 |          01 | Steve
        02 |          01 | Frank
        01 |          02 | Smith 

etc...

And Customer_Attributes looks like this

AttributeId |      Value
------------------------
         01 | First Name
         02 |  Last Name

I would like to map the first table into an Entity object like this...

class {
   string FirstName { get; set; }
   string LastName { get; set; }
}

But I'm not at all sure at all how to do that.

I'm using EF 6, code-first

Any particular reason that you want to map them to an entity object together instead of mapping to objects as two separate entities and then using LINQ/EF Core to do your join afterwards? — Sam Mullinix, Jul 11 '19 at 14:24
@SamMullinix -- Only because there will never be an instance where I want to pull from the values table without filtering by `AttributeId`. I'd rather do all of that up front, for ease of use. — Dave Matney, Jul 11 '19 at 14:41

score 0 · Answer 1 · answered Jul 11 '19 at 15:19

Assuming your context could be as follows (notice the FK reference, we can se the model up to enable the reference of the FK via Customer_Values):

class MyContext : DbContext
{
    public DbSet<Customer_Values> Customer_Value { get; set; }
    public DbSet<Customer_Attributes> Customer_Attribute { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer_Attributes>()
            .HasOne(v => v.Customer_Values)
            .WithOne(a => a.Customer_Attributes)
            .HasForeignKey(v => v.AttributeId)
            .HasConstraintName("FK_Constraint_Name_Here");
    }
}

Customer Values reference to Customer Attributes:

public class Customer_Values
{
    public int CustomerId { get; set; }
    public int AttributeId { get; set; }
    public string Value { get; set; }

    public <Customer_Attributes> Customer_Attribute { get; set; }
}

Customer Attributes picks up the Customer ID via 1:1 relationship

public class Customer_Attributes
{
    public int AttributeId { get; set; }
    public string Value { get; set; }

    public int CustomerId { get; set; }
}

Hopefully this gives you the right idea!

Microsoft Doc FK Modeling Reference

How to map EF object through mapping table

1 Answers1