1

I am developing for an existing application which uses a SQL database that is used by two applications. One uses Entity Framework to connect to the database. The other uses LINQ-to-SQL. The SQL database is designed so that there are some tables showing many-to-many relationships between rows in two tables. Entity Framework seems not to import these tables, apparently because it has some object-oriented idea for how many-to-many relationships ought to be represented. So far, the Entity Framework application has not needed to know about those tables, but now it should. I don't know how that works, and I am concerned that even if I learn about Entity Framework's exciting new way to represent these relationships, that it won't cooperate nicely with the other application or the database which is designed to use the many-to-many table.

I.e., there is a table of Foos, and a table of Bars, and then a table with Foo and Bar Ids that lists which Foos relate to which Bars, and I don't want to stop using this relationship table, particularly because there is another LINQ application that heavily uses this relationship table.

Questions:

If I learn to use Entity Framework's many-to-many system, will it use and update the many-to-many table that the other application uses?

If not, what is a good way to get Entity Framework to not ignore the many-to-many relationship table, so I can write code to use the existing table?

Dronz
  • 1,970
  • 4
  • 27
  • 50

1 Answers1

4

Yes, Entity Framework will manage your many-to-many tables for you. Pure link tables (that only have two foreign key columns) in EF are represented as relationships as opposed to POCO objects. The way this is done is that you tell EF that there is a relationship between two of your objects and that table X is where this relationship is stored. As an example in EF 4.1. which is what I'm currently using this is done like so:

modelBuilder.Entity<Foo>() //Let me tell you about Foo...
            .HasMany(f => f.Bars)  //The property in the Foo class that links to Bar objects is Bars
            .WithMany(b => b.Foos)  //The property in the Bar class that links to Foo objects is Foos
            .Map(m => {
                m.MapLeftKey("FooID"); //Name of the foreign key column in the link table for Foo
                m.MapRightKey("BarID"); //Name of the foreign key column in the link table for Bar
                m.ToTable("FooBar"); //Name of the link table
            });

You can then make changes to this table by linking/unlinking objects in your code. You pretty much do something like

myFoo.Bars.Add(myBar); //Add a row to the link table
myFoo.Bars.Remove(myBar) //Delete a row from the link table

For a full implementation you should google your version of EF.

In case of link tables that contain extra columns (for example a creation date) they are represented by a POCO just like all the other tables. If you're really paranoid about EF's ability to manage your link tables you can force it to go this route by adding a unique id column to your pure link tables, but I'd definitely advice against it.

Think of it this way: EF has been around for a while now and has achieved a certain degree of maturity. Combine this with the fact that many-to-many relationships are not exactly rare in databases. Do you really think the designers of EF haven't dealt with your case?

Manos Dilaverakis
  • 5,849
  • 4
  • 31
  • 57
  • Thanks Manos! Now I need to brush up on calling modelBuilder in code, as opposed to having the VS wizard auto-magically create an object model based on the database. (That is, your code example looks great, but I will need to figure out where to put that and what steps will be needed to update the database in future since in the past it has been updated using the wizard.) Thanks for the explanation that I could add a column to force a POCO. – Dronz Mar 12 '13 at 17:57
  • As for what I was afraid the EF designers had (not) done, it seems to me like MS has a definite pattern of making new versions of things with new designs that omit old ways of doing things, often in a way that forces re-factoring or dealing with things that now can't be done (as in http://stackoverflow.com/questions/4719559/fix-display-garbage-left-by-wpf-dialog-window), so I was afraid it might not play well with the old table, or that it would be a headache getting it to work. But this looks pretty nice. Thanks again! – Dronz Mar 12 '13 at 18:01