Entity Framework - Get 'fake' navigation property within one query

Question

I have a Product table that has no relation defined to the translation table. I added a Translation property to the Product POCO as [NotMapped].

**My Product POCO: **

public partial class Product
{
    public int ProductID { get; set; }

    public double Price { get; set; }

    [NotMapped]
    public virtual Translation Translation{ get; set; }

    /** Other properties **/
}

I also have a Translation table, and like the name says, it contains all the translations. Now, the right translation can be retrieved from the database by providing three parameters: LanguageID, TranslationOriginID and ValueID.

LanguageID: ID from the language that the user has defined.
TranslationOriginID: Simply said, 'What table contains the entity that I want the translation for?' In other words, this ID points to another table that contains all possible origins. An origin is a table/entity that can have a translation. E.g: The origin in this example is Product.
ValueID: This is the ID of the entity that I want a translation for.

My Translation POCO:

public partial class Translation
{
    public int TranslationID { get; set; }

    public byte LanguageID { get; set; }

    public short TranslationOriginID { get; set; }

    public int ValueID { get; set; }

    public string TranslationValue { get; set; }

   /** Other properties **/

    public virtual TranslationOrigin TranslationOrigin { get; set; }

    public virtual Language Language { get; set; }
}

When I want to retrieve all products with their Translation, I execute this code:

List<Product> products = context.Products.ToList();

 foreach (Product product in products)
 {
      product.Translation = context.Translations.FirstOrDefault(y => y.LanguageID == 1 && y.TranslationOriginID == 2 && y.ValueID == product.ProductID);
 }

Like you can see, I execute for every product in the list another query to get the translation.

My question: Is it possible to get all the products and their translation in one query? Or even that I automatically retrieve the right translation when I select a product?

I already tried an .Include() and a .Select(). It didn't work, maybe I did something wrong? I also tried this method, didn't work either.

Btw, I use Entity framework 5 with .NET 4 (so, Entity Framework 4.4).

Thanks in advance.

Greetings Loetn

Answer

With the example given by Ed Chapel, I came up with a solution.

return (from p in context.Products
            join t in context.Translations
            on new
            {
                Id = p.ProductID,
                langId = languageID,
                tOriginId = translationOriginID
            }
            equals new
            {
                Id = d.ValueID,
                langId = d.LanguageID,
                tOriginId = d.TranslationOriginID
            }
            into other
            from x in other.DefaultIfEmpty()
            select new
            {
                Product = p,
                Translation = x
            })
            .ToList().ConvertAll(x => new Product()
            {
                Code = x.Product.Code,
                Translation = x.Translation,
                /** Other properties **/
            });

score 2 · Answer 1 · answered Sep 26 '13 at 07:35

2

I don't like proper LINQ in most cases. However, join is one scenario where the LINQ is easy than the extensions methods:

from p in context.Products
join t in context.Translations
    on t.ValueID equals p.ValueID
       && t.LanguageID == 1
       && t.TranslationOriginID == 2
    into joinT
from x in joinT
select new { 
               Product = p,
               Translation = t,
           };

You then loop over the result setting x.Product.Translation = x.Translation.

answered Sep 26 '13 at 07:35

Ed Chapel

6,842
3
30
44

You were right. The only mistake you've made in your example is the use of multiple parameters in a join statement. You can see my edited post with the right solution. – Loetn Sep 26 '13 at 14:18

score 0 · Answer 2 · answered Sep 26 '13 at 07:45

First of all you should realize that your translations table is not structured like a dba would like it
You have a non enforced relationship because depending on the OriginId your valueId references a different table.
Because of this you cannot use lazy loading or includes from EF.
My best idea at this point would to manually join the table on an anonymous type(to include your originId). Afterwards you can iterate over the results to set the translation property

The result would look like this :

var data =   from p in context.Products
             join pt in context.Translations on new{p.Id,2} equals new {pt.ValueId, pt.OriginId} into trans
             select new {p, trans};
var result = data.ToList().Select( a =>
                {
                    a.p.Translations = a.trans;
                    return a.p;
                }).ToList();

score 0 · Accepted Answer · edited May 23 '17 at 12:20

With the example that Ed Chapel proposed as a solution, I came up with this.

 return (from p in context.Products
                join t in context.Translations
                on new
                {
                    Id = p.ProductID,
                    langId = languageID,
                    tOriginId = translationOriginID
                }
                equals new
                {
                    Id = d.ValueID,
                    langId = d.LanguageID,
                    tOriginId = d.TranslationOriginID
                }
                into other
                from x in other.DefaultIfEmpty()
                select new
                {
                    Product = p,
                    Translation = x
                })
                .ToList().ConvertAll(x => new Product()
                {
                    Code = x.Product.Code,
                    Translation = x.Translation,
                    /** Other properties **/
                });

Entity Framework - Get 'fake' navigation property within one query

3 Answers3