public UserManagementController(UserManager <ApplicationUser> userManager, RoleManager <IdentityRole> roleManager, SignInManager <ApplicationUser> signInManager, UefaDbContext dbContext)
{
_dbContext = dbContext;
_SignInManager = signInManager;
_userManager = userManager;
_roleManager = roleManager;
}
private static void Insert(UefaDbContext context)
{
var countries = new List <Country>();
for (int i = 0; i < 2; i++)
{
countries.Add(new Country
{
Name = $"France {i}"
});
}
context.Countries.AddRange(countries);
context.SaveChanges();
countries = new List <Country>();
for (int i = 0; i < 10; i++)
{
countries.Add(new Country
{
Name = $"France {i}"
});
}
context.Countries.AddRange(countries);
context.SaveChanges();
}
private static void GetPlayerAwardsImprove()
{
using (var context = new UefaDbContext())
{
context.Database.Migrate();
Console.Clear();
var players = context.Players.Include(p => p.PlayerFootballAwards).ThenInclude(a => a.FootballAward);
var list = players.GroupJoin(context.PlayerFootballAwards.Include(p => p.FootballAward), p => p.Id, fa => fa.PlayerId, (p, fpa) => new
{
p.Name,
p.Position,
awards = fpa.Where(f => f.PlayerId == p.Id).Select(f => f.FootballAward.Name).ToList()
//fpa.Join(context.FootballAwards, pf => pf.FootballAwardId, a => a.Id,
//(pf, a) => a.Name)
}).Take(3).ToList();
foreach (var player in list)
{
var awards = player.awards;
Console.WriteLine($"{player.Name} {player.Position} " +
$"{string.Join(" ,", awards ?? Enumerable.Empty<string>())}");
}
}
}
private static void GroupBy()
{
using (var context = new UefaDbContext())
{
//for (int i = 0; i < 1000; i++)
//{
// context.Teams.Add(new Team() { CountryId = 1, Name = "Dynamo" });
//}
// context.SaveChanges();
// foreach (var teams in context.Teams.Where(t => EF.Functions.Like(t.Name, "D%")).GroupBy(t =>t.Name).ToList())
// foreach (var teams in context.Teams.GroupBy(t =>t.Name).Select(t=>new {t.Key, Count = t.Count()}).ToList())
// https://github.com/aspnet/EntityFrameworkCore/issues/12560
foreach (var teams in context.Teams
.GroupBy(t => new { t.Name, t.Country.Id })
.Select(t => new { t.Key.Name, t.Key.Id })
.ToList()
.Select(t => new {
t.Name,
Country = context.Countries.Where(c => c.Id == t.Id).FirstOrDefault()
}).ToList())
{
//Console.WriteLine($"{teams.Key} {teams.Count()}");
Console.WriteLine($"{teams.Name} {teams.Country.Name}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
context.Database.Migrate();
foreach (var team in context.Teams.Include(t => t.Country))
{
Console.WriteLine($"{team.Name} {team.Country?.Name}");
}
}
using (var context = new UefaDbContext())
{
foreach (var team in context.Teams)
{
context.Entry(team)
.Reference <Country>(t => t.Country)
.Load();
context.Entry(team)
.Reference <Country>(t => t.Country)
.Query()
.Where(c => c.Name.Equals("Ukraine"));
context.Entry(team)
.Collection <Player>(t => t.Players)
.Query()
.Where(p => p.Name.Equals("Rakitskiy"));
Console.WriteLine($"{team.Name} {team.Country?.Name}");
}
}
//using (var context = new UefaDbContext())
//{
// foreach (var team in context.Teams.Select(t => new { t.Name, t.Country }))
// {
// Console.WriteLine($"{team.Name} {team.Country?.Name}");
// }
//}
//using (var context = new UefaDbContext())
//{
// context.ChangeTracker.LazyLoadingEnabled = true;
// foreach (var team in context.Teams)
// {
// Console.WriteLine($"{team.Name} {team.Country?.Name}");
// }
//}
//using (var context = new UefaDbContext())
//{
// foreach (var team in context.Teams)
// {
// Console.WriteLine($"{team.Name} {team.Players?.Count}");
// }
//}
}
private static void GetTeams(UefaDbContext context)
{
var countries = context.Countries.ToList();
foreach (var country in countries)
{
Console.WriteLine($"{country.Name} {country.isEurope}");
}
}
private static void GetTeamsByIgnoringFilter(UefaDbContext context)
{
var teams = context.Teams.Include(t => t.Country).IgnoreQueryFilters().ToList();
foreach (var team in teams)
{
Console.WriteLine($"{team.Name} {team.Country?.Name}");
}
}
private static void Update(UefaDbContext context)
{
foreach (var country in context.Countries.TagWith("Update Countries"))
{
country.Name = country.Name + " (Updated)";
}
context.SaveChanges();
}
private static void ShowPrivateProperty(UefaDbContext context)
{
var firstPlayer = context.Players.Find(1);
firstPlayer.Phone = "093-344-44-19";
context.SaveChanges();
foreach (var player in context.Players)
{
Console.WriteLine($"{player.Name} {player.Phone}");
}
}
private static void GroupByClientSide()
{
using (var context = new UefaDbContext())
{
foreach (var teams in context.Teams
.Where(t => EF.Functions.Like(t.Name, "D%")).TagWith("CLIENT SIDE")
.GroupBy(t => t.Name).ToList())
{
Console.WriteLine($"{teams.Key} {teams.Count()}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
context.Database.EnsureDeleted();
context.Database.Migrate();
foreach (var team in context.Teams)
{
Console.WriteLine($"{team.Name} {team.Country?.Name}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
Console.Clear();
GetTeams(context);
GetTeamsByIgnoringFilter(context);
}
}
private static void SelectByName()
{
using (var context = new UefaDbContext())
{
var fcShahtar = "Shahtar";
var teams = context.Teams.FromSql($"select * from Teams Where Name like {fcShahtar}").ToList();
foreach (var team in teams)
{
Console.WriteLine($"{team.Name} {team.Players?.Count}");
}
}
}
private static void GetTeamsUseDbFunc()
{
using (var context = new UefaDbContext())
{
var teams = context.Teams.FromSql("select * from GetTeams()")
.Include(t => t.Players)
.OrderBy(t => t.CountryId);
foreach (var team in teams)
{
Console.WriteLine($"{team.Name} {team.Players?.Count}");
}
}
}
private static void CompileAttention()
{
using (var context = new UefaDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var cq = EF.CompileQuery((UefaDbContext ctx) => ctx.Players.Where(p => StartsWith(p)));
var players = cq(context);
foreach (var player in players)
{
Console.WriteLine($"{player.Name} {player.Address?.City}");
}
}
}
private static void GroupByServerSide()
{
using (var context = new UefaDbContext())
{
foreach (var teams in context.Teams
.Where(t => EF.Functions.Like(t.Name, "D%")).TagWith("SERVER SIDE")
.GroupBy(t => t.Name)
.Select(t => new { Name = t.Key, Count = t.Count() })
.ToList())
{
Console.WriteLine($"{teams.Name} {teams.Count}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
context.Database.EnsureDeleted();
context.Database.Migrate();
context.Players.Add(new Player()
{
Name = "Vitek"
});
context.SaveChanges();
ShowPrivateProperty(context);
ShowShadowProperty(context);
}
}
private static void Update_2()
{
using (var context = new UefaDbContext())
{
Console.WriteLine("Update_2 EntityState.Modified");
var teamToUpdate = new Team()
{
Id = 1,
Country = context.Countries.Find(1),
Name = "Shahtar (Updated_2)"
};
context.Entry(teamToUpdate).State = EntityState.Modified;
context.SaveChanges();
}
}
private static void GetPlayerAwards()
{
using (var context = new UefaDbContext())
foreach (var player in context.Players.Include(p => p.PlayerFootballAwards).ThenInclude(a => a.FootballAward)
.Select(p => new
{
p.Name,
p.Position,
awards = p.PlayerFootballAwards.Select(pfa => pfa.FootballAward.Name).ToList()
}).Take(3).ToList())
{
var awards = player.awards;
Console.WriteLine($"{player.Name} {player.Position} " +
$"{string.Join(" ,", awards ?? Enumerable.Empty<string>())}");
}
}
private static void ShowShadowProperty(UefaDbContext context)
{
Player firstPlayer = context.Players.Find(1);
var lastName = context.Entry(firstPlayer).Property <string>("LastName").CurrentValue;
Console.WriteLine($"Get property value {lastName}");
context.Entry(firstPlayer).Property <string>("LastName").CurrentValue = "Ivanov";
context.SaveChanges();
foreach (var player in context.Players.Where(p => context.Entry(p).Property <string>("LastName").CurrentValue == "Ivanov"))
{
Console.WriteLine(
$"{player.Name} {player.Phone} {context.Entry(player).Property<string>("LastName").CurrentValue}");
}
}
// EF 6
//DbSet.SqlQuery()
//DbContext.Database.SqlQuery()
//DbContext.Database.ExecuteSqlCommand()
// EF core
//DbSet<TEntity>.FromSql()
//DbContext.Database.ExecuteSqlCommand()
//FromSql Limitations
//SQL queries must return entities of the same type as DbSet<T> type.e.g.the specified query cannot return the Course entities if FromSql is used after Students.Returning ad-hoc types from FromSql() method is in the backlog.
// The SQL query must return all the columns of the table. e.g.context.Students.FromSql("Select StudentId, LastName from Students).ToList() will throw an exception.
//The SQL query cannot include JOIN queries to get related data.Use Include method to load related entities after FromSql() method.
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
//context.Database.EnsureDeleted();
//context.Database.Migrate();
Console.Clear();
}
//SelectByName();
//SelectByNameInjetion();
//SelectByNameAndOrder();
SelectByNameLinq();
//GetTeamsUseDbFunc();
//GroupBy();
}
private static void SelectByNameAndOrder()
{
using (var context = new UefaDbContext())
{
var s = @"M%";
var players = context.Players
.FromSql($@"select * from Players Where Name like {s} ESCAPE '\'")
.OrderByDescending(p => p.Id)
.Include(p => p.Team)
.ToList();
foreach (var player in players)
{
Console.WriteLine($"{player.Id} {player.Name} {player.Team?.Name}");
}
}
}
private static void Update_3()
{
using (var context = new UefaDbContext())
{
Console.WriteLine("Update_3 Explicit property modified");
var teamToUpdate = new Team()
{
Id = 1,
Country = context.Countries.Find(1),
Name = "Shahtar (Updated_3)"
};
context.Attach(teamToUpdate);
context.Entry(teamToUpdate).Property(t => t.Name).IsModified = true;
context.SaveChanges();
}
}
private static void SelectByNameLinq()
{
using (var context = new UefaDbContext())
{
var s = @"%l%";
var players = context.Players
// .Where(p => EF.Functions.Like(p.Name, s))
.Where(p => p.Name.Contains(s))
.OrderByDescending(p => p.Id)
.Include(p => p.Team)
.ToList();
foreach (var player in players)
{
Console.WriteLine($"{player.Id} {player.Name} {player.Team?.Name}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
//context.Database.Migrate();
//foreach (var player in context.Players.Take(3)
// .Include(p => p.PlayerFootballAwards)
// .ThenInclude(fa => fa.FootballAward))
//{
// var awards = player.PlayerFootballAwards?.Select(p => p.FootballAward?.Name).ToList();
// Console.WriteLine($"{player.Name} {player.Position} " +
// $"{string.Join(" ,", awards ?? Enumerable.Empty<string>())}");
//}
// GetPlayerAwards();
GetPlayerAwardsImprove();
};
}
private static void Update_1()
{
using (var context = new UefaDbContext())
{
var team = context.Teams.Find(1);
var teamToUpdate = new Team()
{
Id = 1,
Country = context.Countries.Find(1),
Name = "Shahtar (Updated)"
};
// update all properties include country
context.Teams.Update(teamToUpdate);
context.SaveChanges();
}
}
private static void GroupByFixError()
{
using (var context = new UefaDbContext())
{
foreach (var teams in context.Teams
.GroupBy(t => new { t.Name, t.Country.Id }).TagWith("FIX ERROR")
.Select(t => new { t.Key.Name, t.Key.Id })
.ToList()
.Select(t => new
{
t.Name,
Country = context.Countries.Where(c => c.Id == t.Id).FirstOrDefault()
}).ToList())
{
Console.WriteLine($"{teams.Name} {teams.Country.Name}");
}
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
foreach (var player in context.Players)
{
Console.WriteLine($"{player.Name} {player.Position} {player.CardCode}");
}
foreach (var player in context.Players.Where(p => p.CardCode == "ewq-1234-5678"))
{
Console.WriteLine($"{player.Name} {player.Position} {player.CardCode}");
}
};
}
private static void Update_1_1()
{
using (var context = new UefaDbContext())
{
Console.WriteLine("Update");
var team = context.Teams.Find(1);
var teamToUpdate = new Team()
{
Id = 1,
Country = context.Countries.Find(1),
Name = "Shahtar (Updated)"
};
context.Entry(team).State = EntityState.Detached;
context.Teams.Update(teamToUpdate);
context.SaveChanges();
}
}
static void Main(string[] args)
{
using (var context = new UefaDbContext())
{
//context.Database.EnsureDeleted();
//context.Database.EnsureCreated();
//Console.Clear();
//Insert(context);
//Update(context);
}
SeedDataUpdateExamples();
//Update_1();
Update_1_1();
//Update_2();
//Update_3();
//Update_4();
//Update_5();
}