linq_join_snippets_1.cs

// Query teams and players in those teams, a relationship table between
// Team << UserTeams >> User
// Team.Id || UserTeam.TeamId, UserTeam.UserId || User.Id

// Basic select
var query = from x in Teams
            select x;
            
// Inner Join (Give me teams that only have users in them)
var query = from t in Teams
              join ut in UserTeams
                on t.Id equals ut.TeamId
            select new { t, ut };
            
// Left Outer Join (Give me all teams that do or DO NOT have users. Will duplicate Team records in dataset if more than one user in a team)
var query = from t in Teams
              join ut in UserTeams
                on t.Id equals ut.TeamId into uts
                  from sub in uts.DefaultIfEmpty()
            select new { t, sub };

// Left Outer Join With Join Condition. Give me all ranks and if one user is associated to that rank. Rank cannot be deleted.
var query = from x in db.Ranks
            join ur in db.UserRanks
               on x.Id equals ur.RankId into urs
               from sub in urs.Where(f => f.Deleted == false && f.UserId == userId).DefaultIfEmpty()
            where
                        x.Deleted == false
                        && x.TeamId == teamId
            select x;
            
// Want to order by multiple columns?
var movies = from row in _db.Movies 
             orderby row.Category, row.Name
             select row;