How to model POCO object from a multilanguage database with Entity Framework 4?

Question

I am building a new project from scratch. I created a db where I have consistently applied a db structure that I explain with a short self-explanatory example:

Table Item -> (Id, Name) -> Contains general information

Table ItemInfo -> (Item_Id, Language, Description) -> Contains the language dependent information.

Id and Item_Id are connected with a foreign key relationship.

My idea was to model it in a way that I would end up using only a single POCO object "Item" populated through Entity Framework. This object would contain only the public properties: Id, Name and Description. The language will be hidden to the code using this object, the object itself should have the responsibility to give the correct description depending on a global variable that contains the language.

I have tried a few ways to do this and always ended up having problems because Entity Framework wouldn't allow this scenario. I always had to retrieve info for ALL languages and not only the current one or use 2 different queries.

So at the end the solution I started to use was to let a T4 template create both Item and ItemInfo and then I manually added a code similar to 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;}
    }
}

With this code I added the additional properties from ItemInfo to Item and selected the correct language as per my requirements. Do you think this is a good solution? How would you solve this problem instead?

However, running sql profiler I can see that 2 different sql queries are used to populate the Item object, one that queries the Item table and another that queries the ItemInfo.

Can the same scenario be achieved with a single query that does a join between the 2 tables? (I am afraid of the long term performance hit and also this is how I would do it without an ORM).

Any suggestion will be welcome, I have many years of programming experience but I am a newbie with Entity Framework and ORMs in general.

Please help.

Answer 1

You're not showing how you fetch the Item objects, but generally I don't see a problem with fetching everything in one query. You've got several options.
You can do a projection (but not onto a mapped entity - in this example I project 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 comment discussion with @Craig Stuntz)

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

Or you can fetch 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 item object itself:

Item item = itemInfo.Item;

Answer 2

I would say it's a reasonable approach. Also, I wouldn't worry about performance issues with two simple selects. If it turns out to be a problem in the future, you might change it to a view, for instance.