1

I am trying to create EDMX model for existing database, the DB has the following schema (not the best one, but this is what i have and i can not change it):

Table: ObjectTypes (ObjectTypeID "Identity", ObjectName)
Data: 1, Customer
Data: 2, Agent

Table: Customers (CustomerID "Identity", FName, LName, AgentID)
Data:  1, FTest, LTest, 1

Table: Agent (AgentID "Identity", AgentName, AgentCommission)
Data:  1, TestAgent, 10.0

Table: Contacts (ContactID "Identity", ObjectTypeID, ObjectID, ContactName)
Data:  1, 1, 1, CustomerTestContact
Data:  2, 1, 1, CustomerTestContact2
Data:  3, 2, 1, AgentTestContact
Data:  4, 2, 1, AgentTestContact2

Now I need to create Agents and Customer entities with navigation property to contacts.

For contacts of agents: Agents A JOIN Contacts C ON A.AgentID = C.objId AND C.objType = 2
For contacts of customers: Customers Cust A JOIN Contacts C ON Cust.CustomerID = C.objId AND C.objType = 1

How can i do this?

I tried to use inheritance (Customer and Agent inherit from ObjectTypes) and then to add the condition for ObjectTypeID per class (Customer = 1, Agent = 2), but i receive the error that it can be done because ObjectTypeID in ObjectTypes defined as Identity column.

I'm not sure that I'm going on the correct way, but i can not find another one.

Actually, I don't even know if it possible to achieve this with EF.

Thank you.

Alex Dn
  • 5,465
  • 7
  • 41
  • 79
  • This might help: http://stackoverflow.com/questions/5436731/composite-key-as-foreign-key – Pawel May 03 '12 at 17:02
  • @Pawel Thanks for that link, but I have a little bit another case...Contact table do has a "composite FK" of agent/customer, Col1 has actual value of PK, but the second Col of a "composite key" is PK from another table (not same table as Col1). If it would possible to put value of Col2 of the key (hardcoded/or other way) it would make things easier :) When i use agentObj.Contacts i need it to be like: ... Agents A JOIN Contacts C ON A.AgentID = C.objId AND C.objType = 2. For Customer it should be Customers Cust JOIN Contacts C ON Cust.CustomerID = C.objID AND C.objType = 1 – Alex Dn May 03 '12 at 18:02

0 Answers0