using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.EntityClient;
using System.Data;

namespace SimpleDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            DisplayAllVehicles();

            //AddVehicle();
            //DisplayAllVehicles();

            //DeleteVehicle();
            //DisplayAllVehicles();

            //ChangeOwner();
            //DisplayAllVehicles();

            //ProcessVehicles();
            //ProcessVehiclesUsingEntityDataReader();

            Console.ReadLine();
        }


        private static void DisplayAllVehicles()
        {
            using (CarsDatabaseEntities context = new CarsDatabaseEntities())
            {
                foreach (Vehicle v in context.Vehicles)
                    Console.WriteLine(v);
            }
        }


        private static void AddVehicle()
        {
            using (CarsDatabaseEntities context = new CarsDatabaseEntities())
            {
                try
                {
                    context.Vehicles.Add(new Vehicle
                    {
                        CarID = 1234,
                        Manufacturer = "Ferrari",
                        Reg = "FF01 555",
                        Owner = "Fernando"
                    });
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.InnerException.Message);
                }
            }
        }


        private static void DeleteVehicle()
        {
            Console.Write("Enter ID of vehicle to delete: ");
            int id = int.Parse(Console.ReadLine());

            using (CarsDatabaseEntities context = new CarsDatabaseEntities())
            {
                // We can use EF API to lookup entity.
                // EntityKey key = new EntityKey("CarsDatabaseEntities.Vehicles", "CarID", id);
                // Vehicle vehicleToDelete = (Vehicle)context.GetObjectByKey(key);

                // Or we can use LINQ to Entities to lookup entity.
                Vehicle vehicleToDelete = (from v in context.Vehicles
                                           where v.CarID == id
                                           select v).FirstOrDefault();

                if (vehicleToDelete != null)
                {
                    Console.Write("Are you sure you want to delete: {0}? ", vehicleToDelete);
                    if (Console.ReadLine().ToUpper()[0] == 'Y')
                    {
                        context.Vehicles.Remove(vehicleToDelete);
                        context.SaveChanges();
                    }
                }
            }
        }


        private static void ChangeOwner()
        {
            Console.Write("Enter ID of vehicle: ");
            int id = int.Parse(Console.ReadLine());

            Console.Write("Enter new owner: ");
            string newOwner = Console.ReadLine();

            using (CarsDatabaseEntities context = new CarsDatabaseEntities())
            {
                // We can use EF API to lookup entity.
                // EntityKey key = new EntityKey("CarsDatabaseEntities.Vehicles", "CarID", id);
                // Vehicle vehicleToChangeOwner = (Vehicle)context.GetObjectByKey(key);

                // Or we can use LINQ to Entities to lookup entity.
                Vehicle vehicleToChangeOwner = (from v in context.Vehicles
                                                where v.CarID == id
                                                select v).FirstOrDefault();

                if (vehicleToChangeOwner != null)
                {
                    vehicleToChangeOwner.Owner = newOwner;
                    context.SaveChanges();
                }
            }
        }


        private static void ProcessVehicles()
        {
            using (CarsDatabaseEntities context = new CarsDatabaseEntities())
            {
                var vehicleManufacturers = from v in context.Vehicles
                                           select v.Manufacturer;

                Console.WriteLine("Manufacturers:");
                foreach (var item in vehicleManufacturers.Distinct())
                {
                    Console.WriteLine("\t" + item);
                }


                var vehicleSummaries = from v in context.Vehicles
                                       select new { v.Reg, v.Manufacturer };

                Console.WriteLine("Registrations and manufacturers:");
                foreach (var item in vehicleSummaries.Distinct())
                {
                    Console.WriteLine("\t{0} {1}", item.Reg, item.Manufacturer);
                }


                var countWalesCars = (from v in context.Vehicles
                                      where v.Reg.StartsWith("C")
                                      select v.CarID).Count();

                Console.WriteLine("There are {0} Welsh-registered cars.", countWalesCars);
            }
        }


        private static void ProcessVehiclesUsingEntityDataReader()
        {
            // Make a connection object, based on our *.config file. 
            using (EntityConnection cn = new EntityConnection("name=CarsDatabaseEntities"))
            {
                cn.Open();

                // Now build an Entity SQL query.
                string query = "SELECT VALUE vehicle FROM CarsDatabaseEntities.Vehicles AS vehicle";

                // Create a command object.
                using (EntityCommand cmd = cn.CreateCommand())
                {
                    cmd.CommandText = query;

                    // Finally, get the data reader and process records.
                    using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    {
                        Console.WriteLine("\nAll vehicle details, retrieved using entity client API:");
                        while (dr.Read())
                        {
                            Console.WriteLine("[{0}] {1}, {2}, {3}", dr["CarID"],
                                                                        dr["Manufacturer"],
                                                                        dr["Reg"],
                                                                        dr["Owner"]);
                        }
                    }
                }
            }
        }
    }


    public partial class Vehicle
    {
        public override string ToString()
        {
            return string.Format("[{0}] {1}, registration {2}, owner {3}", this.CarID, this.Manufacturer, this.Reg, this.Owner);
        }
    }
}