首页 新闻 会员 周边

ef core 中linq查询的问题

0
悬赏园豆：20 [已关闭问题] 关闭于 2017-03-06 15:16

同一段测试代码

var ingIconList = context.IngIcons
        .GroupBy(a => new
        {
            a.IconType,
            a.FestivalDate
        })
        .Select(a => new { Id = a.Max(c => c.Id) })
        .Join(context.IngIcons, a => a.Id, b => b.Id, (a, b) => b)
        .ToListAsync();

实体

public class IngIcon
{
    public int Id { get; set; }
    public int IconType { get; set; }
    public string IconName { get; set; }
    public string IconRemark { get; set; }
    public DateTime? FestivalDate { get; set; }
    public string IocnPath { get; set; }
    public bool IsChina { get; set; }
    public DateTime DateAdded { get; set; }
}
View Code

在ef6.0中追踪到的sql

SELECT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[IconType] AS [IconType], 
    [Extent2].[IconName] AS [IconName], 
    [Extent2].[IconRemark] AS [IconRemark], 
    [Extent2].[FestivalDate] AS [FestivalDate], 
    [Extent2].[IocnPath] AS [IocnPath], 
    [Extent2].[IsChina] AS [IsChina], 
    [Extent2].[DateAdded] AS [DateAdded]
    FROM   (SELECT 
        [Extent1].[IconType] AS [K1], 
        [Extent1].[FestivalDate] AS [K2], 
        MAX([Extent1].[Id]) AS [A1]
        FROM [dbo].[IngIcons] AS [Extent1]
        GROUP BY [Extent1].[IconType], [Extent1].[FestivalDate] ) AS [GroupBy1]
    INNER JOIN [dbo].[IngIcons] AS [Extent2] ON [GroupBy1].[A1] = [Extent2].[Id]

在ef core中测试追踪到的sql

SELECT [a].[Id], [a].[DateAdded], [a].[FestivalDate], [a].[IconName], [a].[IconRemark], [a].[IconType], [a].[IocnPath], [a].[IsChina]
FROM [IngIcons] AS [a]
ORDER BY [a].[IconType], [a].[FestivalDate]
go
SELECT [b].[Id], [b].[DateAdded], [b].[FestivalDate], [b].[IconName], [b].[IconRemark], [b].[IconType], [b].[IocnPath], [b].[IsChina]
FROM [IngIcons] AS [b]
go

很明显后面的查了全表数据，而且还是两次，但是最终程序执行返回的结果都是正确的。

求解~~~

问题补充：

linqpad 解析是最理想的

SELECT [t2].[Id], [t2].[IconType], [t2].[IconName], [t2].[IconRemark], [t2].[FestivalDate], [t2].[IocnPath], [t2].[IsChina], [t2].[DateAdded]
FROM (
    SELECT MAX([t0].[Id]) AS [value]
    FROM [IngIcons] AS [t0]
    GROUP BY [t0].[IconType], [t0].[FestivalDate]
    ) AS [t1]
INNER JOIN [IngIcons] AS [t2] ON [t1].[value] = [t2].[Id]
捷义的主页 捷义 | 菜鸟二级 | 园豆：241
提问于：2017-03-06 12:01
< >
分享
所有回答(1)
0

.Select(a => new { Id = a.Max(c => c.Id) }) 似乎在 Linq 查询中使用函数会进行全表查询。而且你还是new 的，可参考 http://www.cnblogs.com/dudu/p/6291192.html

BUTTERAPPLE | 园豆：3190 (老鸟四级) | 2017-03-06 13:09

这是两种情况，linq查询条件中出现需要计算的值，那个条件可能会被忽略，查到的是错误的数据。我这里的情况不同，最后得到的结果是对的，但是过程中查了全表，这里的max并不是自定义函数，映射的是sql的max聚合函数，至于new ，语法就是这么写的，这个子查询的计算结果是作为后面内连接的条件。

支持(0) 反对(0) 捷义 | 园豆：241 (菜鸟二级) | 2017-03-06 14:01
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。