public static void AddOrderInTransactionScope()
        {
            Console.WriteLine("Adding two orders using TransactionScope...");
            using (NorthwindEntities northwindEntities = new NorthwindEntities())
            {
                Order firstOrder = new Order();
                firstOrder.CustomerID = "TOMSP";
                firstOrder.ShipName = "First Order Ship Name";
                Order secondOrder = new Order();
                secondOrder.CustomerID = "OCEAN";
                secondOrder.ShipName = "Second Order Ship Name";

                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
                {
                    northwindEntities.Orders.Add(firstOrder);
                    northwindEntities.SaveChanges();

                    northwindEntities.Orders.Add(secondOrder);
                    northwindEntities.SaveChanges();

                    scope.Complete();
                }

                firstOrder = northwindEntities.Orders.Find(firstOrder.OrderID);
                secondOrder = northwindEntities.Orders.Find(secondOrder.OrderID);
                Console.WriteLine("FirstOrder.ShipName={0}, SecondOrder.ShipName={1}", firstOrder.ShipName, secondOrder.ShipName);
            }
        }
        public static void CopyDatabaseSchema()
        {
            Console.WriteLine("Creating copy of Northwind database in the MS SQL server...(wait a moment)");
            IObjectContextAdapter initialDBSchema = new NorthwindEntities();
            string dbScript = initialDBSchema.ObjectContext.CreateDatabaseScript();

            DbContext newDBSchema = new DbContext("Server=localhost;Database=NorthwindTwin;Integrated Security=True;");
            newDBSchema.Database.CreateIfNotExists();
            (newDBSchema as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(dbScript);
        }
 public static void CalculateTotalIncomes()
 {
     Console.WriteLine("Calculates total incomes of Pavlova, Ltd. for year 1997...");
     DateTime startDate = new DateTime(1997, 1, 1);
     DateTime endDate = new DateTime(1998, 1, 1);
     string supplierName = "Pavlova, Ltd.";
     using (var northwindEntities = new NorthwindEntities())
     {
         var totalIncomes = northwindEntities.TotalIncomesForGivenSupplier(supplierName, startDate, endDate).ToList();
         Console.WriteLine("Incomes: {0:N2}", totalIncomes[0]);
     }
 }
        public static bool Delete(string customerID)
        {
            using (var ctx = new NorthwindEntities())
            {
                var theCustomer = ctx.Customers.Find(customerID);

                if (theCustomer == null) return false;

                ctx.Customers.Remove(theCustomer);
                if (ctx.SaveChanges() == 0) return false;
            }

            return true;
        }
        public static Customer Insert(string companyName,
            string contactName = null,
            string contactTitle = null,
            string address = null,
            string city = null,
            string region = null,
            string postalCode = null,
            string country = null,
            string phone = null,
            string fax = null)
        {
            var newCustomer = new Customer();
            newCustomer.CompanyName = companyName;
            newCustomer.ContactName = contactName;
            newCustomer.ContactTitle = contactTitle;
            newCustomer.Address = address;
            newCustomer.City = city;
            newCustomer.Region = region;
            newCustomer.PostalCode = postalCode;
            newCustomer.Country = country;
            newCustomer.Phone = phone;
            newCustomer.Fax = fax;

            string[] getWords = companyName.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
            newCustomer.CustomerID = getWords[0].Substring(0, 3) +
                ((getWords.Length > 1) ? getWords[1].Substring(0, 2) : getWords[0].Substring(0, 2));
            newCustomer.CustomerID = newCustomer.CustomerID.ToUpperInvariant();

            using (var ctx = new NorthwindEntities())
            {
                while (ctx.Customers.Find(newCustomer.CustomerID) != null && newCustomer.CustomerID != LastPossibleCustomerId)
                {
                    for (int i = newCustomer.CustomerID.Length-1; i >= 0 ; i--)
                    {
                        if (newCustomer.CustomerID[i] < 'Z')
                        {
                            char[] letters = newCustomer.CustomerID.ToCharArray();
                            letters[i]++;
                            newCustomer.CustomerID = string.Join("", letters);
                            break;
                        }
                    }
                }
                ctx.Customers.Add(newCustomer);
                ctx.SaveChanges();
            }

            return newCustomer;
        }
        public static void ConcurentChanges()
        {
            using (var ctx1 = new NorthwindEntities())
            {
                Employee employeeFromCtx1 = ctx1.Employees.First(e => e.EmployeeID == 1);
                employeeFromCtx1.FirstName = "Pesho";

                // Modify and Save the same employee in another context
                // i.e. mimicking concurrent access.
                using (var ctx2 = new NorthwindEntities())
                {
                    Employee employeeFromCtx2 = ctx2.Employees.First(e => e.EmployeeID == 1);
                    employeeFromCtx2.FirstName = "Gosho";
                    ctx2.SaveChanges();
                }
                // Console.ReadLine();
                // Save the changes... This should result in an Exception, but NOT due to EF 5 Optimistic Concurrency
                ctx1.SaveChanges();
            }
        }
        public static Customer Modify(string customerID,
            string companyName = null,
            string contactName = null,
            string contactTitle = null,
            string address = null,
            string city = null,
            string region = null,
            string postalCode = null,
            string country = null,
            string phone = null,
            string fax = null)
        {
            using (var ctx = new NorthwindEntities())
            {
                var theCustomer = ctx.Customers.Find(customerID);

                if (theCustomer == null) return null;
                if (companyName != null) theCustomer.CompanyName = companyName;
                if (contactName != null) theCustomer.ContactName = contactName;
                if (contactTitle != null) theCustomer.ContactTitle = contactTitle;
                if (address != null) theCustomer.Address = address;
                if (city != null) theCustomer.City = city;
                if (region != null) theCustomer.Region = region;
                if (postalCode != null) theCustomer.PostalCode = postalCode;
                if (country != null) theCustomer.Country = country;
                if (phone != null) theCustomer.Phone = phone;
                if (fax != null) theCustomer.Fax = fax;
                ctx.SaveChanges();
                return theCustomer;
            }
        }
        public static void CreateUserInGroup()
        {
            Console.WriteLine("Adding new user 'mancho' and attach it to the 'Admins' group...");
            string username = "******";

            using (var northwindEntities = new NorthwindEntities())
            {
                using (var scope = new System.Transactions.TransactionScope())
                {
                    User someUser = new User();
                    someUser.Username = username;
                    someUser.FullName = "Chicho Mancho";
                    someUser.Password = "******";

                    string adminGroup = "Admins";
                    var group = northwindEntities.Groups.Where(a => a.Name == adminGroup).ToList();
                    if (group.Count == 0)
                    {
                        Group newGroup = new Group();
                        newGroup.Name = adminGroup;

                        northwindEntities.Groups.Add(newGroup);
                        northwindEntities.SaveChanges();
                        group.Add(newGroup);
                    }

                    someUser.GroupId = group[0].GroupId;
                    northwindEntities.Users.Add(someUser);
                    northwindEntities.SaveChanges();
                    scope.Complete();
                }
            }
        }
        public static void ShowOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
        {
            var ctx = new NorthwindEntities();
            var query = from ordersInPeriod in ctx.Sales_by_Year(startDate, endDate)
                            join orders in ctx.Orders
                                on ordersInPeriod.OrderID equals orders.OrderID
                            where (orders.ShipRegion == region)
                            select orders;

            int count = 1;
            foreach (var item in query)
            {
                Console.WriteLine("Order: {0}, Date: {1:dd/MM}, To: {2}, Price: {3:N2}",
                    item.OrderID, item.ShippedDate, item.ShipName, item.Freight);
                count++;
            }

            Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
        }
 public static void ShowFirstEmployeeWithTerritories()
 {
     Console.WriteLine("Showing Employee[1] with its territories using EntitySet");
     using (var ctx = new NorthwindEntities())
     {
         var employee = ctx.Employees.First(e => e.EmployeeID == 1);
         Console.WriteLine("Employee[1] name=" + employee.FirstName + " " + employee.LastName);
         foreach (var item in employee.EntitySetTerritories)
         {
             Console.WriteLine("Territory: " + item.TerritoryDescription);
         }
     }
 }
        public static void ShowCustomersByOrderYearAndCountrySQL(int year, string country)
        {
            var sqlCommand = "SELECT * FROM Customers AS c" +
                             "  JOIN Orders AS o" +
                             "    ON c.CustomerID = o.CustomerID " +
                             "WHERE YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1}";
            var ctx = new NorthwindEntities();
            var query = ctx.Database.SqlQuery<Customer>(sqlCommand, year, country);

            int count = 1;
            foreach (var item in query)
            {
                Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName);
            }

            Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
        }
        public static void ShowCustomersByOrderYearAndCountry(int year, string country)
        {
            var ctx = new NorthwindEntities();
            var query = from customer in ctx.Customers
                        join order in ctx.Orders
                            on customer.CustomerID equals order.CustomerID
                        where (order.OrderDate.Value.Year == year && order.ShipCountry == country)
                        select customer;

            int count = 1;
            foreach (var item in query)
            {
                Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName);
            }

            Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
        }