using Entity Framework

1. create class library
2. manage NuGet -> entity framework
3. add new -> ADO.NET Entity Data Model -> Generate from Database -> new connection -> .(dot) /mean localhost/ => server name
4. create console application
5. add reference to class library and include it in using
6. manage NuGet -> installed packages -> entity framework(add for this project)
7. copy connection string from class library "App.config" and paste it into "App.config" in current project

//create instance of context
NorthwindEntities db = new NorthwindEntities();

//LINQ
foreach (var customer in db.Customers.Where(x => x.Country == "UK"))
{
    Console.WriteLine(customer.Address);
}

//execute sql query
var data = db.Customers.SqlQuery("SELECT * FROM dbo.CUSTOMERS").ToList();
foreach (var row in data)
{
    Console.WriteLine(row.PostalCode);
}

 //LINQ query
using (var context = new NorthwindEntities())
{
    var customers = from c in context.Customers
                    where c.City == "London"
                    select c;
}

//Extension Methods Query
using (var context = new NorthwindEntities())
{
    var customersPhones = context.Customers.Where(c => c.City == "London").Select(c => c.Phone).ToList();
    Console.WriteLine(customersPhones.Count());
}

//several where selection
using (var context = new NorthwindEntities())
{
    var customers = context.Customers.Where(c => c.Country == "UK");
    customers = customers.OrderBy(x => x.ContactName);
    customers = customers.Where(c => c.City == "London");
    //query is accumulating and executes when is attempt for foreach or tolist
}

//find by ID
var customer = context.Customers.Find(2);

//anonymous type
using (var context = new NorthwindEntities())
{
    var customersAddress = context.Customers.Where(c => c.City == "London").Select(x => new { x.Address });
    foreach (var item in customersAddress)
    {
        Console.WriteLine(item.Address);
    }
}

//prints sql query //better use trace profiler
Console.WriteLine(customers.ToString());

//add row to table
using (var context = new NorthwindEntities())
            {
                var region = new Region
                {
                    RegionDescription = "Sofia"
                };
                context.Regions.Add(region);
                context.SaveChanges();
            }

//cascade adding will add records to city table as well
using (var db = new Database())
            {
                Country spain = new Country();
                spain.population = "2 000 000";
                spain.Cities.Add(new City { name = "SomeTown" });
                spain.Cities.Add(new City { name = "OtherTown" });
                db.Contries.Add(spain);
                db.SaveChanges();
            }

//delete
using (var context = new NorthwindEntities())
            {
                //var region = context.Regions.Find(9);
                var region = context.Regions.First(x => x.RegionID == 9);
                context.Regions.Remove(region);
                context.SaveChanges();
            }
            //if there is foreign key constraints I will get exception
            //I can set options to cascade delete or set null

//update
using (var context = new NorthwindEntities())
{
    var region = context.Regions.Find(9);
    //var region = context.Regions.First(x => x.RegionID == 9);
    region.RegionDescription = "new desc";
    context.SaveChanges();
}


//extend and override methods (e.g. ToString()) by creating partial class with same name and override methods there

//parameterized native query
private static IEnumerable<string> SelectEmployeeNamesByCountryAndCity(
        string country, string city)
    {
        NorthwindEntities northwindEntities = new NorthwindEntities();
        string nativeSqlQuery =
            "SELECT FirstName + ' ' + LastName " +
            "FROM dbo.Employees " +
            "WHERE Country = {0} AND City = {1}";
        object[] parameters = { country, city };
        var employees = northwindEntities.Database.SqlQuery<string>(
            nativeSqlQuery, parameters);
        return employees;
    }

//join tables
var customerSupplier =
            from customer in northwindEntities.Customers
            join supplier in northwindEntities.Suppliers
            on customer.Country equals supplier.Country
            select new
            {
                CustomerName = customer.CompanyName,
                Supplier = supplier.CompanyName,
                Country = customer.Country
            };

//join with extension methods
 var customerSupplier =
    northwindEntities.Customers.Join(
    northwindEntities.Suppliers,
    customer=>customer.Country),
    (supplier=>supplier.Country),
    (customer,supplier)=> new
    {
    CustomerName = customer.CompanyName,
    Supplier = supplier.CompanyName,
    Country = customer.Country
});

//LINQ group
var groupedCustomers =
  from customer in northwindEntities.Customers
  group customer by Customer.Country;

//extension methods group
var groupedCustomers =
  northwindEntities.Customers.GroupBy(
    customer => customer.Country);

//attach (required for update)
Product product = northwindEntities.Products.First(p => p.ProductID == id);
//change product name
northwindEntities.Products.Attach(product); // This line is required!
product.ProductName = newName;
northwindEntities.SaveChanges();