How to model a POCO object from a multilingual database using Entity Framework 4?

Question

I am creating a new project from scratch. I created db, where I consistently applied the db structure, which I explain with a short explanatory example:

Table element → (Id, Name) → Contains general information

Table ItemInfo → (Item_Id, Language, Description) → Contains language-specific information.

Id and Item_Id are associated with a foreign key relationship.

My idea was to model it in such a way that in the end I would use only one POCO "Item" populated through the Entity Framework. This object will contain only public properties: Id, Name and Description. The language will be hidden from the code using this object, the object itself should be responsible for the correct description depending on the global variable containing this language.

I tried several ways to do this and always got problems because the Entity Framework did not allow this scenario. I always had to extract information for ALL languages, and not just for the current one or use 2 different queries.

So, at the end, the solution I started using was to allow the T4 template to create both Item and ItemInfo, and then I manually added code that looked like this:

public partial class Item { private ItemInfo _itemInfo = null; private ItemInfo itemInfo { get { if (_itemInfo == null) _itemInfo = ItemInfoes.Single(p => p.Language == GlobalContext.Language); return _itemInfo; } } public Description { get { return itemInfo.Description; } set { itemInfo.Description = value;} } } 

Using this code, I added additional properties from ItemInfo to Item and chose the correct language according to my requirements. Do you think this is a good solution? How would you solve this problem instead?

However, running the sql profiler, I see that two different sql queries are used to populate the Item object, one of which queries the Item table and the other that queries ItemInfo.

Is it possible to achieve the same scenario with a single query that performs a join between two tables? (I am afraid that this will lead to a long-term result, and I will also do it without ORM).

Any suggestion would be welcome, I have many years of programming experience, but I am new to Entity Framework and ORM in general.

Please, help.

Answer 1

You do not show how you retrieve Item objects, but usually I do not see a problem with retrieving in just one request. You have several options.
You can do a projection (but not on a mapped entity - in this example, I am projecting onto an anonymous object):

 context. Items. Select(item => new { Id = item.Id, Name = item.Name, Description = item. ItemInfo. Where(info => info.Language == YourGlobalLang). Select(info => info.Description). FirstOrDefault() }; 

(This has been edited to use FirstOrDefault instead of Single - see the discussion of comments using @Craig Stuntz)

This will return a list of all Items - you can add a Where clause to filter.

Or you can get it the other way around (starting with ItemInfo ):

 ItemInfo itemInfo = context. ItemInfoes. Include(info => info.Item). SingleOrDefault(info => info.Language == YourGlobalLang && info.Item.Id == itemIdToFetch); 

After that, you can access the element object itself:

 Item item = itemInfo.Item; 

Answer 2

I would say that this is a reasonable approach. Plus, I wouldn't worry about performance issues with two simple choices. If this becomes a problem in the future, you can, for example, change it to a view.

Answer 3

You can try adding a dynamic where clause. Or, as said, directly use linq for sql.

How to add a where clause to an ObjectSet Entity Framework

Add dynamic where clause to Entity Framework