Entity Framework POCO: How to map related properties to a flattened table?

Question

I am using Entity Framework (version 4.3.1) POCO against a SQL Server 2012 database and I have this data schema...

Table2
======    
Table2Id : int : Identity (1,1) Primary Key not null
Name : nvarchar(10) not null

Table1
======    
Table1Id : int : Identity (1,1) Primary Key not null
Name : nvarchar(10) not null
Table2Id : int not null (foreign key to Table2)

Root
====
RootId : int : Identity (1,1) Primary Key not null
Table1Id : int not null (foreign key to Table1)
Table2Id : int not null (foreign key to Table2)

Nothing untoward there except now the interesting thing is that I do not want a property on my Root object for Table2, instead I want that ID to be populated from the related Table2 from the Table1.

To explain what I mean, here are my objects and context:

public class Root
{
    public int RootId { get; set; }
    public Table1 Table1 { get; set; }
}

public class Table1
{
    public int Table1Id { get; set; }
    public string Name { get; set; }
    public Table2 Table2 { get; set; }
    public virtual ICollection<Root> Roots { get; set; }
}

public class Table2
{
    public int Table2Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Table1> Table1s { get;set; }
}

public class Context : DbContext
{
    public Context(string nameOrConnectionString) 
        : base(nameOrConnectionString)
    {
        Roots = Set<Root>();
        Table1s = Set<Table1>();
        Table2s = Set<Table2>();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {          
        var table1 = modelBuilder.Entity<Table1>();
        table1.ToTable("Table1");
        table1.HasKey(r => r.Table1Id);
        table1.HasRequired(r => r.Table2)
            .WithMany(t => t.Table1s)
            .Map(m => m.MapKey("Table2Id"));

        var table2 = modelBuilder.Entity<Table2>();
        table2.ToTable("Table2");
        table2.HasKey(r => r.Table2Id);

        var root = modelBuilder.Entity<Root>();
        root.ToTable("Root");
        root.HasKey(r => r.RootId);
        root.HasRequired(r => r.Table1)
            .WithMany(t => t.Roots)
            .Map(m => m.MapKey("Table1Id"));

        // TODO: Some sort of mapping            

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Root> Roots { get; private set; }
    public DbSet<Table1> Table1s { get; private set; }
    public DbSet<Table2> Table2s{ get; private set; }
}

Ultimately I would like to run this snippet of code (on an empty database)...

using(var c = new Context(@"connection string"))
{
    c.Roots.Add(new Root
    {
        Table1 = new Table1
        {
            Name = "Test1",
            Table2 = new Table2 { Name = "Test2" }
        }
    });
    c.SaveChanges();
}

and for the data to look like this afterwards:

Table2
======
Table2Id |  Name
       1 | Test2

Table1
=======
Table1Id |  Name
       1 | Test1

Root
====
RootId | Table1Id | Table2Id
1      |        1 |        1

So, how do I perform such a mapping? I looked at the Map function and the Property function but I can't fathom what I should put where or even if they are appropriate?

Could you please write more on what you want to achieve? What is the problem to be solved? — Mare Infinitus, Jul 11 '12 at 18:10

score 3 · Answer 1 · answered Jul 11 '12 at 20:21

3

I do not want a property on my Root object for Table2, instead I want that ID to be populated from the related Table2 from the Table1.

EF doesn't support this. You cannot forward property value from relation to the main table. You must expose navigation property either on Table2 or on Root and map it in the same way as you did it for Table1.

answered Jul 11 '12 at 20:21

Ladislav Mrnka

360,892
59
660
670

Thanks. What if I put a public int Table2Id { get; set; } on the Root - would I be able to have it automatically set with the matching property Table1.Table2.Table2Id somehow? – kmp Jul 12 '12 at 07:31

score 3 · Accepted Answer · edited May 23 '17 at 12:12

Thanks to @Ladislav Mrnka pointing out that it was unsupported and that I would need a navigation property, what I decided was that I could have a private navigation property that defers to the Table1.Table2 property and hides the ickyness. So...

I defined a private navigation property on my Root class like so (notice it does not store anything, just defers to the Table1.Table2 property):

public class Root
{
    public int RootId { get; set; }
    public Table1 Table1 { get; set; }
    private Table2 Table2
    {
        get { return Table1.Table2; }
        set { Table1.Table2 = value; }
    }
}

From the question: Entity Framework 4 - Mapping non-public properties with CTP5 (code first) in a persistence unaware context I created this little helper (which just means I can get a lambda expression for the private property):

public interface IObjectExpressionCreator<T>
{
    Expression<Func<T, TResult>> Property<TResult>(string name);
}

public static class PropertyExpression
{
    private class ObjectExpressionCreator<T> : IObjectExpressionCreator<T>
    {
        public Expression<Func<T, TResult>> Property<TResult>(string name)
        {
            var p = Expression.Parameter(typeof(T),
                                         "propertyOrFieldContainer");

            var body = Expression.PropertyOrField(p, name);

            var lambda = Expression.Lambda(typeof(Func<T, TResult>),
                                           body,
                                           p);

            return (Expression<Func<T, TResult>>)lambda;
        }
    }
    public static IObjectExpressionCreator<T> For<T>()
    {
        return new ObjectExpressionCreator<T>();
    }
}

Now I perform the mapping like so:

root
    .HasRequired(PropertyExpression.For<Root>().Property<Table2>("Table2"))
    .WithMany()
    .Map(m => m.MapKey("Table2Id"));

and when I run the code in the question I get exactly what I wanted.

Entity Framework POCO: How to map related properties to a flattened table?

2 Answers2