public static void Main()
{
using (var db = new NorthwindEntities())
{
Console.WriteLine(db.Database.CreateIfNotExists());
}
}
private static void Main()
{
using (var db = new NorthwindEntities())
{
var companies = db.Customers.Select(c => c.CompanyName).ToList();
companies.ForEach(Console.WriteLine);
}
}
private static void DeleteCustomer()
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers.First();
db.Customers.Remove(customer);
db.SaveChanges();
}
}
private static void ModifyCustomerCompany()
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.Where(c => c.ContactName == "John Doe")
.FirstOrDefault();
customer.CompanyName = "Modified Company";
db.SaveChanges();
}
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
var products = db.Products.Select(p => p.ProductName).ToList();
foreach (var product in products)
{
Console.WriteLine(product);
}
}
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
var employee = db.Employees.Find(1);
foreach (var territory in employee.TerritoriesSet)
{
Console.WriteLine(
"Employee: {0} {1}, Territory description: {2}",
employee.FirstName,
employee.LastName,
territory.TerritoryDescription);
}
}
}
private static void FindAllCustomersWithOrdersToCanadaFrom1997()
{
using (var db = new NorthwindEntities())
{
var query = @"SELECT c.ContactName AS Customer, o.ShipCountry, o.OrderDate
FROM Customers c JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.ShipCountry = 'Canada' AND
o.OrderDate BETWEEN '1997-01-01' AND '1997-12-31'";
var customers = db.Database.SqlQuery<OrdersToCanadaIn1997>(query);
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
private static void Main()
{
Console.WriteLine("-- Establishing first connection to database Northwind...");
Thread.Sleep(1000);
using (var firstDb = new NorthwindEntities())
{
var firstCategory = firstDb.Categories.Find(4);
Console.WriteLine("Initial category description: {0}", firstCategory.Description);
Thread.Sleep(1000);
firstCategory.Description = "Cheese and many more";
Console.WriteLine("Category description after changing: {0}", firstCategory.Description);
Thread.Sleep(1000);
Console.WriteLine("-- Establishing second connection to database Northwind...");
Thread.Sleep(1000);
using (var secondDb = new NorthwindEntities())
{
var secondCategory = secondDb.Categories.Find(4);
Console.WriteLine("Initial category description: {0}", secondCategory.Description);
Thread.Sleep(1000);
secondCategory.Description = "Cheese and many, many more";
Console.WriteLine("Category description after changing: {0}", secondCategory.Description);
Thread.Sleep(1000);
firstDb.SaveChanges();
secondDb.SaveChanges();
Console.WriteLine("Category description after saving: {0}", secondCategory.Description);
Thread.Sleep(1000);
}
Console.WriteLine("-- Closing second connection to the database...");
Thread.Sleep(1000);
Console.WriteLine("Category description after saving: {0}", firstCategory.Description);
Thread.Sleep(1000);
}
Console.WriteLine("-- Closing first connection to the database...");
using (var db = new NorthwindEntities())
{
Console.WriteLine("Actual result: {0}", db.Categories.Find(4).Description);
}
}
private static void Main()
{
using (var db = new NorthwindEntities())
{
// You must change your App.config file in order to generate a clone of Northwind
// Summary:
// Creates a new database on the database server for the model defined in the backing
// context, but only if a database with the same name does not already exist on
// the server.
//
// Returns:
// True if the database did not exist and was created; false otherwise.
var flag = db.Database.CreateIfNotExists();
Console.WriteLine(flag);
}
}
private static void Main()
{
const string nativeSqlQuery =
"SELECT c.ContactName AS [Customer], o.OrderDate [Order Year], o.ShipCountry " +
"FROM Customers c " +
"JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'";
using (db = new NorthwindEntities())
{
var customers = db.Database.SqlQuery<View>(nativeSqlQuery);
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
private static void Main()
{
Console.WriteLine("-- Establishing connection to database Northwind...");
Thread.Sleep(1000);
using (db = new NorthwindEntities())
{
Console.WriteLine("Executing queries...");
Thread.Sleep(1000);
InsertNewCustomersToDb();
Thread.Sleep(1000);
ModifyNewInsertedCustomer();
Thread.Sleep(1000);
DeleteNewInsertedCustomer();
}
Console.WriteLine("-- Closing connection to the database...");
}
private static void FindSalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
using (var db = new NorthwindEntities())
{
var sales = db.Orders
.Where(o => o.OrderDate >= startDate)
.Where(o => o.OrderDate <= endDate)
.Where(o => o.ShipRegion == region)
.Select(o => new
{
OrderDate = o.OrderDate,
ShipRegion = o.ShipRegion
})
.ToList();
foreach (var sale in sales)
{
Console.WriteLine(sale);
}
}
}
private static string InsertNewCustomer()
{
using (var db = new NorthwindEntities())
{
var newCustomer = new Customer()
{
CustomerID = "ABC",
CompanyName = "Telerik",
ContactName = "John Doe",
ContactTitle = "Owner",
Address = "Al. Malinov Str. 31",
City = "Sofia",
PostalCode = "1784",
Country = "Bulgaria",
Phone = "(359) 888888888"
};
db.Customers.Add(newCustomer);
db.SaveChanges();
return newCustomer.CustomerID;
}
}
private static void FindAllCustomersWithOrdersToCanadaFrom1997()
{
using (var db = new NorthwindEntities())
{
var customers = db.Orders
.Where(o => o.ShipCountry == "Canada")
.Where(o => o.OrderDate >= new DateTime(1997, 1, 1))
.Where(o => o.OrderDate <= new DateTime(1997, 12, 31))
.Select(o => new
{
ContactName = o.Customer.ContactName,
OrderDate = o.OrderDate,
ShipCountry = o.ShipCountry
})
.ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
private static void Main()
{
using (db = new NorthwindEntities())
{
var customers = db.Customers
.Join(db.Orders,
(c => c.CustomerID),
(o => o.CustomerID),
(c, o) => new
{
CustomerName = c.ContactName,
OrderYear = o.OrderDate.Value.Year,
o.ShipCountry
})
.ToList()
.FindAll(c => c.OrderYear == 1997 && c.ShipCountry == "Canada")
.ToList();
customers.ForEach(Console.WriteLine);
// Select only distinct customers with specific conditions
//customers.Distinct().ToList().ForEach(Console.WriteLine);
}
}
