Skip to content

Instantly share code, notes, and snippets.

@tmeers
Created December 8, 2015 14:46
Show Gist options
  • Save tmeers/b7e126a2243ba603c9a2 to your computer and use it in GitHub Desktop.
Save tmeers/b7e126a2243ba603c9a2 to your computer and use it in GitHub Desktop.
The objective: write a query that selects all Leagues that a given user has access to.
The standard SQL query for a JOIN and a WHERE clause on the table you joined.
SELECT *
FROM League
OUTER JOIN LeagueUser ON LeagueUser.League_Id = League.Id
WHERE LeagueUser.User_Id = [passed id]
And the proposed EF query.
List<League> myLeagues = _db.Leagues.Join(_db.LeagueUsers,
league => league.Id,
leagueuser => leagueuser.League.Id,
(league, leagueuser) => leagueuser)
.Where(leagueuser => leagueuser.User.Id == user.Id)
.ToList();
The query correctly returns the leagueuser, which is not the corret return that I need. I need to return league, but am just not sure how.
public class League
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public int RegisteredByUserId { get; set; }
public bool ActiveStatus { get; set; }
public virtual ApplicationUser RegisterdByUser { get; set; }
}
public class LeagueUser
{
[Key]
public int Id { get; set; }
public virtual League League { get; set; }
public virtual ApplicationUser User { get; set; }
public UserStatus UserStatus { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment