EF核心關係查詢

asp.net-core c# entity-framework-core

我對Entity Framework很新,我的表關係看起來有點像這樣

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; }

}

我想在Customer表上進行查詢,並僅包含最後創建的產品MAX(Id)

正常的SQL查詢看起來像這樣

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

我當前的EF查詢看起來像這樣,但它返回所有產品......

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

我試過這樣的東西給了我正確的結果,但EF做了一堆查詢,很快我看到這似乎是錯誤的方式去做它

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

我想要一些建議,或者如果有不同的方法可以使這個工作。

一般承認的答案

看起來下面的查詢可以用不同的方式編寫

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

這可以寫成

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

假設客戶名稱是必需的,上面的查詢可以用LINQ編寫或使用EF編寫如下

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

熱門答案

.NET(LINQ)支持兩種語法,可在Entity Framework中進行查詢

如果您想使用與SQL查詢類似的方法使用“查詢語法”,只需使用查詢語法

    from cus in customers select cust;

方法語法

    customers.where(s=>s.name == "ali").toList;

使用var來保存結果。我想你很擅長使用lambda表達式



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow