using System;

using System.Linq;

using System.Data.Entity;

using System.Collections;

using System.Collections.Generic;

public class Program

{

    public static void Main()

    {

        using (var ctx = new DatabaseContext())

        {

            InitializeDB(ctx);

            var query = from custom in ctx.Customers

                join pur in ctx.Purchases on custom.CustomerId equals pur.CustomerId

                 where custom.DateOfBirth <= new DateTime(2000, 1, 1)

                 select new

            {

                custom.CustomerId,

                custom.FullName,

                custom.DateOfBirth,

                pur.PurchaseDateUTC,

                pur.Total

            };

​

            foreach (var cust in query){

                 Console.Write(cust.FullName);

                Console.WriteLine ("  ( edad:" + CalcularEdad(cust.DateOfBirth)+"años )");

                Console.Write(cust.PurchaseDateUTC.ToString("dd/MM/yyyy"));

                Console.Write("   ");

                Console.WriteLine(cust.Total);

                Console.WriteLine("==============================");

            }

       }

            //foreach (var purchase in ctx.Purchases){

                //Console.Write(purchase.Customer.FullName);

                //Console.WriteLine(purchase.Customer.DateOfBirth.ToString("dd/MM/yyyy"));

            //}

            // The following displays Student table data - dotnetfiddle.net specific code

            //FiddleHelper.WriteTable("Putvhsdr", ctx.Purchases.ToList());

        //}

    }

    public static void InitializeDB(DatabaseContext ctx){

        ctx.Customers.Add(new Customer(){CustomerId = 1, FullName = "Mario Cuevas", DateOfBirth = new DateTime(1985,10,18)});

        ctx.Customers.Add(new Customer(){CustomerId = 2, FullName = "Pedro Gimenez", DateOfBirth = new DateTime(2010,01,10)});

        ctx.Customers.Add(new Customer(){CustomerId = 3, FullName = "Ricardo Gomez", DateOfBirth = new DateTime(1993,11,25)});

        ctx.Customers.Add(new Customer(){CustomerId = 4, FullName = "María Sanchez", DateOfBirth = new DateTime(2009,12,2)});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,2,10,12,32), Total = 1000.5m, CustomerId = 1});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,4,2,25,55), Total = 56.12m, CustomerId = 2});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,7,3,12,57), Total = 75.45m, CustomerId = 2});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,12,1,17,12), Total = 987.5m, CustomerId = 3});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,15,8,55,00), Total = 12000.5m, CustomerId = 3});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,1,25,10,43,10), Total = 1.7m, CustomerId = 3});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,2,2,17,32,22), Total = 100.5m, CustomerId = 4});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,2,2,15,22,35), Total = 255.75m, CustomerId = 1});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,2,7,12,07,45), Total = 888.8m, CustomerId = 3});

        ctx.Purchases.Add(new Purchase(){PurchaseId = 1001, PurchaseDateUTC = new DateTime(2020,2,9,9,00,10), Total = 672.25m, CustomerId = 1});

        ctx.SaveChanges(); 

    }

    public static int CalcularEdad(DateTime fechaNacimiento)

        {

            // Obtiene la fecha actual:

            DateTime fechaActual = DateTime.Today;

            // si la fecha de nacimiento es mayor a la fecha actual se muestra mensaje 

            // de advertencia:

            if (fechaNacimiento > fechaActual)

            {

                Console.WriteLine ("La fecha de nacimiento es mayor que la actual.");

                return -1;

            }

            else 

            {

                int edad = fechaActual.Year - fechaNacimiento.Year;

                // Comprueba que el mes de la fecha de nacimiento es mayor 

                // que el mes de la fecha actual:

                if (fechaNacimiento.Month > fechaActual.Month)

                {

                    --edad;

                }

                return edad;

            }

        }

}

​

public class Purchase

{

    public int PurchaseId {get; set;}

    public DateTime PurchaseDateUTC { get;set;}

    public Decimal Total { get;set; }

    public int CustomerId {get; set;}

    public virtual Customer Customer {get;set;}

}

​

public class Customer

{

    public int CustomerId {get; set;}

    public string FullName {get; set;}

    public DateTime DateOfBirth {get;set;}

}

​

​

public class DatabaseContext: DbContext 

{

    public DatabaseContext() : base(FiddleHelper.GetConnectionStringSqlServer()) 

    {

    }

    public DbSet<Purchase> Purchases { get; set; }

    public DbSet<Customer> Customers { get; set; }

}

​