Why is the EF database query not working correctly?

Question

ASP.NET

In this code, I create a new sheet of string type, count the number of goals in the match for the hosts and guests, but for some reason not known to me, the sums of the goals are always equal...

var calendar = _context.Matchs.Where(i => i.ChampionatId == id).Select(x => new List<string> {
                 x.Name,
                _context.StatisticPlayers.Where(i => i.MatchId == x.Id && i.CommandId == x.HomeId).Sum(n=>n.Goal).ToString(),
                _context.StatisticPlayers.Where(i => i.MatchId == x.Id && i.CommandId == x.GuestId).Sum(p=>p.Goal).ToString()
});

If I change this code a little, then everything works fine,

var calendar = _context.Matchs.Where(i => i.ChampionatId == id).Select(x => new List<string> {
                 x.Name,
                _context.StatisticPlayers.Where(i => i.MatchId == x.Id && i.CommandId.ToString().Contains(x.HomeId.ToString())).Sum(n=>n.Goal).ToString(),
                _context.StatisticPlayers.Where(i => i.MatchId == x.Id && i.CommandId.ToString().Contains(x.GuestId.ToString())).Sum(p=>p.Goal).ToString()
});

Is this an EF glitch, or is it my glitch?

Answer 1

Here's a little riddle for you: if for some reason this code throws an exception, will you know where it occurred?
The sequence of LINQ commands is, of course, wonderful, but in my opinion this code is completely unreadable and it is very, very difficult to understand what is wrong in it in my opinion.
If you look for problems, then apparently you have them in the element "i.CommandId == x.HomeId" and "i.CommandId == x.GuestId". What type are these fields? What is stored in them and in what form?
UPD: Can you explain what you should ultimately store inside the List<string> ? What do you want to form?

Answer 2

CommandId (int) - team id, in the "Player statistics" table, from here I pull out the goals scored by the players who should be in this team;
HomeId and GuestId (both int) - team id, hosts or guests respectively in the Matches table.
I hope I described it more or less clearly ...
Even more, we try this way - everything is fine:

var summ = _context.StatisticPlayers.Where(i => i.MatchId == 143 && i.CommandId == 9).Sum(n=>n.Goal).ToString();
var summ1 = _context.StatisticPlayers.Where(i => i.MatchId == 143 && i.CommandId == 1).Sum(n => n.Goal).ToString();

So bad:

var calendar = _context.Matchs.Where(i => i.ChampionatId == id).Select(f => new List<string> {
                _context.StatisticPlayers.Where(i => i.MatchId == 143 && i.CommandId == 9).Sum(n=>n.Goal).ToString(),
                _context.StatisticPlayers.Where(i => i.MatchId == 143 && i.CommandId == 1).Sum(n => n.Goal).ToString()
            });