CODEWITHSUNDEEP
entity-frameworklinqef-core-2.1
Longa
Longa

Reputation: 364

Include with additional param on join

I have many Nations and for each Nation i have a lot of Teams.

The GetTeam endpoint in team controller retrieve a single Team and its related Nation. Via LINQ the query is this:

Context.Teams.Include(t => t.Nation).First(t => t.Id.Equals(__id))

The resulting JSON is what I want:

{"team":{"name":"Team1","nation":{"id":1,"name":"Nation1"}}

Let's say now that the property "name", both in Team and Nation model is dropped and a new model relation is created, with Translation.

What I want now is to retrieve the same JSON, but with a different query based on culture. Gettin crazy understand how I can achieve it with include.

How can I compose this query in LINQ ?

select * 
from Teams inner join 
Translations TeamTr on Teams.id = TeamTr .id and TeamTr .culture = "IT" inner join 
Nations on Teams.nation_id = Nations.id inner join 
Translations NationTr on Nations .id = NationTr .id and NationTr .culture = "IT"

And compose the resulting data as JSON above?

Upvotes: 0

Views: 30

Answers (2)

Longa
Longa

Reputation: 364

Nice catch tdayi.

First of all I've created a new class, that will be the container of the linq result:

public class TeamDetailLinqDto
{
    public Team Team { get; set; }
    public Translation TeamTranslation { get; set; }
    public Nation Nation { get; set; }
    public Translation NationTranslation { get; set; }
}

and this is the linq query:

    public IQueryable<TeamDetailLinqDto> GetTeams()
    {
        var result = from team in Context.Teams
                     join teamTranslation in Context.Translations on
                         new { Id = team.Id, Locale = "IT" }
                         equals new { Id = teamTranslation.EntityId, Locale = teamTranslation.Locale }
                     join nation in Context.Nations on team.NationId equals nation.Id
                     join nationTranslation in Context.Translations on
                         new { Id = nation.Id, Locale = "IT" }
                         equals new { Id = nationTranslation.EntityId, Locale = nationTranslation.Locale }
                     select new TeamDetailLinqDto
                     {
                         Team = team,
                         TeamTranslation = teamTranslation,
                         Nation = nation,
                         NationTranslation = nationTranslation
                     };

        return result;
    }

Upvotes: 1

tdayi
tdayi

Reputation: 142

for example:

(from team in Context.Teams
join teamTr in Context.Translations on team.id equals teamTr.id
join nation in Context.Nations on team.nation_id equals nations.id
join nationTr in Context.Translations on nation.id equals nationTr.id
where teamTr.culture == "IT" && nationTr.culture == "IT"
select new 
{
   teamName = team.name,
   nationName = nation.name

}).ToList();

Upvotes: 1

Related Questions