// @nuget: EntityFramework

// @nuget: Z.EntityFramework.Extensions

​

using System;

using System.Data.Entity;

using System.Linq;

using System.Collections.Generic;

public class Program

{

    public static void Main()

    {

        // Insert data

        using (var context = new EntityContext())

        {

            context.BulkInsert(CustomerData());

            context.BulkInsert(InvoiceData());

        }

        // Projection Query

        using (var context = new EntityContext())

        {

            var fromDate = DateTime.Now.AddDays(-7);

​

            var customer = context.Customers.Where(c => c.CustomerID == 1)

                .Where(c => c.Invoices.Any(i => i.Date >= fromDate))

                .Select(c => new

                {

                    c,

                    Invoices = c.Invoices.Where(i => i.Date >= fromDate)

                })

                .FirstOrDefault();

            FiddleHelper.WriteTable(customer.Invoices);

        }

​

    }

    public static List<Customer> CustomerData() 

    {

        List<Customer>  list = new List<Customer>()

        {

            new Customer() { Name ="Customer_1"},

            new Customer() { Name ="Customer_2"},

            new Customer() { Name ="Customer_3"}

        };

        return list;

    }

    public static List<Invoice> InvoiceData()

    {

        List<Invoice> list = new List<Invoice>()

        {

            new Invoice() { Date = new DateTime(2018,1,5), CustomerID = 1},

            new Invoice() { Date = new DateTime(2018,5,3), CustomerID = 1},

            new Invoice() { Date = DateTime.Now.AddDays(-5), CustomerID = 1},

            new Invoice() { Date = DateTime.Now.AddDays(-3), CustomerID = 1},

            new Invoice() { Date = new DateTime(2018,4,15), CustomerID = 2},

            new Invoice() { Date = new DateTime(2018,2,20), CustomerID = 3},

            new Invoice() { Date = new DateTime(2018,5,22), CustomerID = 3},

            new Invoice() { Date = new DateTime(2018,5,23), CustomerID = 3}

        };

        return list;

    }

​

    public class EntityContext : DbContext

    {

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

        {

​

        }

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

        public DbSet<Invoice> Invoices { get; set; }

    }

    public class Customer

    {

        public int CustomerID { get; set; }

        public string Name { get; set; }

        public List<Invoice> Invoices { get; set; }

    }

    public class Invoice

    {

        public int InvoiceID { get; set; }

        public DateTime Date { get; set; }

        public int CustomerID { get; set; }

        public Customer Customer { get; set; }

    }

}