简化实体框架查询

本文关键字:查询 框架 实体 | 更新日期: 2023-09-27 17:53:16

我有一个简单的linq查询,它按条件对结果进行排序。

var vehicles = context.Vehicles
    .AsNoTracking()
    .Where(v => v.CreatedAt >= DbFunctions.AddDays(DateTime.UtcNow, -10))
    .ToList();

但是实体框架将这个简单的查询转换为

SELECT 
CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN '0X' WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN '0X0X' WHEN (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL)) THEN '0X1X' ELSE '0X2X' END AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[CreatedAt] AS [CreatedAt], 
[Extent1].[Name] AS [Name], 
CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS bit) WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[HasCycleCar] WHEN (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL)) THEN CAST(NULL AS bit) END AS [C2], 
CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS int) WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS int) WHEN (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL)) THEN [Project3].[Seats] END AS [C3], 
CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS int) WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS int) WHEN (([Project3].[C1] = 1) AND ([Project3].[C1] IS NOT NULL)) THEN CAST(NULL AS int) ELSE [Project2].[Capacity] END AS [C4]
FROM    [dbo].[Vehicles] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[HasCycleCar] AS [HasCycleCar], 
    cast(1 as bit) AS [C1]
    FROM [dbo].[Motorcycles] AS [Extent2] ) AS [Project1] ON [Extent1].[Id] = [Project1].[Id]
LEFT OUTER JOIN  (SELECT 
    [Extent3].[Id] AS [Id], 
    [Extent3].[Capacity] AS [Capacity], 
    cast(1 as bit) AS [C1]
    FROM [dbo].[Trucks] AS [Extent3] ) AS [Project2] ON [Extent1].[Id] = [Project2].[Id]
LEFT OUTER JOIN  (SELECT 
    [Extent4].[Id] AS [Id], 
    [Extent4].[Seats] AS [Seats], 
    cast(1 as bit) AS [C1]
    FROM [dbo].[PassengerCars] AS [Extent4] ) AS [Project3] ON [Extent1].[Id] = [Project3].[Id]
WHERE [Extent1].[CreatedAt] >= (DATEADD (day, -10, SysUtcDateTime()))

来我只需要基本信息,但EF读取所有,更多的是与繁琐的查询。我理解EF需要遵守多态行为,并返回创建它的类型的对象。

但是我怎么能简单地说他没有读不必要的?

简化实体框架查询

如果您只想要实体类型属性的子集,您可以在查询中包含投影(对匿名或命名类型):

var results = from v in context.Vehicles
              …
              select new VechicleSubSet {
                Id = v.Id,
                Name = v.Name
              };

当枚举results(触发对数据库的查询)时,只选择NameId(如果在查询的其他地方使用,可能会引用其他列)。

注意:在你的SQL中,看起来你已经在你的对象表映射中配置了像Table per Class这样的东西:因此EF试图返回该映射内的所有数据,而不仅仅是从单个表。