Many to Many Relationships without Double Junction Tables, Entity Framework

Question

I have the following setup (which works fine). Using CodeFirst (CTP4).

A template has a list of influences, each influence gives a value to a trait.

public class Template
{
    public virtual int Id { get; set; }
    public virtual ICollection<Influence> Influences { get; set; }
}

public class Influence
{
    public virtual int Id { get; set; }
    public virtual Trait Trait { get; set; }
    public virtual int Value { get; set; }
}

public class Trait
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

Template is configured like this.

public class TemplateConfiguration : EntityConfiguration<Template>
{
    public TemplateConfiguration()
    {
        HasKey(o => o.Id);
        Property(o => o.Id).IsIdentity();

        HasMany(o => o.Influences).WithRequired()
            .Map("templates.influences",
            (template, influence) => new {
                Template = template.Id,
                Influence = influence.Id
            });
        MapSingleType(o => new {
            o.Id
        });
    }
}

This works but I'd rather avoid the extra 'influences' table. Essentially, 'Influences' is simply an object and there doesn't need to be a central store for them. In fact, it is more beneficial to the design I am trying to approach if there is not a central table for them.

I wish to setup a scenario like this for the Template table... Basically Influences don't have their own table, they're just mapped by Trait/Value where they are used.

    public TemplateConfiguration()
    {
        HasMany(u => u.Influences).WithMany()
            .Map("templates.influences",
            (template, influence) => new {
                Template = template.Id,
                Trait = influence.Trait.Id,
                Value = influence.Value
            });

        MapSingleType(c => new {
            c.Id
        }).ToTable("templates");
    }

When I try to do it this way, I get the exception on the Template Mapping.

System.InvalidOperationException was unhandled

The given expression includes an unrecognized pattern 'influence.Trait.Id'.

---

Below is the entire project code, if needed.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.EntityClient;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace EFTest
{
    class Program
    {
        static void Main(string[] args)
        {
                    Database.SetInitializer<SampleDataContext>(new AlwaysRecreateDatabase<SampleDataContext>());
            var builder = new ModelBuilder();

            builder.Configurations.Add(new TraitConfiguration());
            builder.Configurations.Add(new InfluenceConfiguration());
            builder.Configurations.Add(new TemplateConfiguration());

            var model = builder.CreateModel();

            using (var context = new SampleDataContext(model))
            {
                var traits = new List<Trait>
                {
                    new Trait { Name = "Years" },
                    new Trait { Name = "Days" }
                };
                traits.ForEach(x => { context.Traits.Add(x); });
                context.SaveChanges();

                var templates = new List<Template>
                {
                    new Template
                    {
                        Influences = new List<Influence>
                        {
                            new Influence
                            {
                                Trait = context.Traits.Single( i => i.Name == "Years" ),
                                Value = 5
                            },
                            new Influence
                            {
                                Trait = context.Traits.Single( i => i.Name == "Days" ),
                                Value = 15
                            }
                        }
                    }
                };
                templates.ForEach(x => { context.Templates.Add(x); });
                context.SaveChanges();
            }
        }
    }

    public class SampleDataContext : DbContext
    {
        public SampleDataContext(DbModel model)
            : base(model)
        {
        }

        public DbSet<Trait> Traits { get; set; }
        public DbSet<Influence> Influences { get; set; }
        public DbSet<Template> Templates { get; set; }
    }

    public class Trait
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    public class TraitConfiguration : EntityConfiguration<Trait>
    {
        public TraitConfiguration()
        {
            HasKey(o => o.Id);
            Property(o => o.Id).IsIdentity();

            MapSingleType(o => new {
                o.Id,
                o.Name
            });
        }
    }

    public class Influence
    {
        public virtual int Id { get; set; }
        public virtual Trait Trait { get; set; }
        public virtual int Value { get; set; }
    }

    public class InfluenceConfiguration : EntityConfiguration<Influence>
    {
        public InfluenceConfiguration()
        {
            HasKey(o => o.Id);
            Property(o => o.Id).IsIdentity();

            HasRequired(o => o.Trait);
            Property(o => o.Value);

            MapSingleType(o => new {
                o.Id,
                Trait = o.Trait.Id,
                o.Value
            });
        }
    }

    public class Template
    {
        public virtual int Id { get; set; }
        public virtual ICollection<Influence> Influences { get; set; }
    }

    public class TemplateConfiguration : EntityConfiguration<Template>
    {
        public TemplateConfiguration()
        {
            HasKey(o => o.Id);
            Property(o => o.Id).IsIdentity();

            HasMany( o => o.Influences).WithRequired()
                .Map("templates.influences", 
                (template, influence) => new {
                    Template = template.Id,
                    Influence = influence.Id
                });
            MapSingleType(o => new {
                o.Id
            });
        }
    }
}

Answer 1

OK, new day new idea.

I have now installed CTP4 and got the same 4 tables as you got.

The reason why the many to many relation is produced, is that the model does not know that an influence will only be used by one template. Then you have to tell it that:

public class Influence
 {
    public virtual int Id { get; set; }
    public virtual Trait Trait { get; set; }
    public virtual int Value { get; set; }
    public virtual Template Template { get; set; }
 } 

and:

    public InfluenceConfiguration()
    {
        HasKey(o => o.Id);
        Property(o => o.Id).IsIdentity();
        Property(o => o.Value);

        MapSingleType(o => new
        {
            o.Id,
            Trait = o.Trait.Id,
            o.Value,
            Template = o.Template.Id
        });
    } 

The influences table will then look like this:

CREATE TABLE [dbo].[Influences](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Template] [int] NULL,
    [Trait] [int] NULL,
    [Value] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Influences]  WITH CHECK ADD  CONSTRAINT [Influence_Template] FOREIGN KEY([Template])
REFERENCES [dbo].[Templates] ([Id])
GO

ALTER TABLE [dbo].[Influences] CHECK CONSTRAINT [Influence_Template]
GO

ALTER TABLE [dbo].[Influences]  WITH CHECK ADD  CONSTRAINT [Influence_Trait] FOREIGN KEY([Trait])
REFERENCES [dbo].[Traits] ([Id])
GO

ALTER TABLE [dbo].[Influences] CHECK CONSTRAINT [Influence_Trait]
GO