Direct access to EF junction table

Question

I would need direct access to this table. Yes, I know there are questions like this on SO and they all end up showing people not yet accustomed to EF navigation why they don't need this. But I happen to know how to use EF normally and I still need this, because I need to backup and restore an EF-driven database to and from an external format.

I can get all entries in the junction table like this:

from foo in Foo
from bar in foo.Bar
select new FooBar(foo.id, bar.id);

where FooBar is an appropriate helper class storing the two keys making up the relationship. I can export this list to a format of my choice easily. However, during restore, I would need to populate an emptied Foo_Bar junction table with these original entries coming from the backup (tracking and foreign key constraints are disabled during the restore operation, the incoming data is known to be good, input validation isn't important here). Because the database is not in a consistent state in the midst of a restore operation, using the traditional way of creating and attaching all entities is not possible.

One idea I found somewhere, but unfortunately, without any details of how to do it, was to create a secondary context consisting only of the junction tables and populating that directly. That would be a very efficient and nice solution to my problem but how can that be done?

Update:

By the suggestions, I tried to add this to the model:

public virtual DbSet<Foo_Bar> Foo_Bar { get; set; }

[Table("Foo_Bar")]
public class Foo_Bar {
  [Key, Column(Order = 1)]
  public long foo_id { get; set; }

  [Key, Column(Order = 2)]
  public long bar_id { get; set; }
}

Specifying the many-to-many relationship as:

modelBuilder.Entity<Bar>()
  .HasMany(x => x.Foo)
  .WithMany(x => x.Bar)
  .Map(config => {
    config.MapLeftKey("foo_id");
    config.MapRightKey("bar_id");
    config.ToTable("Foo_Bar");
  });

This results in an error message of: EntitySet 'FooBar' with schema 'dbo' and table 'Foo_Bar' was already defined. Each EntitySet must refer to a unique schema and table. Actually, it wasn't, at least not intentionally. I have no FooBar in the model, just Foo, Bar and the junction table Foo_Bar I want to define now. It's probably something automatic but I can't see what causes it. My modelBuilder call definitely maps it to the underscored name.

Add an extra column to the table that you just won't use. That way EF will not treat it as a 'junction table'. Not the cleanest approach though. — Peter Bons, Feb 19 '17 at 21:20
But I need it to treat as a junction table. I use it normally with EF and I'm perfectly happy with it handling it for me. But I also happen to need the backup/restore functionality. — Gábor, Feb 19 '17 at 21:26
Well, it will still work like always, only difference is that due to the extra column it will be included in the model and so you can reach it directly. A pure junction table consisting of just the foreign keys will be left out of the model. — Peter Bons, Feb 19 '17 at 21:34
It was created by EF upon `.HasMany(...).WithMany(...).Map(config => config.ToTable("Foo_Bar"))` for me. How can I modify it? Am I allowed to create my own table the usual way and then just refer to it with my name in this `Map()`? — Gábor, Feb 19 '17 at 21:47
I don't know if you refer to [this](http://stackoverflow.com/a/33461427/861716), but creating a second context is all there is to it (and don't use it for migrations). — Gert Arnold, Feb 19 '17 at 22:40
No, I haven't seen that one (and the scheme in the question might just be enough for me this time). I tried to include the junction in my model but I got error messages from EF complaining about it being duplicate. As I only need to add, because I can delete the whole table with a simple SQL statement, I hope I can get away with what I have now but it would be nice to have a definitive answer to the question and I'm not able to provide it yet. :-) — Gábor, Feb 20 '17 at 09:02

score 0 · Answer 1 · edited May 23 '17 at 12:31

OK, I don't yet know how to solve it within the model as Gert suggested (without getting error messages) but a secondary context solved it all right:

public class JunctionModel : DbContext {
  public virtual DbSet<Foo_Bar> Foo_Bar { get; set; }

  public JunctionModel()
    : base("name=DatabaseConnection") {
  }
}

This and my own experimentation seem to suggest that there is no other, more direct way without radically changing the model which might not be easily possible in some cases.

Direct access to EF junction table

1 Answers1