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.

Why don't you use linq-to-sql rather than using the Entity Framework? — dnatoli, Aug 17 '11 at 06:53
linq to sql is old and will be obselete linq to entities will be supported for a long time coming and is actively being improved and developed avoid linq to sql if possible — Chris McGrath, Aug 18 '11 at 05:27

Yakimych · Accepted Answer · 2011-08-19T15:28:15.020

6

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;

edited Aug 19 '11 at 15:28

answered Aug 17 '11 at 13:30

Yakimych

17,612
7
52
69

`Single()` will fail at runtime in L2E. You have to use `SingleOrDefault()` instead. – Craig Stuntz Aug 19 '11 at 14:10
@Craig Stuntz - it depends on how the OP will be handling error cases, so I wouldn't plainly state that `Single` is wrong while `SingleOrDefault` is right. This is also out of scope of the question, but you have a point here of course. In the first example, I'd probably want an exception to be thrown, since having none or more than one translation in the same language is an invalid state. In the second example, however, I completely agree - `SingleOrDefault` is more preferable since we're trying to fetch an item with a specific `Id` and it might not exist. Edited the second example, thanks! – Yakimych Aug 19 '11 at 14:37
I didn't say that `Single()` was "wrong." I said it *won't execute* in a L2E query. Ever. Even if there's only one language. Have you tried it? – Craig Stuntz Aug 19 '11 at 14:41
@Craig Stuntz - Yes, I have used `Single()` in L2E queries, and it works fine (at least in EF4). Is there any specific scenario it wouldn't work in you're concerned about? – Yakimych Aug 19 '11 at 14:53
1

@Craig Stuntz - Ok, I just did some more testing and you were partially right. You won't be able to use `SingleOrDefault` either, however. Here is the exception one gets: "`The methods 'Single' and 'SingleOrDefault' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.`". So it will indeed fail in the first example. Edited to use `FirstOrDefault` instead, and thanks again. – Yakimych Aug 19 '11 at 15:21
Thank you, I like both of the methods suggested! – Durden81 Aug 22 '11 at 11:59

score 2 · Answer 2 · answered Aug 17 '11 at 10:42

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.

answered Aug 17 '11 at 10:42

Jacek Gorgoń

3,206
1
26
43

+1 for suggesting to not worry about performance prematurely. Optimize the code if it is indeed found to be a bottleneck. Most likely, some UI code somewhere will be the real culprit if your app is slow. – Thorin Aug 18 '11 at 01:57
It is a bottleneck. This is a rewrite of an existing application and the current bottleneck is indeed the database. There are 15 tables with this structure so a total of 30. Also the number of languages will start small 3-4 but could increase even to 10. Using 2 queries for each time an item is retrieved will effectively slow down every page load where items from different tables are needed and it will be very difficult to change this later on. – Durden81 Aug 18 '11 at 12:12
If this is indeed the case, then I would suggest Yakimych's 1st method. You might want to use a typed wrapper class (``WrappedItem``) instead of the anonymous type and just reference it everywhere instead of ``Item`` itself. – Jacek Gorgoń Aug 18 '11 at 14:44
1

There will be a real performance problem here, because you'll need N+1 DB queries to populate any list. That's (really) bad, and fixing it isn't premature optimization. – Craig Stuntz Aug 19 '11 at 14:12

score 0 · Answer 3 · edited May 23 '17 at 11:59

0

You may try to add the where clause dynamically. Or as it was said use linq to sql directly.

How to append a where clause to an Entity Framework ObjectSet

Add the where clause dynamically in Entity Framework

edited May 23 '17 at 11:59

Community

1
1

answered Aug 17 '11 at 12:40

Vince

1,036
1
10
17

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

3 Answers3