public static int RemoveTown(SoftuniContext dbContext)
{
//first set the addresses of the employees living in seattle to null
var employeesLivingInSeattle = dbContext.Employees.Where(employee => employee.Address.Town.Name.Equals("Seattle"));
foreach (var employeeInSeattle in employeesLivingInSeattle)
{
employeeInSeattle.AddressId = null;
}
dbContext.SaveChanges();
//Remove the addresses which are located in seattle
var addressesInSeattle = dbContext.Addresses.Where(address => address.TownId == 4);
int addressDeleteCount = addressesInSeattle.Count();
foreach (var addressToRemove in addressesInSeattle)
{
dbContext.Remove(addressToRemove);
}
dbContext.SaveChanges();
var townToRemove = dbContext.Towns.FirstOrDefault(town => town.TownId == 4);
dbContext.Remove(townToRemove);
dbContext.SaveChanges();
return(addressDeleteCount);
}
public static string IncreaseSalaries(SoftuniContext dbContext)
{
var employeesWhoseSalariesWillIncrease = dbContext.Employees
.Where(empl => empl.Department.Name.Equals("Engineering") ||
empl.Department.Name.Equals("Tool Design") ||
empl.Department.Name.Equals("Marketing") ||
empl.Department.Name.Equals("Information Services"))
.OrderByDescending(empl => empl.FirstName)
.ThenBy(empl => empl.LastName);
foreach (var item in employeesWhoseSalariesWillIncrease)
{
item.Salary = IncreaseSalaryBy12Procent(item.Salary);
}
dbContext.SaveChanges();
StringBuilder sb = new StringBuilder();
foreach (var item in employeesWhoseSalariesWillIncrease)
{
sb.Append(item.FirstName + " " + item.MiddleName + " " + item.LastName + " Salary: " + item.Salary + Environment.NewLine);
}
return(sb.ToString());
}
//Assigning new address and getting the first 10 employees and their addresses
public static string AddNewAddressToEmployee(SoftuniContext dbContext)
{
int townID = 4;
string addressToAdd = "Vitoshka 15";
//adding the address to table Addresses
dbContext.Addresses.Add(new Address()
{
AddressText = addressToAdd, TownId = townID
});
dbContext.SaveChanges();
//getting the address's id we just added
int?addressID = dbContext.Addresses
.FirstOrDefault(address => address.AddressText.Equals(addressToAdd) && address.TownId == townID).AddressId;
//get Employee with last name Nakov
var employeeWithLastNameNakov = dbContext.Employees.FirstOrDefault(employee => employee.LastName.Equals("Nakov"));
employeeWithLastNameNakov.AddressId = addressID;
dbContext.SaveChanges();
StringBuilder sb = new StringBuilder();
var employeesAddressesFirst10 = dbContext.Employees.OrderByDescending(employee => employee.AddressId)
.Take(10).Select(employee => new
{
Text = employee.Address.AddressText
}
).ToArray();
foreach (var employee in employeesAddressesFirst10)
{
sb.Append(employee.Text + Environment.NewLine);
}
return(sb.ToString());
}
//06.Adding a New Address and Updating Employee
public static void AddingNewAddressUpdatingEmployee()
{
SoftuniContext contex = new SoftuniContext();
using (contex)
{
var address = contex.Set <Address>();
address.Add(new Address {
AddressText = "Vitoshka 15", TownID = 4
});
contex.SaveChanges();
var result = contex.Employees.SingleOrDefault(e => e.LastName == "Nakov");
if (result != null)
{
result.AddressID = 292;
contex.SaveChanges();
}
var Findaddress = contex.Employees.OrderByDescending(e => e.AddressID).Select(e => e.Address.AddressText).Take(10);
foreach (var add in Findaddress)
{
Console.WriteLine(add);
}
}
}
static void Main(string[] args)
{
var optionBuilder = new DbContextOptionsBuilder <SoftuniContext>();
// optionBuilder.UseSqlServer("path to database or use default");
optionBuilder.UseLoggerFactory(factory);
using var db = new SoftuniContext(optionBuilder.Options);
//if table doesnt exist create it
db.Database.EnsureCreated();
var firstEmployee = db.Employees.FirstOrDefault();
firstEmployee.FirstName = "Delyan";
db.SaveChanges();
}
//15.First Letter
public static void DeleteProjectById()//The way to delete a project
{
SoftuniContext context = new SoftuniContext();
using (context)
{
var project = context.Projects.Find(2);
foreach (Employee emp in project.Employees)//First we delete all projects from all employees
{
emp.Projects.Remove(project);
}
context.Projects.Remove(project);
context.SaveChanges();
var projects = context.Projects.Select(p => p.Name).Take(10);
Console.WriteLine(project);
}
}
private static void Insert()
{
SoftuniContext context = new SoftuniContext();
Random rnd = new Random();
var employees = context.Employees.Take(1000).ToList();
for (int i = 0; i < 1000; i++)
{
context.Employees.Add(new Employee()
{
JobTitle = employees[rnd.Next(1, employees.Count)].JobTitle,
Department = employees[rnd.Next(1, employees.Count)].Department,
FirstName = employees[rnd.Next(1, employees.Count)].FirstName,
LastName = employees[rnd.Next(1, employees.Count)].LastName,
HireDate = employees[rnd.Next(1, employees.Count)].HireDate,
Salary = employees[rnd.Next(1, employees.Count)].Salary
});
}
context.SaveChanges();
Console.WriteLine("Inserted");
}
static void Main(string[] args)
{
using (var context = new SoftuniContext())
{
var project = context.Projects.Find(2);
var employeesProjectsToRemove = project.Employees.Where(e => e.Projects == project);
foreach (var employeeProject in employeesProjectsToRemove)
{
employeeProject.Projects.Remove(project);
}
context.Projects.Remove(project);
context.SaveChanges();
var projects = context.Projects.Take(10);
foreach (var proj in projects)
{
Console.WriteLine($"{proj.Name}");
}
}
}
static void Main(string[] args)
{
using (var context = new SoftuniContext())
{
var address = new Address()
{
AddressText = "Vitoshka 15",
TownID = 4
};
Employee employee = null;
employee = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
employee.Address = address;
//context.Employees.FirstOrDefault(e => e.LastName == "Nakov").Address = address;
context.SaveChanges();
var employees = context.Employees.OrderByDescending(e => e.AddressID).Take(10);
foreach (var emp in employees)
{
Console.WriteLine($"{emp.Address.AddressText}");
}
}
}