static void UnionIntersectExcept() { using (SoccerContext db = new SoccerContext()) { var players = db.Players.Where(p => p.Id < 10) .Union(db.Players.Where(p => p.Team.Name.Contains("Basr"))); foreach (var p in players) { Console.WriteLine(p.Name); } Console.WriteLine("\n_______________________________\n"); var players2 = db.Players.Where(p => p.Id < 10) .Intersect(db.Players.Where(p => p.Team.Name.Contains("Basr"))); foreach (var p in players2) { Console.WriteLine(p.Name); } Console.WriteLine("\n_______________________________\n"); var except1 = db.Players.Where(p => p.Id < 10); var except2 = db.Players.Where(p => p.Team.Name.Contains("Basr")); var players3 = except1.Except(except2); foreach (var p in players3) { Console.WriteLine(p.Name); } } }
static void LINQExtensions() { using (SoccerContext db = new SoccerContext()) { var players = db.Players.Where(p => p.TeamId == 1); } }
static void AggregationOperatorsSQL() { using (SoccerContext db = new SoccerContext()) { int number1 = db.Players.Count(); int number2 = db.Players.Count(p => p.Team.Name.Contains("Basr")); Console.WriteLine("Count of players : {0}", number1); Console.WriteLine("Count of Barcelona players : {0}", number2); int minId = db.Players.Min(p => p.Id); int maxId = db.Players.Max(p => p.Id); double avgAge = db.Players.Where(p => p.Team.Name == "Basr") .Average(p => p.Age); Console.WriteLine("Min ID : {0}", minId); Console.WriteLine("Max ID : {0}", maxId); Console.WriteLine("Average age : {0}", avgAge); int ageSum = db.Players.Sum(p => p.Age); // суммарная цена всех моделей фирмы Samsung int ageSumBar = db.Players.Where(p => p.Team.Name.Contains("Basr")) .Sum(p => p.Age); Console.WriteLine("Age sum : {0}", ageSum); Console.WriteLine("Age sum of Barcelona : {0}", ageSumBar); } }
static void Group() { using (SoccerContext db = new SoccerContext()) { var groups = from p in db.Players group p by p.Team.Name; foreach (var g in groups) { Console.WriteLine(g.Key); foreach (var p in g) { Console.WriteLine("{0} - {1}", p.Name, p.Position); } Console.WriteLine(); } //var groups = db.Players.GroupBy(p => p.Team.Name); var groups2 = from p in db.Players group p by p.Team.Name into g select new { Name = g.Key, Count = g.Count() }; // альтернативный способ //var groups2 = db.Players.GroupBy(p=>p.Team.Name) // .Select(g => new { Name = g.Key, Count = g.Count()}); foreach (var c in groups2) { Console.WriteLine("Team: {0} Count of players: {1}", c.Name, c.Count); } } }
static void OrderBy() { using (SoccerContext db = new SoccerContext()) { var players = db.Players.OrderBy(p => p.Name); foreach (Player p in players) { Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Position); } players = db.Players.OrderByDescending(p => p.Name); Console.WriteLine("___________________________________"); foreach (Player p in players) { Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Position); } var PlayerCollection = db.Players.Select(p => new { Id = p.Id, Name = p.Name, Position = p.Position }).OrderBy(p => p.Name).ThenBy(p => p.Position); foreach (var p in PlayerCollection) { Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Position); } } }
static void LINQToEntityObject() { using (SoccerContext db = new SoccerContext()) { var players = db.Players.Where(p => p.Age < 30).ToList().Where(p => p.Id < 10); } }
static void FindFirst() { using (SoccerContext db = new SoccerContext()) { Player player = db.Players.Find(2); // выберем элемент с id=2 Console.WriteLine(player); player = db.Players.FirstOrDefault(p => p.Id == 3); if (player != null) { Console.WriteLine(player.Name); } } }
static void SimpleLINQ() { using (SoccerContext db = new SoccerContext()) { var players = from p in db.Players where p.TeamId == 1 select p; foreach (var player in players) { Console.WriteLine(player.Name); } } }
static void Delete() { using (SoccerContext db = new SoccerContext()) { db.Database.ExecuteSqlCommand("ALTER TABLE dbo.Players ADD CONSTRAINT Players_Teams FOREIGN KEY (TeamId) REFERENCES dbo.Teams (Id) ON DELETE SET NULL"); //удаление игрока Player pl_toDelete = db.Players.First(p => p.Name == "Роналду"); db.Players.Remove(pl_toDelete); // удаление команды Team t_toDelete = db.Teams.First(); db.Teams.Remove(t_toDelete); db.SaveChanges(); } }
static void Select() { using (SoccerContext db = new SoccerContext()) { var players = db.Players.Select(p => new { Name = p.Name, Position = p.Position, Age = p.Age, CommandName = p.Team.Name }); foreach (var p in players) { Console.WriteLine("{0} ({1}) - {2} ... {3} years", p.Name, p.CommandName, p.Position, p.Age); } } }
static void AsNoTracking() { using (SoccerContext db = new SoccerContext()) { Player firstPlayer = db.Players.FirstOrDefault(); firstPlayer.Name = "Test"; //db.SaveChanges(); List <Player> players = db.Players.ToList(); //Player firstPlayer = db.Players.AsNoTracking().FirstOrDefault(); //firstPlayer.Name = "Test"; ////db.SaveChanges(); //List<Player> players = db.Players.AsNoTracking().ToList(); } }
static void Add() { using (SoccerContext db = new SoccerContext()) { // создание и добавление моделей Team t1 = new Team { Name = "Барселона" }; Team t2 = new Team { Name = "Реал Мадрид" }; db.Teams.Add(t1); db.Teams.Add(t2); db.SaveChanges(); Player pl1 = new Player { Name = "Роналду", Age = 31, Position = "Нападающий", Team = t2 }; Player pl2 = new Player { Name = "Месси", Age = 28, Position = "Нападающий", Team = t1 }; Player pl3 = new Player { Name = "Хави", Age = 34, Position = "Полузащитник", Team = t1 }; db.Players.AddRange(new List <Player> { pl1, pl2, pl3 }); db.SaveChanges(); // вывод foreach (Player pl in db.Players.Include("Team")) { Console.WriteLine("{0} - {1}", pl.Name, pl.Team != null ? pl.Team.Name : ""); } Console.WriteLine(); foreach (Team t in db.Teams) { Console.WriteLine("Команда: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } } }
static void Add() { using (SoccerContext db = new SoccerContext()) { // создание и добавление моделей Team t1 = new Team { Name = "Basr" }; Team t2 = new Team { Name = "Real" }; db.Teams.Add(t1); db.Teams.Add(t2); db.SaveChanges(); Player pl1 = new Player { Name = "Ronal", Age = 31, Position = "Forward", Team = t2 }; Player pl2 = new Player { Name = "Messi", Age = 28, Position = "Forward", Team = t1 }; Player pl3 = new Player { Name = "Havi", Age = 34, Position = "Defence", Team = t1 }; db.Players.AddRange(new List <Player> { pl1, pl2, pl3 }); db.SaveChanges(); // вывод foreach (Player pl in db.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Team != null ? pl.Team.Name : ""); } Console.WriteLine(); foreach (Team t in db.Teams) { Console.WriteLine("Team: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } } }
static void Change() { using (SoccerContext db = new SoccerContext()) { Player player = db.Players.Where(p => p.Name == "Месси").FirstOrDefault <Player>(); player.Name = "Месси №1"; db.SaveChanges(); foreach (Team t in db.Teams.Include("Players")) { Console.WriteLine("Команда: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } } }
static void Change() { using (SoccerContext db = new SoccerContext()) { Player player = db.Players.Where(p => p.Name == "Месси").First <Player>(); Team team = db.Teams.Where(p => p.Name == "Барселона").First <Team>(); team.Players.Remove(player); db.SaveChanges(); foreach (Team t in db.Teams.Include("Players")) { Console.WriteLine("Команда: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } } }
static void Change() { using (SoccerContext db = new SoccerContext()) { //Player player = null; //foreach(Player p in db.Players) //{ // if(p.Name == "Messi") // { // player = p; // break; // } //} //if (player != null) //{ // player.Name = "Messi #1"; // db.SaveChanges(); //} //Player player = db.Players.Where(p => p.Name == "Messi").First<Player>(); //player.Name = "Messi №1"; //db.SaveChanges(); foreach (Team t in db.Teams.Include("Players")) { Console.WriteLine("Team: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } foreach (Team t in db.Teams) { Console.WriteLine("Team: {0}", t.Name); foreach (Player pl in t.Players) { Console.WriteLine("{0} - {1}", pl.Name, pl.Position); } Console.WriteLine(); } } }
static void Join() { using (SoccerContext db = new SoccerContext()) { //var players = from p in db.Players // join c in db.Teams on p.TeamId equals c.Id // select new { Name = p.Name, Team = c.Name, Position = p.Position }; var players = db.Players.Join(db.Teams, // вторая таблица для соединения x => x.TeamId, // свойство из первой таблицы для соединения c => c.Id, // свойство из второй таблицы для соединения (p, c) => new // новый результативный объект { Name = p.Name, Team = c.Name, Position = p.Position }); foreach (var p in players) { Console.WriteLine("{0} ({1}) - {2}", p.Name, p.Team, p.Position); } } }