强制转换Select以从集合中的. last()获取属性

Question

我有一个复杂的对象，有许多子对象，我试图只从它和它的子对象中选择几个属性显示在网格中。以前的查询方式是盲目地抛出每个需要的.Include()，生成一个1095行长的SQL语句。

我没有问题，从一个子对象获得单一属性，然而，一个是最后一个活动的名称被执行。对集合执行.Last().Name会抛出一个异常，该异常不能转换为SQL。我将做一个基本的例子来帮助可视化(所有fk实际上都是在我的代码中设置的，这不是问题):

public class Foo
{
    public int Id { get; set; }
    // just a dummy class everyone knows for illustration
    public Address Address { get; set; }
    public ICollection<Activity> Activities { get; set; }
}
public class Activity
{
    public string Name { get; set; }
}
public class FooModel
{
    public int Id { get; set; }
    public string StreetName { get; set; }
    public string LastActivity { get; set; }
}

这是我正在设置的查询的一个基本示例:

public IEnumerable<FooModel> GetHomePageItems(IEnumerable<int> fooIds)
{
    return await context.Foos
                        .Where(f => fooIds.Contains(f.id))
                        .Select(f => new FooModel
                            {
                                Id = f.Id,
                                StreetName = f.Address.Street,
                                // here is the problem as it can't 
                                // convert this to SQL
                                LastActivity = f.Activities.Last().Name
                            })
                        .ToListAsync();
}

这是可以做的事情，还是我必须在没有LastActivity的情况下把所有东西都拉进来，然后用GroupBy查询活动并以这种方式获取它们?

Answer 1

您可以尝试将查询更改为:

var query = context.Foos
                .Where(f => fooIds.Contains(f.Id))
                .Select(f => new FooModel
                {
                    Id = f.Id,
                    StreetName = f.Address.Street,     
                    LastActivity = f.Activities.OrderByDescending(x => x.Id).FirstOrDefault().Name
                }).ToListAsync();

linq为Entity Framework version 6.1.3生成以下sql:

SELECT
    [Filter1].[Id1] AS [Id],
    [Filter1].[Street] AS [Street],
    [Limit1].[Name] AS [Name]
    FROM   (SELECT [Extent1].[Id] AS [Id1], [Extent2].[Street] AS [Street]
        FROM  [dbo].[Foos] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Extent1].[Address_Id] = [Extent2].[Id]
        WHERE [Extent1].[Id] IN (1, 2, 3, 4) ) AS [Filter1]
    OUTER APPLY  (SELECT TOP (1) [Project1].[Name] AS [Name]
        FROM ( SELECT
            [Extent3].[Id] AS [Id],
            [Extent3].[Name] AS [Name]
            FROM [dbo].[Activities] AS [Extent3]
            WHERE [Filter1].[Id1] = [Extent3].[Foo_Id]
        )  AS [Project1]
        ORDER BY [Project1].[Id] DESC ) AS [Limit1]

这对你的项目来说可能足够了。但是，对于大量数据，您可能必须切换到更快的方法，甚至可能使用.Sql()方法手动查询。

实体框架不识别.LastOrDefault()和.Last()方法，相反，您应该使用.FirstOrDefault()或.First()方法与OrderBy()或OrderByDescending()相结合。