EF Core relationships query
10,304
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);
Author by
Admin
Updated on June 15, 2022Comments
-
Admin almost 2 years
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.