Ejemplo n.º 1
0
        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);
                }
            }
        }
Ejemplo n.º 2
0
 static void LINQExtensions()
 {
     using (SoccerContext db = new SoccerContext())
     {
         var players = db.Players.Where(p => p.TeamId == 1);
     }
 }
Ejemplo n.º 3
0
        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);
            }
        }
Ejemplo n.º 4
0
        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);
                }
            }
        }
Ejemplo n.º 5
0
        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);
                }
            }
        }
Ejemplo n.º 6
0
 static void LINQToEntityObject()
 {
     using (SoccerContext db = new SoccerContext())
     {
         var players = db.Players.Where(p => p.Age < 30).ToList().Where(p => p.Id < 10);
     }
 }
Ejemplo n.º 7
0
 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);
         }
     }
 }
Ejemplo n.º 8
0
 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);
         }
     }
 }
Ejemplo n.º 9
0
 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();
     }
 }
Ejemplo n.º 10
0
 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);
         }
     }
 }
Ejemplo n.º 11
0
        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();
            }
        }
Ejemplo n.º 12
0
        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();
                }
            }
        }
Ejemplo n.º 13
0
        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();
                }
            }
        }
Ejemplo n.º 14
0
 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();
         }
     }
 }
Ejemplo n.º 15
0
 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();
         }
     }
 }
Ejemplo n.º 16
0
 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();
         }
     }
 }
Ejemplo n.º 17
0
        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);
                }
            }
        }