Entity Framework EF Query using LINQ for related Entities - Getting Customers with their orders from specific period of time only

Question

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.

var customers = context.Customers .Include(o => o.Orders) .Where(a => a.DateTime > start && a.DateTime < end) .ToList(); Something like this? Yeah,it doesn't work, "Customer doesn't contain a definition for a "DateTime" [...] " — User, Nov 11 '18 at 18:16
The property you're using to filter called `Date` not `DateTime`.. This is why it says " _Customer doesn't contain a definition for a "DateTime"_ " — Ahmad Ibrahim, Nov 11 '18 at 18:29
No, it's not the issue. Thats how i just typed it here(with mistake), but in code it's DateTime. The issue is how to iterate with linq through Orders list realted to specific Customers and get Only Orders from specific Period of time. — User, Nov 11 '18 at 18:36
As you see, this question is being asked more often. The duplicate is just one appearance. Filtered `Include` is still [under discussion](https://github.com/aspnet/EntityFrameworkCore/issues/1833). — Gert Arnold, Nov 11 '18 at 18:51

score 0 · Answer 1 · answered Nov 11 '18 at 18:36

0

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();

answered Nov 11 '18 at 18:36

Ahmad Ibrahim

1,915
2
15
32

I need a list of all customers, but with only orders from that given period of time. For example Customer "1" has 2 orders 1- Date : 01.01.2015 2-Date: 01.01.2017 An user gives a period of time from 01.01.2016 to 01.01.2018 He should recive and Customer 1 entity but only with 1 Order included -> Order nr.2 – User Nov 11 '18 at 18:52

Entity Framework EF Query using LINQ for related Entities - Getting Customers with their orders from specific period of time only

1 Answers1