Entity Framework and LINQ(Lambda)

Entity Framework and LINQ(Lambda)

using EsSample.Data;
using EsSample.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
namespace EsSample
{
    class Program
    {
        static void Main(string[] args)
        {

            //GetAll();
            //GetByName("George");
            //AgeLessThen();
            //GetAllAdresses();
            //GetOneCity("City");
            //GetAddressOrderby();
            //GetAddressOrderbyDescending();
            // UpdateRecord();
            //UpdateCustomer();
            //GetCustomerId(2);
            //AddRecord();
            //DeleteRecords();
            //ObjectGraph();
            //GetAddress(1);
            //DoSearch("Julie", "Rahman");
            //GetCustomers();
            //-----------------------------------------------------------------------------------
            //var cast = getCustomerWithSp(5);
            //Console.WriteLine(cast.FirstName + " " + cast.LastName + " " + cast.DateOfBirth + " " + cast.CustomerId);
            //Console.ReadLine();
            //-----------------------------------------------------------------------------------
            //List<Customer> cast = Callsp();
            //foreach (var c in cast)
            //{
            //    Console.WriteLine(c.FirstName + " " + c.FirstName);
            //}
            //Console.ReadLine();
            //-----------------------------------------------------------------------------------
            //List<Address> adrss = CallAddresses();
            //foreach (var a in adrss)
            //{
            //    Console.WriteLine(a.City);
            //}
            //Console.ReadLine();
            //-----------------------------------------------------------------------------------
            //var cast = getCustomerWithSp(2);
            //Console.WriteLine(cast.FirstName+" "+cast.LastName);
            //Console.ReadLine();
            //-----------------------------------------------------------------------------------
            //var address = getAddressWithSp(5);
            //Console.WriteLine(address.AddressId +" "+address.Street + " " + address.City + " " + address.PostalCode + " " + address.StateProvince);
            //Console.ReadLine();
        }
        

        // ---------- Get All Customers -------------------
        private static void GetAll()
        {
            using (var db = new SalesModelContext())
            {
                var cust = db.Customer.ToList();

                foreach (var customer in cust)
                {
                    Console.WriteLine(customer.FirstName + " " + customer.LastName + " " + customer.DateOfBirth + " " + customer.CustomerId);
                }
            }

            Console.ReadLine();
        }

        // ---------- Get Customer By Name -------------------
        private static void GetByName(string firstName)
        {
            using (var db = new SalesModelContext())
            {
                var cust = db.Customer.Where(p => p.FirstName == firstName);

                foreach (var customer in cust)

                {
                    Console.WriteLine(customer.FirstName + " " + customer.LastName + " " + customer.DateOfBirth + " " + customer.CustomerId);
                }
            }
            Console.ReadLine();
        }


        // ---------- Get Customer by ID -------------------
        private static void GetCustomerId(int customerId)
        {
            using (var db = new SalesModelContext())
            {
                var cust = db.Customer.Find(customerId);
                {
                    Console.WriteLine(cust.FirstName + " " + cust.LastName + " " + cust.DateOfBirth + " " + cust.CustomerId);
                }
            }
            Console.ReadLine();
        }


        // ---------- Get Customer less then age -16 -------------------
        private static void AgeLessThen()
        {
            using (var db = new SalesModelContext())
            {
                var customers = db.Customer.Where(p => p.DateOfBirth.Year > DateTime.Now.Year - 16);
                foreach (var customer in customers)

                {
                    Console.WriteLine(customer.FirstName + " " + customer.LastName + " " + customer.DateOfBirth + " " + customer.CustomerId);
                }
            }
            Console.ReadLine();
        }


        // ---------- Get All Adresses -------------------
        public static void GetAllAdresses()
        {
            using (var db = new SalesModelContext())
            {
                var addrss = db.Addresse.ToList();
                foreach (var address in addrss)
                {
                    Console.WriteLine(address.AddressId + " " + address.Street + " " + address.City + " " + address.StateProvince + " " + address.PostalCode + " " + address.CustomerId);
                }
            }

            Console.ReadLine();
        }


        // ---------- Get one City only-------------------
        public static void GetOneCity(string city)
        {
            using (var db = new SalesModelContext())
            {
                var addrss = db.Addresse.Where(p => p.City == city);
                foreach (var address in addrss)
                {
                    Console.WriteLine(address.City);
                }
            }
            Console.ReadLine();
        }


        // ---------- OrderBy -------------------
        public static void GetAddressOrderby()
        {
            using (var db = new SalesModelContext())
            {
                var addrss = db.Addresse.OrderBy(p => p.City);
                foreach (var address in addrss)
                {
                    Console.WriteLine(address.City);
                }
            }
            Console.ReadLine();
        }


        //--------OrderByDescending----------------------
        public static void GetAddressOrderbyDescending()
        {
            using (var db = new SalesModelContext())
            {
                var addrss = db.Addresse.OrderByDescending(p => p.City);
                foreach (var address in addrss)
                {
                    Console.WriteLine(address.City);
                }
            }
            Console.ReadLine();
        }


