About Me

My photo
a Dynamic and Energetic guy.....

Thursday, February 24, 2011

Complex LINQ query with outer joins and customized columns


using (VonQuestDBDataContext context = new VonQuestDBDataContext())
            {
                var lines =
                    from P in context.VQPlayers
                    join T in context.VQPlayerTippings on P.VQPlayerID equals T.VQPlayerID into T_t
                    from T in T_t.DefaultIfEmpty()
                    join MT in context.MatchTeams on T.MatchTeamID equals MT.MatchTeamID into T_mt
                    from MT in T_mt.DefaultIfEmpty()

                    join G in context.VQGames on P.VQGameID equals G.VQGameID into T_g
                    from G in T_g.DefaultIfEmpty()

                    join Te in context.Teams on MT.TeamID equals Te.TeamID into T_te
                    from Te in T_te.DefaultIfEmpty()

                    join U in context.Users on P.UserID equals U.UserID into T_u
                    from U in T_u.DefaultIfEmpty()

                    join Mate in context.Matches on MT.MatchID equals Mate.MatchID into T_Mate
                    from Mate in T_Mate.DefaultIfEmpty()

                    join R in context.Rounds on Mate.RoundID equals R.RoundID into T_r
                    from R in T_r.DefaultIfEmpty()

                    where (G.VQGameID == vqGameID && R.RoundID==roundID
                    orderby P.VQPlayerID

                    select new { User = U.UserName, MatchID = Mate.MatchID == null ? "" : Mate.MatchID.ToString(), ChosenTeam = Te.TeamName == null ? "(NONE)" : Te.TeamName, };

                dgvTippings.DataSource = lines;

                dgvTippings.Columns[0].HeaderText = "User Name";
                dgvTippings.Columns[1].HeaderText = "Match ID";
                dgvTippings.Columns[2].HeaderText = "Chosen Team";
            }

No comments:

My Masters