krillgar krillgar - 1 year ago 59
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

.Include()
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
.Last().Name
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(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();
}


Is this something that cane be done, or do I have to pull everything in without the
LastActivity
, 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
                }).ToListAsync();

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

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]

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download