linq to sql not counting all columns

Question

I have a table called Game, which holds 4 columns for player id's. And I also have a table called Player. When I try to count all the games in which a player has been in, I only get the players from the first column in the Game table.

player.Games.Count();

I have set up foreign keys for all the tables, and it's showing up correctly in the table designer. From the image below, you can see that it should count from blue1+2 and red1+2, but it only counts from Blue1...

I also tried to create a method to manually select all the games:

public int GetPlayerGames(int playerID)
{
    return (from game in db.Games
            where game.Blue1 == playerID
            || game.Blue2 == playerID
            || game.Red1 == playerID
            || game.Red2 == playerID
            select game).Count();
}

But when I use it inside a linq query

return from player in db.Players
       where GetPlayerGames(player.ID) > 0
       select player;

I get this error:
Method 'Int32 GetPlayerGames(Int32)' has no supported translation to SQL.

Answer 1

If you've set up four relationships they'll each have a different name, most likely Games, Games1, Games2 and Games3. By doing player.Games.Count() you're only counting the games associated with the first relationship, you need to add extra counts for the games associated by the other relationships:

var allGameCounts = player.Games.Count() + player.Games1.Count() + player.Games2.Count() + player.Games3.Count();

Answer 2

I actually ended up using the method above, but just casting it to a list first:

return from player in db.Players.ToList()
       where GetPlayerGames(player.ID) > 0
       select player;

Answer 3

If you want to do the filtering in sql without loading the whole database, you could do this...

from player in db.Players
where player.Games.Any()
  || player.Games1.Any()
  || player.Games2.Any()
  || player.Games3.Any()
select player