0

We have an existing database which we'd like to connect to using code-first EF.

An issue we have come across is in the following scenario...

**TABLE 1** 
DebtorID Int PK (identity column)
Type Int PK FK ( value would be '1' - FK to Table 4)

**TABLE 2** 
CreditorID Int PK (identity column)
Type Int PK FK (value would be '2' - FK to Table 4)

**TABLE 3** 
TransactionID Int PK
ParentID Int FK
ParentType Int FK

-----------------------

**TABLE 4**
Type Int PK (identity column)
Description Varchar(max)

Table 3 has foreign key links to multiple tables (Table 1, Table 2, etc...). The key is unique by the type column. Constraints have been altered here to allow this scenario.

Q. How can we implement this schema using code-first? We have looked at Data Annotations, and also Fluent API, but can't come up with a solution other than to alter the database schema (which would be a big headache).

As it stands, we only seem to be able to link to a single foreign table.

The solution also has to account for other tables (not shown here) where the primary keys for Table 1 etc... are part of the primary key.

A real world view of our schema, and the issue with code-first.

Current schema: http://oi60.tinypic.com/2aeug4m.jpg

Code-First schema: http://oi62.tinypic.com/9schmc.jpg

Rob.Jaix
  • 13
  • 3
  • Since you already have a database, why not use Database First approach - http://msdn.microsoft.com/en-gb/data/jj206878.aspx ? It should generete working classes for you. – Michał Krzemiński Apr 04 '14 at 07:16
  • We would like to use the migrations part of code-first to handle the ongoing update to the database schema. We also like the concept of creating the classes first, and not having to work with a single edmx file across a large development team. Is code-first the wrong way to go? should we be using database-first, and is it easily to update deployed databases with new changes using database-first? – Rob.Jaix Apr 06 '14 at 23:14

1 Answers1

0

I have found the answer using the Fluent Api - was much easier than I thought.

Simply in the OnModelCreating() method, I needed to add multiple ForeignKey entries to cover each link.

modelBuilder.Entity(Of TABLE_3).HasRequired(Function(p) p.TABLE_1).WithMany().HasForeignKey(Function(p) New With {p.ParentID, p.ParentType})

modelBuilder.Entity(Of TABLE_3).HasRequired(Function(p) p.TABLE_2).WithMany().HasForeignKey(Function(p) New With {p.ParentID, p.ParentType})

This was in addition to any configuration I did using Data Annotation with regards to setting up primary keys and navigational properties (e.g. navigation properties for TABLE_1 and TABLE_2)

Rob.Jaix
  • 13
  • 3