Navigation properties when datatypes does not match in Entity Framework
.net-4.5, c#, entity-framework, sql-server-2005
Solution
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.
Problem
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.