Marc Marc - 3 months ago 14
C# Question

EF Core relationships query

I'm fairly new to Entity Framework, my tables relationship looks a bit like this

public class Customer {
public int Id { get; set; }
public string Name { get; set; }

public List<Product> Products { get; set; }
}

public class Product {
public int Id { get; set; }

public int CustomerId { get; set; }
public Customer Customer { get; set; }

}


I would like to make a query on the Customer table and include only the last Product created MAX(Id)

Normal SQL query would look like this

SELECT *
FROM Customer
INNER JOIN Product ON Customer.Id = Product.CustomerId
WHERE Product.Id = (SELECT MAX(Id) FROM Product WHERE CustomerId = Customers.Id)


My current EF query look like this but it return all the products...

List<Customer> customers = _context.Customers
.Include(c => c.Products)
.ToList();


I tried something like this which gives me the right results, but EF makes a bunch of query and very quickly I see this seems like wrong way to go at it

List<Customer> customers = _context.Customers
.Select(c => new Customer() {
Id = c.Id,
Name = c.Name,
c.Products = c.Products.Where(d => d.Id == c.Products.Max(max => max.Id)).ToList()
}).ToList();


I would like some suggestion, or if there's a different way to make this works.

Answer

It looks like below query can be written in a different way

SELECT * 
FROM Customer
INNER JOIN Product ON Customer.Id = Product.CustomerId
WHERE Product.Id = (SELECT MAX(Id) FROM Product WHERE CustomerId = Customers.Id)   

This can be written as

SELECT TOP 1 *
FROM Customer
INNER JOIN Product ON Customer.Id = Product.CustomerId
Order by Product.Id desc

Assuming customer name is required,above query can be written in LINQ or using EF as below

var customers = _context.Customers.Join(_context.Products, cu => cu.id,
p => p.CustomerId, (cu,p) => new { cu,p})
.Select( c => new { prodId = c.p.Id,customername = c.cu.Name })
.OrderByDescending( c => c.prodId).Take(1);