Ndtyjky

I am having an issue with LINQ query for getting a Customers list from Database with orders from only specific period of time. I Tried many different things but still can't figure it out how such LINQ query schould look like. Those enitites looks something like this:

public class Customer

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Order> Orders { get; set; }

    }



 public class Order

{

    public int Id { get; set; }

    public DateTime Date { get; set; }

    public Customer Customer { get; set; }

}

Ammong many different thing i tried this:

var customers = context.Customers

           .Include(o => o.Orders.Where(a=>a.DateTime > start && a.DateTime < end))

           .ToList();

, but i get an System.InvalidOperationException.
I guess .Include can't be used that way.

PS: I am a newbie in EF and LINQ so please don't be harsh.

EDIT:
I think i have to clarifiy what i am trying to do:

After reciving a query from an user, who gives a period of time for example: from 01.01.2015 to 01.01.2018

The database is supposed to return ALL CUSTOMERS with realted orders but ONLY FROM that specific period of time. So I could for example invoke .Count() method and check how many orders a particular customer has placed during this period of time.

This should give a list of customers who have at least one order between start and end date.

var customers = context.Customers

       .Where(o => o.Orders.Any(a=>a.Date > start && a.Date < end))

       .ToList();

If you need customers who have all orders between start and end date, you can use All instead of Any.

var customers = context.Customers

       .Where(o => o.Orders.All(a=>a.Date > start && a.Date < end))

       .ToList();