Linq query with join returning wrong values / associations
I have a fantasy football site and on one page I allow the user to select up to 5 players to compare (for this example, I used Peyton Manning, Aaron Rogers and Andrew Luck). I run the following query to return all weekly data for these players:
var weekStats = from p in db.Players
join t in db.Teams on p.teamAbbre equals t.teamAbbre
join w in db.WeekStats on p.playerId equals w.playerId
where (p.playerId == player0 || p.playerId == player1 || p.playerId == player2 || p.playerId == player3 || p.playerId == player4)
&& w.season == 2014
select new PlayerIndexViewModel
{
player = p,
team = t,
weekStat = w
};
Everything looks great at first glance and you get 16 results for each player. But as I got closer, I saw that this only returns Peyton's stats for all three players! (Now most players haven't complained about getting Peyton's stats, but I don't think so ...)
I am doing double / triple check and everything is correct in db. The following sql works fine:
select p.*, t.*, w.* from Player p
join Team t on t.teamAbbre = p.teamAbbre
join WeekStat w on w.playerId = p.playerId
where
w.season = 2014
and (p.playerId = 2501863 or p.playerId = 2506363 or p.playerId = 2533031 or p.playerId = -1 or p.playerId = -1)
Note. In this example, we only selected 3 players, so -1 are just dummies.
After some searching, I read about "navigation" as an alternative to unions and tried the following but got the same results:
var weekStats2 = from w in db.WeekStats
where (w.playerId == player0 || w.playerId == player1 || w.playerId == player2 || w.playerId == player3 || w.playerId == player4)
&& w.season == 2014
select new PlayerIndexViewModel
{
player = w.player,
team = w.player.Team,
weekStat = w
};
I also added a line where I look at one stat to see what's going on:
var weekStats2 = from w in db.WeekStats
where (w.playerId == player0 || w.playerId == player1 || w.playerId == player2 || w.playerId == player3 || w.playerId == player4)
&& w.season == 2014
select new PlayerIndexViewModel
{
player = w.player,
team = w.player.Team,
weekStat = w,
statCat_5 = w.statCat_5
};
When I run this and step through the debugger, I can see statCat_5 is appropriate for every player, but the values inside weekStat still duplicate Peyton's statistics. It is also strange that the correct week for Rogers and Luck is missing (which means that each of the three players had a different week, so the associated week is missing from the data).
I guess my question is this: am I crazy? What's wrong with my LINQ that is causing this weirdness?
----------- Edit Here's the sql executed according to the debugger:
{SELECT
[Extent1].[season] AS [season],
[Extent2].[playerId] AS [playerId],
[Extent2].[playerName] AS [playerName],
[Extent2].[teamAbbre] AS [teamAbbre],
[Extent2].[position] AS [position],
[Extent3].[teamAbbre] AS [teamAbbre1],
[Extent3].[geoName] AS [geoName],
[Extent3].[teamName] AS [teamName],
[Extent3].[conference] AS [conference],
[Extent3].[division] AS [division],
[Extent3].[imageURL] AS [imageURL],
[Extent1].[weekNum] AS [weekNum],
[Extent1].[playerId] AS [playerId1],
[Extent1].[statCat_1] AS [statCat_1],
[Extent1].[statCat_2] AS [statCat_2],
[Extent1].[statCat_3] AS [statCat_3],
[Extent1].[statCat_4] AS [statCat_4],
[Extent1].[statCat_5] AS [statCat_5],
.....
[Extent1].[statCat_90] AS [statCat_90],
[Extent1].[statCat_91] AS [statCat_91]
FROM [dbo].[WeekStat] AS [Extent1]
INNER JOIN [dbo].[Player] AS [Extent2] ON [Extent1].[playerId] = [Extent2]. [playerId]
LEFT OUTER JOIN [dbo].[Team] AS [Extent3] ON [Extent2].[teamAbbre] = [Extent3].[teamAbbre]
WHERE (2014 = [Extent1].[season]) AND ([Extent1].[playerId] IN (@p__linq__0,@p__linq__1,@p__linq__2,@p__linq__3,@p__linq__4))}
When I paste this into the sql server it works correctly (individual players get their stats, not just copies of Peyton stats).
source to share
Since SQL returns expected results and C # code does not, it looks like the problem is not related to the JOIN itself.
It's more like a value versus link problem. A new PlayerIndexViewModel instance is created for each player, but they reference the Weekstats instance, and judging by the results, it might be the same instance for all rows.
To check if this is the problem, try assigning weekStat to a clone of w instead of w itself (assuming WeekStats is cloned).
I can't figure out why this issue might affect WeekStats more than players or teams (starting with the first snippet of code); the answer may have to do with how the classes were bound to the tables.
A question about a similar question has been answered here
source to share