How to handle "secondary" keys in Entity Framework

Question

I'm evaluating using EF against an existing schema - the problem is that I can't work out how to set up associations between tables where the foreign key is NOT the primary key of the master table.

As an example, a foo may have many bars as defined like this (forgive the pseudocode):

table foo {
  int foo\_id pk,
  char(10) foo\_code,
  ...
}

table foobar {
  int bar\_id pk,
  char(10) bar\_foo\_code fk(foo.foo\_code),
  ...
}

What am I missing to be able to create the foo_foobar association, and hence a Bars navigation property on the Foo entity?

Answer 1

Linq to entities doesn't support Foreign Keys which don't point to the primary key of a table (see log message 3). Linq to entities will treat it as a normal field on a table. You won't be able to navigate to the entity it's linked to.

If you have an existing schema i'd recommend using the edm generator as this will create the EMDX file, code behind and even the view code (which can be very large). If your existing scheme is quite large Check out this post, which explains how to deal with large schemas.

When you run the EDM Generator you'll find out all the things that are not supported.

Looking at a previous EDMGen2.exe log we got the following types of messages back:

1. The data type 'sql_variant' is not
   supported, the column 'ColumnName' in table 'TableName' was excluded.
2. The table/view 'tableName' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view
3. The relationship 'RelationshipName' has columns that are not part of the key of the table on the primary side of the relationship which is not supported, the relationship was excluded.

We've also found that the Linq project actually crashed Visual Studio quite alot as the code file produced by EDM was well over 80 mb.