public void LinqGroupBy() { var sql = "SELECT * FROM matchevents ORDER BY idMatch LIMIT 100"; using (var c = new PostgresqlDataLayer(mConfig).GetConn()) { var events = c.Query <MatchEvent>(sql); var eventsByMatch = events.GroupBy(e => e.IdMatch, e => e, (key, group) => new { IdMatch = key, Events = group }); } }
public void MultiMapping2() { var sql = "SELECT * FROM players p JOIN users u ON p.iduser = u.id"; using (var c = new PostgresqlDataLayer(mConfig).GetConn()) { var players = c.Query <Player, User, Player>(sql, (player, user) => { player.UserData = user; return(player); }, new { }, splitOn: "id").AsList(); Assert.IsTrue(players.Count > 0); } }
public static void RandomizePlayers() { if (mArgs.Count != 2) { Log("Usage: RandomizePlayers <idTournament>"); return; } if (!long.TryParse(mArgs[1], out long idTournament)) { throw new ArgumentException("idTournament"); } using (var c = new PostgresqlDataLayer(mDbConfig).GetConn()) { var t = c.BeginTransaction(); try { var players = c.Query <Player>("SELECT id, idUser FROM players p JOIN teamplayers tp ON tp.idPlayer = p.id JOIN tournamentteams tt ON tt.idteam = tp.idTeam WHERE tt.idTournament = @idTournament", new { idTournament }, t); var sql = new StringBuilder(); foreach (var player in players) { var name = NameGenerator.GetRandomName(); var surname = NameGenerator.GetRandomSurname(); sql.AppendFormat("UPDATE players SET name = '{0}', surname='{1}' WHERE id = {2};\r\n", name, surname, player.Id); sql.AppendFormat("UPDATE users SET name = '{0}' WHERE id = {1};\r\n", name + " " + surname, player.IdUser); } var query = sql.ToString(); c.Execute(query, t); t.Commit(); } catch { t.Rollback(); throw; } } }
public void MultiMapping3() { var sql = "SELECT p.*, email, mobile, avatarimgurl, status FROM players p JOIN teamplayers t ON t.idplayer = p.id JOIN users u ON p.iduser = u.id WHERE t.idteam = @idteam"; using (var c = new PostgresqlDataLayer(mConfig).GetConn()) { var players = c.Query <Player, User, TeamPlayer, Player>( sql, (player, user, teamPlayer) => { player.UserData = user; player.TeamData = teamPlayer; return(player); }, new { idteam = 9 }, splitOn: "email, status").AsList(); Assert.IsTrue(players.Count > 0); } }
public static void RandomizeTeams() { if (mArgs.Count != 2) { Log("Usage: RandomizeTeams <idTournament>"); return; } if (!long.TryParse(mArgs[1], out long idTournament)) { throw new ArgumentException("idTournament"); } using (var c = new PostgresqlDataLayer(mDbConfig).GetConn()) { var t = c.BeginTransaction(); try { var teams = c.Query <Team>("SELECT id, name FROM teams t JOIN tournamentteams tt ON tt.idTeam = t.id WHERE tt.idTournament = @idTournament", new { idTournament }, t); var sql = new StringBuilder(); foreach (var team in teams) { sql.AppendFormat("UPDATE teams SET name = '{0}', logoImgUrl = '' WHERE id = {1};\r\n", NameGenerator.GetTeamName(team.Id), team.Id); } var query = sql.ToString(); c.Execute(query, t); t.Commit(); } catch { t.Rollback(); throw; } } }