Entity Framework : join two tables and where clause

Question

I'm having trouble working with Entity Framework and PostgreSQL, does anybody know how to join two tables and use the second table as a where clause?

The select I want to do in Entity Framework would be in SQL:

SELECT ai.id, ai.title, ai.description, ai.coverimageurl 
FROM app_information ai 
INNER JOIN app_languages al on al.id = ai.languageid
WHERE al.languagecode = 'es'

Currently I have this

var appInformationToReturn = context.app_information
     .Join(
          context.app_language,
          ai => ai.languageid,
          al => al.id,
          (ai, al) => new AppInformation()
          {
               id = ai.id,
               title = ai.title,
               description = ai.description,
               coverimageurl = ai.coverimageurl
          })
     .Where()
     .FirstOrDefault();

I don't know how to build the where clause.

.Where(x => x.languagecode="es"). Add property languagecode in AppInformation class. you can also use as new { x,y,z.....} in place of new AppInformation() — Vivek Nuna
– Vivek Nuna, Commented May 14, 2021 at 13:05
Honestly I don't see any reason for that join, since the principal table is not used for anything in the query. Not to mention there should be a navigation property to be used when needed instead of a manual join. — Ivan Stoev
– Ivan Stoev, Commented May 14, 2021 at 13:17
This post needs more details and clarity. Would be nice to see a full db context. — Serge
– Serge, Commented May 14, 2021 at 17:22
Btw, LINQ is not SQL. Not only Select is last, but other operators does not require to be in the same order as SQL. For instance, there could be multiple Where, and they could be at any point of the query chain. In your case, you could simply insert Where inside the Join , e.g. .Join(context.app_language.Where(al => al.languagecode = "es"), ... — Ivan Stoev
– Ivan Stoev, Commented May 14, 2021 at 19:45

Hille · Accepted Answer · 2023-04-17 13:53:33Z

Like this:

var appInformationToReturn = context.app_information
     .Join(
          context.app_language,
          ai => ai.languageid,
          al => al.id,
          (ai, al) => new
          {
               id = ai.id,
               title = ai.title,
               description = ai.description,
               coverimageurl = ai.coverimageurl,
               lang = al.languagecode
          })
     .Where(x => x.lang == "es")
     .Select(x => new AppInformation()
     {
          id = x.id,
          title = x.title,
          description = x.description,
          coverimageurl = x.coverimageurl
     })
     .FirstOrDefault();

Update (2023/03/26): Lately this answer got some attention so I decided to update and combine it with the first part of the answer provided by @Serge.

var item = (
     from ai in context.app_information
     join al in context.app_language on ai.languageid equals al.id 
     where (al.languagecode == "es")
     select new AppInformation 
     {
          id = ai.id,
          title = ai.title,
          description = ai.description,
          coverimageurl = ai.coverimageurl
     }).FirstOrDefault();

Svyatoslav Danyliv · Accepted Answer · 2021-05-14 13:46:27Z

6

try this:

var item = (
    from ai in context.app_information
    join al in context.app_language on ai.languageid equals al.id 
    where (al.languagecode == "es")
    select new AppInformation 
    {
        id = ai.id,
        title = ai.title,
        description = ai.description,
        coverimageurl = ai.coverimageurl
    }).FirstOrDefault();

or try shorter

var item = context.app_information
    .Where(ai => ai.app_language.languagecode == "es")
    .Select(ai => new AppInformation 
    {
        id = ai.id,
        title = ai.title,
        description = ai.description,
        coverimageurl = ai.coverimageurl
    })
    .FirstOrDefault();

edited May 14, 2021 at 13:46

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

answered May 14, 2021 at 13:23

Serge

44.4k4 gold badges26 silver badges53 bronze badges

3 Comments

Afshin Mobayen Khiabani Over a year ago

In the second code part. Where is the join? You assumed that there is a relation between tables. I know there should be relation but he asked with join.

Serge Over a year ago

Two are the same. The second just has a lambda syntax

Afshin Mobayen Khiabani Over a year ago

It's not the same. Simple way of saying this is you assumed that there is app_language variable in app_information Entity class. He asked using join which mean maybe there is no such a variable for other Entity in app_information.

Collectives™ on Stack Overflow

Entity Framework : join two tables and where clause

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related