public void CustomerJoinTest()
{
using (var context = new NorthWindContext())
{
var customerData = (
from customer in context.Customers
join contactType in context.ContactType on customer.ContactTypeIdentifier
equals contactType.ContactTypeIdentifier
join contact in context.Contact on customer.ContactIdentifier equals contact.ContactIdentifier
join country in context.Countries on customer.CountryIdentfier equals country.Id
select new CustomerEntity
{
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactIdentifier = customer.ContactIdentifier,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
Address = customer.Street,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentfier,
CountyName = country.CountryName
}).ToList();
Assert.IsTrue(customerData.Count == 98);
}
}
public void GetAllCustomersICustomerUsingDatabase()
{
using (var context = new NorthWindContext())
{
var customers = context.Customers.ToList();
}
}
public void AddCustomerTest()
{
using (var context = new NorthWindContext(ContextInMemoryOptions()))
{
var contact = new Contact()
{
FirstName = "Karen",
LastName = "Payne"
};
context.Entry(contact).State = EntityState.Added;
var customer = new Customer()
{
CompanyName = "Karen's coffee shop",
ContactIdentifierNavigation = contact,
CountryIdentfier = 20
};
context.Entry(customer).State = EntityState.Added;
var saveChangesCount = context.SaveChanges();
Assert.IsTrue(saveChangesCount == 2,
"Expect one customer and one contact to be added.");
}
}
public async Task <Suppliers> SubmitSuppliers([Service] NorthWindContext dbContext, Suppliers submitSupplier)
{
var result = dbContext.Suppliers.Add(submitSupplier);
await dbContext.SaveChangesAsync();
return(result.Entity);
}
public void ApplyDiscount2(int pCustomerIdentifier, float pCurrentDiscount, float pNewDiscount)
{
using (var context = new NorthWindContext())
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.LazyLoadingEnabled = false;
var orderDetailsResults = (from order in context.Orders
join orderDetail in context.Order_Details on order.OrderID equals orderDetail.OrderID
where order.CustomerIdentifier == pCustomerIdentifier
select orderDetail).AsNoTracking().ToList();
orderDetailsResults = orderDetailsResults
.DistinctBy(details => details.OrderID)
.Where(details => details.Discount == pCurrentDiscount)
.ToList();
foreach (var item in orderDetailsResults)
{
item.Discount = pNewDiscount;
context.Entry(item).State = EntityState.Modified;
}
Console.WriteLine(context.SaveChanges());
}
}
public static Image GetPictureBytes(int categoryIdentifier)
{
using (var context = new NorthWindContext())
{
return(ImageHelpers.ByteArrayToImage(context.Categories.FirstOrDefault(cat => cat.CategoryId == categoryIdentifier).Picture));
}
}
void List(int categoryId)
{
using (NorthWindContext context = new NorthWindContext())
{
dgwProduct.DataSource = context.Products.Where(x => x.CategoryID == categoryId).ToList();
}
}
/// <summary>
/// Get an <see cref="Employee"/> by an existing primary key
/// </summary>
/// <param name="identifier"></param>
/// <returns>Employee for identifier</returns>
public static async Task <Employee> ReadEmployee(int identifier)
{
await using var context = new NorthWindContext();
return(await Task.Run(() =>
context.Employees.FirstOrDefaultAsync(emp => emp.EmployeeId == identifier)));
}
private static void Sıralama(NorthWindContext db)
{
//var result = db.Products.Count();
//var result = db.Products.Count(i=>i.UnitPrice>10 &&i.UnitPrice<30);
//var result = db.Products.Count(i => i.Discontinued);
//var result = db.Products.Count(i => i.Discontinued==true);
//var result = db.Products.Max(p=>p.UnitPrice);
//var result = db.Products.Where(p=>p.CategoryId==2).Min(p=>p.UnitPrice);
//var result = db.Products.Where(p => !p.Discontinued).Average(k => k.UnitPrice);//ortalama
//var result = db.Products.Where(p => !p.Discontinued).Sum(k => k.UnitPrice);//toplam
//var result = db.Products.OrderBy(k=>k.UnitPrice).ToList();//ürün fiyat artan şekilde
/*var result = db.Products.OrderBy(k=>k.UnitPrice).ToList();*///ürün fiyat artan şekilde
//var result = db.Products.OrderByDescending(k=>k.UnitPrice).ToList();//ürün fiyat azalan şekilde liste
//var result = db.Products.OrderByDescending(k => k.UnitPrice).FirstOrDefault();//ürün fiyat azalan şekilde tek
//Console.WriteLine(result.ProductName + " " + result.UnitPrice);
//foreach (var item in result)
//{
// Console.WriteLine(item.ProductName+" "+item.UnitPrice);
//}
//Console.WriteLine(result);
}
public int CountProducts()
{
using (var context = new NorthWindContext())
{
return(context.Products.AsNoTracking().Count());
}
}
/// <summary>
/// Get all Countries for DataGridView ComboBox column
/// </summary>
/// <returns></returns>
public static List <Countries> Countries()
{
using (var context = new NorthWindContext())
{
return(context.Countries.AsNoTracking().ToList());
}
}
/// <summary>
/// Get all Contact types for DataGridView ComboBox column
/// </summary>
/// <returns></returns>
public static List <ContactType> ContactTypes()
{
using (var context = new NorthWindContext())
{
return(context.ContactType.AsNoTracking().ToList());
}
}
public List <CustomerEntity> GetCustomersWhereSecondLetterIs(string pCondition)
{
using (var context = new NorthWindContext())
{
var customerData = (
from customer in context.Customers
join contactType in context.ContactType on customer.ContactTypeIdentifier equals contactType.ContactTypeIdentifier
join contact in context.Contact on customer.ContactIdentifier equals contact.ContactIdentifier
where Functions.Like(customer.CompanyName, pCondition)
select new CustomerEntity
{
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactIdentifier = customer.ContactIdentifier,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentfier,
CountyName = customer.CountryIdentfierNavigation.CountryName
}).ToList();
return(customerData);
}
}
/// <summary>
/// This is a perfect good query to return all products which will
/// throw an exception
///
/// The operation cannot be completed because the DbContext has been disposed.
///
/// This is because a "using" statement was used, once the return statement has
/// been reached the DbContext has been disposed as indicated in the exception
/// message, no different from any using statement's resources like when
/// using a connection object for SqlClientConnection or SqlClientCommand.
/// </summary>
/// <returns>
/// IQueryable of Products which since there is no explicit type the caller
/// must cast e.g.
///
/// var results = ops.GetProductsIQueryable1();
/// var resultsList = results.Cast<List<Product>>().ToList();
///
/// Alternately but since the DbContext has been disposed, same error.
/// var results = ops.GetProductsIQueryable1().Cast<List<Product>>().ToList();
///
/// </returns>
public IQueryable GetProductsIQueryable1()
{
using (var context = new NorthWindContext())
{
return(from product in context.Products select product);
}
}
public static Category InputCategory(NorthWindContext db)
{
Category category = new Category();
Console.WriteLine("Enter a new name for a product: ");
category.CategoryName = Console.ReadLine();
Console.WriteLine("What is the new description: ");
category.Description = Console.ReadLine();
ValidationContext context = new ValidationContext(category, null, null);
List <ValidationResult> results = new List <ValidationResult>();
var isValid = Validator.TryValidateObject(category, context, results, true);
if (isValid)
{
return(category);
}
else
{
foreach (var result in results)
{
logger.Error($"{result.MemberNames.First()} : {result.ErrorMessage}");
}
}
return(null);
}
public int CountProducts1()
{
using (var context = new NorthWindContext())
{
context.Configuration.AutoDetectChangesEnabled = false;
return(context.Products.AsNoTracking().Count());
}
}
public async Task <List <ContractDetailView> > GetDetailViewByMasterId(string masterId)
{
using (NorthWindContext <ContractDetailView> context = new NorthWindContext <ContractDetailView>())
{
return(await context.ContractDetailsView.FromSqlRaw("Exec [dbo].[Proc_GetContractDetail_ById] @ContractId={0}", masterId)
.ToListAsync());
}
}
/// <summary>
/// This version disposes the DbContext so if we needed to
/// update information we would need to create a new instance
/// of the DbContext and attach to, in this case Products.
/// </summary>
/// <returns></returns>
public List <Product> GetProductsAsList()
{
using (var context = new NorthWindContext())
{
context.Configuration.LazyLoadingEnabled = false;
return((from product in context.Products select product).ToList());
}
}
void List(string productName, int categoryID)
{
using (NorthWindContext context = new NorthWindContext())
{
//to avoid being affected by case sensitivity.
dgwProduct.DataSource = context.Products.Where(x => x.ProductName.ToLower().Contains(productName.ToLower()) && x.CategoryID == categoryID).ToList();
}
}
public void DeleteProduct(Product product)
{
using (var context = new NorthWindContext())
{
context.Entry(product).State = EntityState.Deleted;
context.SaveChanges();
}
}
public void Update(TEntity entity)
{
using (NorthWindContext context = new NorthWindContext())
{
var updatedCategory = context.Entry(entity);
updatedCategory.State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// Example for updating a disconnected entry indicating
/// this is a modification by setting State property to Modified.
/// </summary>
/// <param name="product"></param>
/// <param name="discontinued"></param>
public void SaveProduct(Product product, bool discontinued)
{
using (var context = new NorthWindContext())
{
product.Discontinued = discontinued;
context.Entry(product).State = EntityState.Modified;
context.SaveChanges();
}
}
/// <summary>
/// Read Employee current values from the database
/// </summary>
/// <param name="identifier">Employee key to return data for</param>
/// <returns>Employee for identifier</returns>
public static Employee OriginalEmployee(int identifier)
{
var context = new NorthWindContext();
/*
* AsNoTracking indicates that the ChangeTracker is not tracking the entity
*/
return(context.Employees.AsNoTracking().FirstOrDefault(employee => employee.EmployeeId == identifier));
}
void ConfigureCombos()
{
using (NorthWindContext context = new NorthWindContext())
{
cmbCategory.DataSource = context.Categories.ToList();
cmbCategory.DisplayMember = "CategoryName";
cmbCategory.ValueMember = "CategoryID";
}
}
// ENTITY FRAMEWORK BITS
private static int WriteEmployee_EntityFramework(Employee employee)
{
NorthWindContext northWindContext = new NorthWindContext();
northWindContext.Employees.Add(employee);
var results = northWindContext.SaveChanges();
return(results);
}
private static void Uygulama(NorthWindContext db)
{
////tüm müşteri kayıtlarını getiriniz.(Customers)---------------------------
//var customers = db.Customers.ToList();
//foreach (var customer in customers)
//{
// Console.WriteLine(customer.ContactName);
//}
////tüm müsterilerin sadece customerId ve ContactName kolonlarını getirin----------------------------
//var customers = db.Customers.Select(c => new { c.CustomerId, c.ContactName }).ToList();
//foreach (var item in customers)
//{
// Console.WriteLine(item.CustomerId+" "+item.ContactName);
//}
////almanyada yasayan müsterilerin adlarını getirin-------
//var customers = db.Customers.Select(c => new { c.ContactName, c.Country }).Where(c => c.Country == "Germany").ToList();
//foreach (var item in customers)
//{
// Console.WriteLine(item.Country + " " + item.ContactName);
//}
////Diego roel isimli müsteri nerede yasamaktadır------------------------------
//var customers = db.Customers.Where(p => p.ContactName == "Diego Roel").FirstOrDefault();
//Console.WriteLine(customers.ContactName+" "+customers.CompanyName);
////stokta olmayan ürünler hangileridir--------------------------------
//var products = db.Products.Where(i => i.UnitsInStock == 0).ToList();
//var products = db.Products.Select(i=> new { i.ProductName,i.UnitsInStock}).Where(i => i.UnitsInStock == 0).ToList();
//foreach (var item in products)
//{
// Console.WriteLine(item.ProductName+" "+item.UnitsInStock);
//}
//Tüm calısanların ad ve soyadını tek kolon halinde getirin-----------------------------
//var employes = db.Employees.Select(i => new { FullName = i.FirstName + " " + i.LastName }).ToList();
//foreach (var item in employes)
//{
// Console.WriteLine(item.FullName);
//}
////ürünler tablosundaki ilk 5 kaydı alınız.(TAKE)-------------------------------------
//var products = db.Products.Take(5).ToList();
//foreach (var item in products)
//{
// Console.WriteLine(item.ProductId+" "+item.ProductName);
//}
////ürünler tablosundaki ikinci 5 kaydı alınız.(SKİP)--------------------------------
//var products = db.Products.Skip(5).Take(5).ToList();
//foreach (var item in products)
//{
// Console.WriteLine(item.ProductId+" "+item.ProductName);
//}
}
/// <summary>
/// Get all
/// </summary>
/// <returns></returns>
/// created by: ntkien 11.05.2020
public virtual async Task <List <T> > GetAll()
{
List <T> results = null;
using (NorthWindContext <T> context = new NorthWindContext <T>())
{
results = await context.ListBase.ToListAsync();
}
return(results);
}
private static void Delete1(NorthWindContext db)
{
var p = db.Products.Find(97);
if (p != null)
{
db.Products.Remove(p);
db.SaveChanges();
}
}
void List()
{
using (NorthWindContext context = new NorthWindContext())
{
dgwProduct.DataSource = context.Products.ToList();
dgwProduct.Columns[0].Visible = false;
dgwProduct.Columns[1].Visible = false;
dgwProduct.Columns[2].HeaderText = "Ürün Adı";
}
}
public Product GetByID(int id)
{
using (NorthWindContext context = new NorthWindContext())
{
return(context.Products.FirstOrDefault(x => x.ProductId == id));
// return context.Products.SingleOrDefault(x => x.ID == id);
//tabloda eğer gönderilen id varsa veri çek yoksa default null gönder firstordefault
//tabloda eğer gönderilen id birden fazla ise sadece bir veriyi çek yoksa default null gönder singleordefault
}
}
