CODEWITHSUNDEEP
c#postgresqlentity-framework.net-coreentity-framework-core
Àlex
Àlex

Reputation: 704

Entity Framework : join two tables and where clause

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.

Upvotes: 31

Views: 83821

Answers (2)

Afshin Mobayen Khiabani
Afshin Mobayen Khiabani

Reputation: 1289

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();

Upvotes: 50

Serge
Serge

Reputation: 43959

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();

Upvotes: 6

Related Questions