public _001_()
{
using (var db = new TrainintDBContext())
{
Console.WriteLine(db.Database.Connection.ConnectionString);
var item = db.Database.SqlQuery <Phone>("select * from Phones").ToList();
var param = new SqlParameter("@Id", 1);
item = db.Database.SqlQuery <Phone>("select * from Phones where Id = @Id", param).ToList();
param = new SqlParameter("@Id", "1");
item = db.Database.SqlQuery <Phone>("select * from GetPhones (@Id)", param).ToList();
param = new SqlParameter("@Number", "1");
var users = db.Database.SqlQuery <User>("GetUserByPhoneNumber @Number", param).ToList();
// вставка
//int numberOfRowInserted = db.Database.ExecuteSqlCommand("INSERT INTO Companies (Name) VALUES ('HTC')");
// обновление
//int numberOfRowUpdated = db.Database.ExecuteSqlCommand("UPDATE Companies SET Name='Nokia' WHERE Id=3");
// удаление
//int numberOfRowDeleted = db.Database.ExecuteSqlCommand("DELETE FROM Companies WHERE Id=3");
}
}
public _001_Eager()
{
using (var db = new TrainintDBContext())
{
var users = db.Users.Include(p => p.Documents).ToList();
}
}
public _001_Transaction()
{
using (var db = new TrainintDBContext())
{
db.Database.Log = Console.Write;
using (var bt = db.Database.BeginTransaction())
{
try
{
db.Database.ExecuteSqlCommand("UPDATE Users set Age = 1 where Id = 1");
db.Users.Add(new Models.User.User()
{
Name = "TEST"
});
db.SaveChanges();
bt.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
bt.Rollback();
}
}
}
}
void Init()
{
using (var db = new TrainintDBContext())
{
var user = db.Users.FirstOrDefault();
PrintConsole(user);
if (user != null)
{
var phoneOne = new Phone()
{
Number = "92311111"
};
var phoneTwo = new Phone()
{
Number = "92322222"
};
db.Phones.Add(phoneOne);
db.Phones.Add(phoneTwo);
user.Phones.Add(phoneOne);
user.Phones.Add(phoneTwo);
db.SaveChanges();
}
user = db.Users.FirstOrDefault(x => x.Id == user.Id);
PrintConsole(user);
}
}
public _001_Info()
{
using (var db = new TrainintDBContext())
{
var airplanes = db.Airplanes.ToList();
foreach (var item in airplanes)
{
Console.WriteLine($"{item.Name} {item.Year} {item.CountWheel} ");
}
}
}
public _002_Explicit()
{
using (var db = new TrainintDBContext())
{
var doc = db.UserDocument.FirstOrDefault();
db.Entry(doc).Reference("User").Load();
if (doc.User != null)
{
Console.WriteLine(doc.Id);
}
}
}
public _001_OneToOne()
{
using (var db = new TrainintDBContext())
{
var user = db.Users.FirstOrDefault();
if (user != null)
{
var userInfo = db.UserProfiles.Include(x => x.User.Documents).Where(x => x.User.Id == user.Id).FirstOrDefault();
Console.WriteLine(userInfo);
}
}
}
void Init()
{
using (var db = new TrainintDBContext())
{
db.UserDocument.Add(new Models.User.UserDocument()
{
UserId = 1,
Description = "85455688",
Type = UserDocument.TypeDocument.Passport
});
db.SaveChanges();
}
}
public _002_OneToMany()
{
this.Init();
using (var db = new TrainintDBContext())
{
var users = db.Users.Include("Documents").ToList();
PrintConsole(users);
var userFirst = users.FirstOrDefault();
userFirst.Name = "Test";
userFirst.Age = 100;
db.Entry(userFirst).State = EntityState.Modified;
db.SaveChanges();
PrintConsole(db.Users.Include("Documents").ToList());
}
}
public _003_Lazy()
{
using (var db = new TrainintDBContext())
{
foreach (var user in db.Users.ToList())
{
Console.WriteLine("User:"******"Docs:");
foreach (var document in user.Documents)
{
Console.WriteLine(document.Type.ToString());
}
}
foreach (var doc in db.UserDocument.ToList())
{
Console.WriteLine($"{doc.User.Name} {doc.Type.ToString()}");
}
}
}
/// <summary>
/// Когда контекст данных извлекает данные из базы данных, Entity Framework помещает извлеченные объекты в кэш и отслеживает изменения,
/// которые происходят с этими объектами вплоть до использования метода SaveChanges(),
/// который фиксирует все изменения в базе данных. Но нам не всегда необходимо отслеживать изменения. Например, нам надо просто вывести данные для просмотра.
/// </summary>
public _001_AsNoTracking()
{
using (var db = new TrainintDBContext())
{
var user = db.Users.AsNoTracking().FirstOrDefault();
//var user = db.Users.FirstOrDefault();
foreach (var item in db.Users.ToList())
{
Console.WriteLine("Name {1} Age {0}", item.Age, item.Name);
}
user.Age = 9999;
db.SaveChanges();
foreach (var item in db.Users.ToList())
{
Console.WriteLine("Name {1} Age {0}", item.Age, item.Name);
}
}
}
public _001_Linq()
{
using (var db = new TrainintDBContext())
{
var items = (from x in db.Users where x.Age > 3 select x).ToList();
// Методы расширения
items = db.Users.Where(x => x.Age > 3).ToList();
// Различие между Linq to Entities и Linq to Objects
items = db.Users.Where(x => x.Age > 3).ToList().Where(y => y.Id == 2).ToList();
var item = db.Users.FirstOrDefault(x => x.Id == 12);
var user = db.Users.Select(x => new
{
NameNew = x.Name,
AgeNew = x.Age
});
// OrderBy
var userOrderByDescending = from x in db.Users orderby x.Age descending select x;
var userOrderByDescendingExtension = db.Users.OrderByDescending(x => x.Age).ToList();
//
// Join
var userJoin = from x in db.Users
join y in db.UserDocument on x.Id equals y.UserId
select new
{
UserName = x.Name,
DocumentDescription = y.Description
};
//
// groupBy
var userGroupby = from x in db.Users group x by x.Age;
//var userGroupby = db.Users.GroupBy(x => x.Age);
foreach (var elements in userGroupby)
{
Console.WriteLine(elements.Key);
foreach (var value in elements)
{
Console.WriteLine(value);
}
}
//
// Union
var usersUnion = db.Users.Where(x => x.Age < 10)
.Union(db.Users.Where(x => x.Name.Contains("Саша")));
//
// Intersect
var usersIntersect = db.Users.Where(x => x.Age < 10)
.Intersect(db.Users.Where(x => x.Name.Contains("Саша")));
//
// Except
var user1 = db.Users.Where(x => x.Age < 10);
var user2 = db.Users.Where(x => x.Name.Contains("Саша"));
var userResult = user1.Except(user2);
//
}
}
