Entity Framework, TPT Inheritance and Multi-Language support

Question

Hello

I have this DB Structure:

Service

• Id int PK
• CategoryId FK
• SomeOtherFields

LocalizedService

• LocalizedServiceId PK
• LanguageId FK
• Name
• Description

As you can see, I have a base table called Service, and a LocalizedService table in which I'm writing multilingual data, such as name and description which should be translated in different languages. I have linked both tables in EF designer using TPT inheritance. Service is a base class of LocalizedService. The problem is that, TPT requires both tables to have primary key, which wouldn't be a problem if I was working on 1 language, but I should translate service rows across 3 different languages. So only LocalizedServiceId shouldn't be unique, but a combination of LocalizedServiceId + LanguageId should be unique because I can not insert more than one language row into LocalizedService. For example, take a look at LocalizedService rows bellow:

• 1, 1, SomeService, NULL <-- Inserted
• 2, 1, AnotherService, NULL <-- Inserted
• 2, 2, RussianTranslationService, NULL <-- can not insert because LocalizedServiceId with value 2 already exists in the table.

That's not a problem on DB side, I can create PK for multiple fields (for example, LocalizedServiceId and LanguageId both will be Primary Key), but in this case, EF designer throws errors saying: "Error 13 Error 75: Key usage is not valid. uPay.Data.Entities.LocalizedServiceRecord cannot define keys because one of its base classes (uPay.Data.Entities.ServiceRecord) defines keys. D:\Projects\uPay\uPay.Data.Entities\Entities.edmx 1531 9 uPay.Data.Entities". I really need a solution on this problem ASAP, your help will be very appreciated.

Answer 1

The error that you are getting from your EDM is coming from the fact that EF supports TPT inheritance between 2 entities on a one to one association which means a one to one relationship on database and that essentially requires that both tables has the very primary keys. Your scenario is essentially a one to many relationship where each service could have many localizations based on the ServiceID and LocalizationID.

There are 2 workarounds that has been suggested by Craig Stuntz on his post here by the way, the first one is to drill down into the EDM's SSDL part and remove the LanguageId from the Key section:

<EntityType Name="ServiceTypeLocale">
   <Key>
       <PropertyRef Name="LocalizedServiceId" />
       <!-- You have to comment this:-->
       <!--<PropertyRef Name="LanguageId" />-->
   </Key>
...

This is not recommended by the way since if you do this then the Update Model from Database wizard will try to "fix" your mapping every time you do an update.

Another workaround would be to create a view in your database and simulate a one to one relationship and then map the view instead of the ServiceLocalization table.