C# lambda Join multiple columns from one table to single column from another table

Question

I have the following data:

Games

Id | AwayTeamId | HomeTeamId |  SportId  |  foo  |  bar  |
------------------------------------------------------------
1  |      1     |     3      |     1     |  42   |  23   |
2  |      2     |     3      |     1     |  81   |  25   |
3  |      3     |     1      |     1     |  40   |  23   |

Teams

Id | TeamName  |
-----------------
1  |   Lions   |
2  |   Tigers  |
3  |   Bears   |

Sports

Id | SportName    |
--------------------
1  | Soccer       |
2  | Baseball     |
3  | Basketball   |

and I would like data like this for a Game

{
    "id": 2,
    "sport": {
        "id": 1,
        "name": "soccer"
    }
    "awayTeam": {
        "id": 2,
        "name": "Tigers"
    },
    "homeTeam": {
        "id": 3,
        "name": "Bears"
    },
    "foo": 81,
    "bar": 25
}

Here's where I'm stuck: I know how to do a join for Games and Teams but don't know how to

have Game.AwayTeamId and Game.HomeTeamId both join on the same column in the Teams table
include the additional Sport lookup

Obviously I'm looking to make a single database call if possible.

Here is my C# code that I know is incomplete:

            var gameLookup = Context.Teams
                .Join(
                    Context.Games.Where(b => b.Id == id),
                    team => team.Id,
                    game => game.AwayTeamId,
                    (team, game) => new
                    {
                        game.Id,
                        AwayTeam = team,
                        game.foo,
                        game.bar
                    }
                ).FirstOrDefault();

Thank you in advance.

Cetin Basoz · Accepted Answer · 2020-04-05 22:51:51Z

1

If you have relations set in your backend then it is simply:

var data = db.Games
             .Include(g => g.AwayTeam)
             .Include(g => g.HomeTeam)
             .Include(g => g.Sport)
             .Single(g => g.Id == 2);
var d = new {
        id = data.Id,
        sport = new {id = data.Sport.Id, name = data.Sport.SportName},
        awayTeam = new { id = data.AwayTeam.Id, name=data.AwayTeam.TeamName},
        homeTeam = new { id = data.HomeTeam.Id, name=data.HomeTeam.TeamName},
        foo = data.Foo,
        bar = data.Bar
    };
var result = Newtonsoft.Json.JsonConvert.SerializeObject(d, Newtonsoft.Json.Formatting.Indented);   

Console.WriteLine(result);

As an example with MS SQL Server:

create table Sports (Id int primary key, SportName varchar(20));
insert into Sports (Id, SportName) values
(1,'Soccer'),
(2,'Baseball'),
(3,'Basketball');

create table Teams(Id int primary key, TeamName varchar(20));
insert into Teams (Id, TeamName) values 
(1,'Lions'),
(2,'Tigers'),
(3,'Bears');

create table Games(Id int primary key, 
    AwayTeamId int references Teams(Id),
    HomeTeamId int references Teams(Id),
    SportId int references Sports(Id), 
    foo  int,  bar int);
insert into Games (Id, AwayTeamId, HomeTeamId, SportId, foo, bar) values
(1, 1, 3, 1 ,42 ,23),
(2, 2, 3, 1 ,81 ,25),
(3, 3, 1, 1 ,40 ,23);

Above code generates this output:

{
  "id": 2,
  "sport": {
    "id": 1,
    "name": "Soccer"
  },
  "awayTeam": {
    "id": 2,
    "name": "Tigers"
  },
  "homeTeam": {
    "id": 3,
    "name": "Bears"
  },
  "foo": 81,
  "bar": 25
}

edited Apr 5, 2020 at 22:51

answered Apr 5, 2020 at 21:33

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

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

4 Comments

Adam LeVasseur Over a year ago

My tables were previously not correctly setup, but I dropped and created them using basically the same SQL you included above. Now when I try code like yours I get "Lambda expression used inside Include is not valid."

Cetin Basoz Over a year ago

@AdamLeVasseur, it does work in EF. Or instead of lambdas, you can do the Includes with strings of those table names. If you are using MS SQL Server, you might as well use LinqToSQL instead, which doesn't require those includes.

Adam LeVasseur Over a year ago

I had an issue in my model definition that was causing the lambda error. Once I got that straightened out, your solution was exactly what I needed. Thank you for your help.

Cetin Basoz Over a year ago

@AdamLeVasseur, I see. I should have included the full code, including Fluent API for mappings (BTW, dotnet ef is a great helpful tool in that regard).

Deepak Mishra · Accepted Answer · 2020-04-06 19:29:41Z

1

Use .Include as mentioned in https://learn.microsoft.com/en-us/ef/ef6/querying/related-data

 var gameLookup = Context.Games.Include(t=>t.AwayTeam).Include(t=>t.HomeTeam)
                         .Include(t=>t.AwayTeam).Include(t=>t.Sports)....

edited Apr 6, 2020 at 19:29

answered Apr 5, 2020 at 21:13

Deepak Mishra

3,2531 gold badge29 silver badges37 bronze badges

Collectives™ on Stack Overflow

C# lambda Join multiple columns from one table to single column from another table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related