Entity framework issue with join two objects

Question

I have following objects:

public class City
    {
        public int CityId { get; set; }
        public string Name { get; set; }
        public virtual ICollection<CityTranslation> CityTranslations { get; set; }
    }
public class CityTranslation
    {
        public int CityId { get; set; }
        public string LanguageCode { get; set; }
        public string Translation { get; set; }

        public virtual City City { get; set; }
    }

City table contain default language value in Name field other translations are in CityTranslation table.
I am having problem to get value by language from this.
I am trying to execute following:

public virtual IEnumerable<City> GetAllByLanguage(string language)
        {
            if (language != "en")
            {
                var data = from c in context.Cities
                           join t in context.CityTranslations
                           on c.CityId equals t.CityId
                           && t.LanguageCode == language
                           select c;
            }
            else 
            {
                return dbSet.ToList();
            }
        }

But I am gettings compile error.

Operator '&&' cannot be applied to operands of type 'int' and 'bool'

plus some casting errors.
What should I do to get value from translation table?

t.CityId is int, t.LanguageCode == language returns boolean. And && is logical AND operator. You can't use it wth int and bool. — Soner Gönül
– Soner Gönül, Commented Jul 6, 2014 at 15:45
Don't you mean where t.LanguageCode == language? You can also include the WHERE clause in lambda syntax in the reference to the table you're joining on e.g join t in context.CityTranslations.Where(x => x.LanguageCode == language) on c.CityId equals t.CityId — chridam
– chridam, Commented Jul 6, 2014 at 15:50
var data = from c in context.Cities join t in context.CityTranslations.Where(x => x.LanguageCode == language) on c.CityId equals t.CityId select c; but I am getting casting error. — 1110
– 1110, Commented Jul 6, 2014 at 17:50

Charles Mager · Accepted Answer · 2014-07-06 18:26:20Z

2

Others have spotted the issue, but you shouldn't even need the join - EF will handle it:

var data = from t in context.CityTranslations
           where t.LanguageCode == language
           select t.City;

answered Jul 6, 2014 at 18:26

Charles Mager

26.3k2 gold badges41 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Gert Arnold Over a year ago

Actually, I think this answer is better!

1110 Over a year ago

It doesn't return good translation. It still return City with translation from city table. Your answer return it from translation table.

Charles Mager Over a year ago

There is no 'City' in the translation table. Both methods achieve the same thing (and probably execute the same SQL) - you're doing a join and returning the 'City' part. Neither will get you a translation unless you include t.Translation in any result.

1110 Over a year ago

@CharlesMager I have to reopen this question as I accepted answer a little too fast. Result city from this query still return me City object with default language. Can you help me to get translated object?

Charles Mager Over a year ago

@1110 There is no City object with the translated name - what is it you actually want back? It sounds like you just want all CityTranslations that match the LanguageCode, which is a far simpler query (it's as I wrote, but select t rather than select t.City.

|

Gert Arnold · Accepted Answer · 2014-07-06 18:16:36Z

1

The second predicate should not be part of the join:

var data = from c in context.Cities
           join t in context.CityTranslations
               on c.CityId equals t.CityId
           where t.LanguageCode == language

answered Jul 6, 2014 at 18:16

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Comments

Hargrovm · Accepted Answer · 2014-07-06 17:28:14Z

1

For a join on multiple values you need to create composite key using anonymous types so your join statement needs to be something like

on new {t.CityId, t.languageCode} equals new {c.CityId, language}

answered Jul 6, 2014 at 17:28

Hargrovm

1,1989 silver badges11 bronze badges

Collectives™ on Stack Overflow

Entity framework issue with join two objects

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related