我有一个包含两个表的数据库:
Customers(Id PK,LastName)
和
Orders(Id PK,CustomerId FK,ProductName,Price等)
我想要检索客户的最新订单详细信息以及客户名称。
我使用.NET L2SQL,但我认为这是一个SQL问题而不是LINQ问题,所以我在这里发布了我尝试过的SQL查询:SELECT [t0].[LastName], (
SELECT [t2].[ProductName]
FROM (
SELECT TOP (1) [t1].[ProductName]
FROM [Orders] AS [t1]
WHERE [t1].[CustomerId] = [t0].[Id]
ORDER BY [t1].[Id] DESC
) AS [t2]
) AS [ProductName], (
SELECT [t4].[Price]
FROM (
SELECT TOP (1) [t3].[Price]
FROM [Orders] AS [t3]
WHERE [t3].[CustomerId] = [t0].[Id]
ORDER BY [t3].[Id] DESC
) AS [t4]
) AS [Price]
FROM [Customers] AS [t0]
问题是订单(Orders)有更多的列(30),每增加一个子查询,查询语句就会变得更长且更慢。
有没有更好的方法?