EF Core using navigation property when querying parent and child separatedly

c# entity-framework-core

Question

I'm currently developing something with EF Core and I'm not yet fluent in linq-to-Entity or in linq for that matter.

I have two entities that I need to query, one is the customer and the other are the invoices. But I need to retrieve only his/her latest invoice, and I only need customers that have been active (aka made a purchase) in the last x days.

I'd like to do the following to obtain the customers

IEnumerable<Customer> customers = _context.Customers
           .Where(c => c.LastUpdate > date)
           .ToList();

And this to obtain the invoices

IEnumerable<Invoice> invoices = _context.Invoices
           .Where(i => customers.Contains(i.customerId)).ToDictionary(i => i.customerId, i => i)
           .Where(c => c.Invoices.Max(i => i.Date) = i.Date) //I don't know how to do this part but I hope you get the idea that I'm trying to get only the newest invoice for each customer
           .ToList();

The question is whether or not I'll be able to use the Invoice.Customer property find the customer this invoice belongs to, or alternatively Customer.Invoice.First() to get the corresponding invoice.

Or is there another way to do this? Sorry I'm a noob and usually I work more with sql, but I'm trying to move with the times...

Btw I'd like to keep this with the entity framework syntax only and rather not deal with linq and IQueryable because I'm trying to deal only with entities and wouldn't like to map things in the repository.

Thanks

1
1
4/18/2020 8:36:55 PM

Popular Answer

You want the IQueryable<T> instead of the IEnumerable<T> when using an ORM like EF. The IQueryable<T> instructs the compiler to create an expression tree (Expression<T>) which is traversed by the query engine in the ORM to generate an optimized SQL statement for the underlying DB. The IEnumerable<T>, however, is complied (not an expression tree) and only the CLR can understand which means the query engine has lost the visibility into LINQ-to-Entity.

IEnumerable<Customer> customers = context.Customers.Where(x => x.State=="CA")

translates to:

Select * from Customers

Instead of the RDBMS doing the work, the ORM will retrieve the entire customer table and have the CLR enumerate the collection to find all customers who live in California which is highly inefficient.

IQueryable<Customer> customers = context.Customers.Where(x => x.State=="CA")

translates to:

Select * from Customers Where State='CA'

The Invoice entity class should have the Customer as a navigation property. This allows you to get the customers who are active in the x days and their latest invoice.

var results = context.Invoice.GroupBy(c => c.Customer.Id)
 .Select(grp =>
    new {
          Cust = grp.Key, 
          LatestInvoice = grp.Where(d => d.LastUpdateDate >= "date").OrderByDescending(i => i.LastUpdateDate).First()
    }).ToList();`
0
4/18/2020 7:18:43 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow