krillgar krillgar - 1 year ago 48
C# Question

Casting a Select to get a property from .Last() in a collection

I have a complex object with many children where I'm trying to only select a few properties from it and its children for displaying in a grid. The way the query was structured before with blindly tossing in every

that was needed generated a SQL statement that is 1095 lines long.

I have no issue with getting single properties from a child object, however the one is the name of the last activity that was performed. Doing a
on the collection tosses an Exception that it can't be converted to SQL. I'll make a basic example to help visualize (all FKs are actually set in my code, that's not the problem) :

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

This is a basic example of the query I'm setting up:

public IEnumerable<FooModel> GetHomePageItems(IEnumerable<int> fooIds)
return await context.Foos
.Where(f => fooIds.Contains(
.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

Is this something that cane be done, or do I have to pull everything in without the
, and then query for the activities with a GroupBy and get them that way?

Answer Source

You can try changing your query to:

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

That linq generates the following sql for Entity Framework version 6.1.3:

    [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]

Which might be enough for your project. On large amounts of data, though, you might have to switch to something faster, even probably to manual query using .Sql() method.