0

In my database I have Customers, Logins and Tickets.

  • Tickets have Customers OR Logins on the CreatedByID
  • Customers DO NOT have Logins and viceversa
  • Designate between a Customer or Login using the CreatedByType column on Ticket

I'm unable to get entity framework to work with it and haven't had any luck reading about it. How do you make this function with Entity Framework?

tereško
  • 58,060
  • 25
  • 98
  • 150
Preston
  • 1,300
  • 1
  • 17
  • 32
  • I would recommend breaking them out into separate columns. What you currently have goes against the single responsibility principle. – Middas Jan 26 '13 at 08:05
  • You mean that you have problem with building the entities? – Bahman Jan 26 '13 at 09:34
  • 1
    SQL itself does not allow a FK field referencing two different PK fields, let alone entity framework. You'll need two FK fields. – Gert Arnold Jan 26 '13 at 09:53
  • see http://stackoverflow.com/questions/10396518/entity-framework-code-first-multi-column-foreign-key –  Jan 26 '13 at 13:07
  • @GertArnold I'm aware of that but I've worked in many many databases and data warehouses that use this all over the place it seems to be a common practice. I guess I'll have to use the two PK fields. I was hoping I could put a CASE WHEN in the relationship somewhere. Also, Tereško do you ever actually provide input on questions or just edit them? – Preston Jan 26 '13 at 19:46

1 Answers1

0

Your database model is invalid as you have ambiguous relation between the entities. You can either go with introducing additional column or by implementing inheritance on the database side. That means you could make the Customers entity to inherit from Logins, then you would only need one master id

Paweł Staniec
  • 3,151
  • 3
  • 29
  • 41
  • I've worked in many many databases and data warehouses that use this all over the place it seems to be a common practice. I guess I'll have to use the two PK fields. – Preston Jan 26 '13 at 21:46
  • Sure, I saw that one of the leasing products for identity governance has a setup where Accounts have Attributes, and Policies have attributes. Attribute table has "ObjectID" that stores the identifier of the master entity. Every time something goes wrong we have to poke around the database to figure out if this attribute is policy or account related. (there are no foreign keys setup, no constrains) Wouldn't it be better to have a shared master table, lets' call it "ObjectsWithAttributes". It would have a master ObjectID, and Accounts and Policies would derive from that (by another nav. prop) – Paweł Staniec Jan 27 '13 at 12:14
  • http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application good read about inheritance I was also thinking if you could maybe try to use CompositeKey http://stackoverflow.com/questions/5466374/composite-key-with-ef-4-1-code-first – Paweł Staniec Jan 27 '13 at 12:15