Navigation properties when data types do not match in the Entity Framework

Question

I am trying to map an obsolete database to an Entity Framework model. The database is very general, and most of the data is stored in the Object and Event tables. Columns are called things like Date1, Num11, Text4. There are no explicit foreign keys in the database.

Here is a subset of the two tables:

CREATE TABLE [Object] ( [ObjectId] int not null identity(1,1) primary key, [ObjectTypeId] int, [Name] varchar(100) ); CREATE TABLE [Event] ( [EventId] int not null identity(1,1) primary key, [EventTypeId] int, [Subject] text, [Body] text, [Date1] datetime, [Num11] decimal(18,2) ); 

For some EventTypeID values, the Num11 field refers to Object . I can easily write a join between tables:

 SELECT ev.[EventId], ev.[Subject], ev.[Body], ev.[Date1] AS [CreatedDate], p.[ObjectId] AS [PersonId], p.[Name] AS [PersonName] FROM [Event] ev LEFT JOIN [Object] p ON p.ObjectId = ev.Num11 WHERE ev.[EventTypeId] = 7 AND ev.[Date1] > '2013-04-07' 

In the Entity Framework constructor, I can create separate objects for each type of object and rename the columns accordingly. Problems arise when I try to create navigation properties between objects, because the column type of the foreign key does not always match the primary key.

Neither SQL Server nor Entity Framework will allow me to create a link to foreign keys between columns.

How to create navigation properties between objects when the data types FK an and PK do not match exactly? Something that allows me to include a related object in a LINQ query and hopefully be able to open it in an OData service.

I cannot make any changes to existing tables in the database, but if necessary, I could add views. Although I would need to save the objects back to the database.

Answer 1

Not a nice design, but there are some options. This is what you can do:

• Create a 1: 1 view on Event with an extra column that converts the decimal to an integer. The property should be marked as computed.
• Use the property in the FK association with Object , so Object has an EventViewItems navigation EventViewItems (if you like), which is displayed in the view. (You must add the association manually in the edmx designer and configure the foreign key field ).
• Read objects and events in a single linq expression, e.g. db.Objects.Include(o => o.EventViewItems)

But you can’t

• Add events to db.Objects.EventViewItems because you cannot write to the FK field.
• Add events to db.EventViewItems because InsertFunction does not exist for the view. (If you hack the view into a model like a table ).

So, you will need to include the original Event in the model and use this to create separate Event objects to create / update / delete (CUD).

It feels, but shaky, because you must ensure that your actions are not performed during the execution of exceptions. On the other hand, you will have separate paths for reading and CUD. Call it CQRS, and this design is suddenly in front of me.

Answer 2

You can try this. Change the type of Num11 in the model to integer. In the efconfiguration Event, set databasetyp from num11 to int with xx.HasColumnType ("int").