Entity Framework one-to-many join table with metadata

Question

I'm trying to implement something of a join table in Entity Framework but I can't really seem to get any results back.

I have two main objects:

public class Device
{
    [Key]
    public int Id { get; set; }

    public virtual ICollection<Connection> Slaves { get; set; }
}

public class Connection
{
    [Key]
    public int Id { get; set; }

    public virtual Device Master { get; set; }
    public virtual Device Slave  { get; set; }

    public DateTime       Start  { get; set; }
    public DateTime       End    { get; set; }
}

public class Context : DbContext
{
    public virtual DbSet<Device> Devices { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         // Fluent API???
    }
}

In words: I have a class Device which can contain multiple slave devices. Slave devices are coupled using a join table and their association is only valid when the End timestamp is smaller than the current time.

I've been trying to get it to work with Fluent API but to no avail. As to not obscure my question I'll leave my feeble attempts out for now. I was able to create the join table with the proper FK columns but when querying for the slave list it returns null. Looking into the generated SQL it is not even querying the join table so that kinda makes sense =)

Could you help me out getting this (rather uncommon) linkage to work? Also, if I'm going about this all wrong, please let me know to; nothing is set in stone yet.

Thanks!

UPDATE

Ok, so I got the one-to-many assoc working. I followed @DavidG's advice and moved the Slaves List into the Device class and made that virtual. That seemed like a crucial step since adding virtual suddenly made EF query the Connections table.

I added this to my context:

public class Context : DbContext
{
    public virtual DbSet<Device>     Devices     { get; set; }
    public virtual DbSet<Connection> Connections { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
          // Create a one-to-many for the connections
        modelBuilder.Entity<Device>().HasMany(g => g.Devices).WithRequired(c => c.Master);
        modelBuilder.Entity<Connection>().HasRequired(c => c.Slave).WithMany().WillCascadeOnDelete(false);

        modelBuilder.Entity<Connection>()
            .Property(f => f.Start)
            .HasColumnType("datetime2")
            .HasPrecision(0);

        modelBuilder.Entity<Connection>()
            .Property(f => f.End)
            .HasColumnType("datetime2")
            .HasPrecision(0);
    }
}

And now I'm getting all the slave devices back at least (new Context().Devices). Thanks! What I still can't figure out though is how to filter on Connection properties like: End == null though.

Any help would be appreciated!

I don't think you can do it in this way with Entity Framework. You either need to add the properties of `MasterDevice` to `Device` or set it up as another table that links back to `Device`. — DavidG, Aug 24 '15 at 12:32
Yeah, I thought so after writing it down. Let me simply my question by removing the `MasterDevice`. — Nebula, Aug 24 '15 at 12:34
I dont know whether this is to do with EF or the way you're constructing the entities. — Callum Linington, Aug 24 '15 at 12:40
@CallumLinington Me neither =) But if you have a different view then please share; I'm open to suggestions. — Nebula, Aug 24 '15 at 13:41
Is your Connection object stored in the database? If it is, you'd need to add a relation between it and the Device table. If it isn't, you'd need to query on the devices using the `.Where` operator. e.g., `var c = new Connection() { Master = context.Devices.Where( d => d == blah blah ).Single() }` — Tony Vitabile, Aug 24 '15 at 14:42

score 0 · Answer 1 · answered Aug 24 '15 at 12:43

Try with,

public class Device
{
      [Key]
    public int Id { get; set; }

    public ICollection<Connection> Slaves { get; set; }
}

public class Connection
{
    [Key]
    public int Id { get; set; }

    [ForeignKey("Master")]
    public int MasterDeviceId { get; set; }
    [ForeignKey("Slave")]
    public int SlaveDeviceId { get; set; }

    public virtual Device Master { get; set; }
    public virtual Device Slave  { get; set; }

    public DateTime       Start  { get; set; }
    public DateTime       End    { get; set; }
}

Thanks, I now got the `Slaves` list working. Could you help on the final step: filtering based on the `Connection` properties? — Nebula, Aug 24 '15 at 13:38

Entity Framework one-to-many join table with metadata

1 Answers1