LINQ to Entity - Join between 3 tables 1 to many -> many to 1

Question

Hey all,

I'm a begginer with LINQ to Entity. I'm trying to select a record to update it with user entries later. Here's my schema

I'm trying to get a prediction record and I just can seem to make the query work. Is it a problem with the way my tables are built or something... I'm really lost here?

Here's my query

 var query = (from predict in _dataContext.Predictions
              from usr in predict.Users where usr.userId == pUserId
              from game in predict.GamesSchedule where game.gameId == pGameId 
              select predict);

Thanks for you help!

Answer 1

This should work:

var query = from predict in _dataContext.Predictions
   where predict.Users.UserId == pUserId && predict.GamesSchedule.gameId == pGameId
   select predict;

or, alternatively:

var query = _dataContext.Predictions.Where(p => p.Users.UserId == pUserId && p.GamesSchedule.gameId == pGameId);

The key is to not think about querying LINQ-to-Entities like you think of querying a database. Leverage the relationships between the entities to do your filtering.