Navigation properties when datatypes does not match in Entity Framework

Question

I am trying to map a legacy database into an Entity Framework Model. The database is very generic, and most data are stored in the tables "Object" and "Event". The columns are named things like "Date1", "Num11", "Text4". There are no explicit foreign keys defined 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 values of EventTypeID, the Num11 field references an Object. I can easily write a join between the 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 designer, I can create a separate Entities for each type of object, and rename the columns appropriately. The problems start when I try to create navigation-properties between the Entities, since the foreign key column type does not always match the primary key.

Neither SQL Server, nor Entity Framework, will allow me to create a foreign key reference between the columns.

How can I create navigation-properties between entities, when the FK an and PK datatypes does not match exactly? Something that enables me to include the related entity in a LINQ query, and hopefully be able to expose it in a OData service.

I can't make any changes to existing tables in the database, but if required, I could add views. Though I would need to be able to save the entities back to the database.

Answer 1

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

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

But you can't

• Add events to db.Objects.EventViewItems, because you can't write into the FK field.
• Add events to db.EventViewItems because there is no InsertFunction defined for a view. (Unless you hack the view into the model as a table).

So you'll have to include the original Event in the model too and use that to create/update/delete (CUD) individual Event objects.

Feels a but shaky, because you have to watch your steps not to run into runtime exceptions. On the other hand you'll have separate paths for reads and CUD. Call it CQRS and this design suddenly is cutting edge.

Answer 2

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