        //-------------------Do Search-------------------------------------------------------------------------000000000000000000000000--------------
        private static void DoSearch(string firstN, string lastName)
        {
            using (var db = new SalesModelContext())
            {

                var query = db.Customer.Where(p => p.CustomerId > 0);

                if (!string.IsNullOrEmpty(firstN))
                {
                    query = query.Where(p => p.FirstName == firstN);
                }
                if (!string.IsNullOrEmpty(lastName))
                {
                    query = query.Where(p => p.LastName == lastName);
                }
                var customers = query.ToList();

                foreach (var cust in customers)
                {
                    Console.WriteLine(cust.FirstName + " " + cust.LastName);
                }
            }
            Console.ReadLine();
        }


        //-----AddRecord--------------------------
        public static void AddRecord()
        {
            using (var db = new SalesModelContext())
            {
                var cust = new Customer
                {
                    FirstName = "George",
                    LastName = "Maisuradze",
                    DateOfBirth = DateTime.Now.AddYears(-30)
                };
                db.Customer.Add(cust);
                db.SaveChanges();

            }

        }


        //---Update Records--------------------------------

        private static void UpdateCustomer()
        {
            int customerId = 1;
            using (var db = new SalesModelContext())
            {

                var customer = db.Customer.Find(customerId);
                customer.DateOfBirth = DateTime.Now.AddYears(-22);
                customer.LastName = "JaJ";
                db.SaveChanges();

            }
            Console.ReadLine();
        }


        //---Delete Record--------------------------
        public static void DeleteRecords()
        {
            using (var db = new SalesModelContext())
            {
                var cust = db.Customer.Where(c => c.FirstName == "David").ToList();

                foreach (var customer in cust)
                {
                    db.Customer.Remove(customer);
                }
                db.SaveChanges();

            }

        }


        //---Insert  Record to DB--------------------------
        public static void ObjectGraph()
        {
            var customer = new Customer
            {
                FirstName = "John",
                LastName = "Bush",
                DateOfBirth = DateTime.Now.AddYears(-25)
            };
            var address = new Address()
            {
                Street = "123 Test",
                City = "Philly",
                StateProvince = "PA",
                PostalCode = "19111"
            };
            customer.Addresses.Add(address);
            using (var context = new SalesModelContext())
            {
                context.Customer.Add(customer);
                context.SaveChanges();
            }
        }


        //---Get One Address--------------------------
        public static void GetAddress(int Id)
        {

            using (var db = new SalesModelContext())
            {
                var addrss = db.Addresse.Find(Id);

                {
                    Console.WriteLine(addrss.City + " " + addrss.PostalCode);
                }
            }
            Console.ReadLine();
        }


        //-----------EagreLoading()----------- goes in and retrieved DATA in ONE SHOT-----------
        public static void EagreLoading()
        {
            using (var db = new SalesModelContext())
            {
                var eagreLoadgraph = db.Customer.Include(c => c.Addresses).ToList();
                var eagreLoadgraph2 = db.Customer.Include("Addresses").ToList();
                var eagreLoadgraph3 = db.Customer.Include("Addresses").Where(p => p.Addresses.Any()).ToList();
            }
        }


        //---------List of Customers----------
        public static List<Customer> Callsp()
        {
            List<Customer> customers;

            using (var db = new SalesModelContext())
            {
                customers = db.Database.SqlQuery<Customer>("aait_Customer_Retrieve").ToList();
            }
            return customers;
        }

        //---------List of Addresses----------
        public static List<Address> CallAddresses()
        {
            List<Address> addresses;

            using (var db = new SalesModelContext())
            {
                addresses = db.Database.SqlQuery<Address>("aait_Address_Retrieve").ToList();
            }
            return addresses;
        }


        //-----------Return only one Customer--------
        public static Customer getCustomerWithSp(int id)
        {
            Customer customer = null;

            using (var db = new SalesModelContext())
            {
                customer = db.Database.SqlQuery<Customer>("aait_Customer_Retrieve  @CustomerId=" + id).FirstOrDefault();
            }
            return customer;
        }


        //-----------Return only one Address--------
        public static Address getAddressWithSp(int id)
        {
            Address address = null;

            using (var db = new SalesModelContext())
            {
                address = db.Database.SqlQuery<Address>("aait_Address_Retrieve @CustomerId=" + id).FirstOrDefault();
            }
            return address;
        }
    }
}


//------------------ POCO Class1-----------------------------------------------

namespace EsSample.Models
{
    public class Address
    {
        public int AddressId { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string StateProvince { get; set; }
        public string PostalCode { get; set; }
        public int CustomerId { get; set; }
        public Customer Customer { get; set; }

    }
}
//------------------ POCO Class2-----------------------------------------------

namespace EsSample.Models
{
    public class Customer : AuditableBase
    {
        public Customer()
        {
            Addresses = new List<Address>();
        }

        public int CustomerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DateOfBirth { get; set; }
        public string PhoneNumber { get; set; }
        public string EmailAddress { get; set; }


        public List<Address> Addresses { get; set; }
        //public ICollection<Address> Addresses { get; set; }
    }
}

//-----------------Connection To DataBase---------------------------------

using EsSample.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EsSample.Data
{
    public class SalesModelContext : DbContext
    {
        public SalesModelContext() : base("name=DefaultConnection")
        {

        }
        public DbSet<Customer> Customer { get; set; }
        public DbSet<Address> Addresse { get; set; }
        }
    }
}

//-----------Web.config--- and Creating DataBase(SalesDb)------

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source= (Local); Initial Catalog = SalesDB; Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>