c# LINQ returning all joined results

Question

I want to return a result set from a join on two tables. So far I've joined the table but am having difficulty figuring out the last part (or possibly the whole thing needs rewired).

var x = _context.GameHasPlayers
    .Join(_context.Games, 
        ghp => ghp.GameId,
        g => g.Id,
        (gameHasPlayer, game) => /*What goes here*/ );

In SQL I'd simply write this:

select * from GameHasPlayer ghp
join Game g on g.Id = ghp.GameId

Let's say this should return 2 Games, each with 2 players, so a total of four rows.

It runs if I use in the last line of my c# query:

(gameHasPlayer, game) => new Game { });

But I just get four (as expected) new empty Game classes, so I'm struggling with how I can return the entire result set (i.e. all 4 rows of both tables joined) - do I need to create some new model/view that combines Game and GameHasPlayer first?

(e.g. in SQL I could create a view that implements the above SQL query and return a generic dataset or recreate the view c# side?)

----- edit -----

I'm also trying below:

var x = from ghp in _context.GameHasPlayers
    from g in _context.Games
    where g.Id == ghp.GameId
    select (g) // or select (ghp)

which gives me ACTUAL results but only for g or ghp - if I try select (g, ghp) it just doesn't play ball!

I don't understand it, but it seems to work (for my second c# query), giving me IQueryable anonymous - so I'm assuming in order to define it I have to... define it somewhere! — jamheadart, Nov 29 '19 at 07:06
Does this answer your question? [Join/Where with LINQ and Lambda](https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda) — xdtTransform, Nov 29 '19 at 07:11
Yeah actually. I found similar things but that one is nearly exactly same question and solution. — jamheadart, Nov 29 '19 at 07:19

score 0 · Answer 1 · answered Nov 29 '19 at 07:58

You have to understand that C# will need some idea of the structure of the instances your returning. Simply conjoining two otherwise unrelated classes isn't an option, sadly.

Fortunately, that's what the select statement provides you with: a way to create anonymous objects on the fly to store in your result set.
select (g) works because the type of g is known to the compiler. But if you want to join g and ghp, you need to construct a new type:

select(r => new {
   Id = g.Id,
   GameId = ghd.GameId,
   // ...
})

score 0 · Answer 2 · answered Nov 29 '19 at 08:28

You can use either of the below syntax:

_context.GameHasPlayers.Join(
    _context.Games,
    gameHasPlayer => GameHasPlayer.GameId,
    game => Game.Id,
    (gameHasPlayer, game) => new { GameHasPlayer = gameHasPlayer, Game = game }
)


from ghp in _context.GameHasPlayers
join g in _context.Games on g.Id equals ghp.GameId
select new { GameHasPlayer = ghp, Game = g }

c# LINQ returning all joined results

2 Answers2