Why does this SelectMany perform several SQL queries instead of a single join?

Question

I have the following extension method:

public static decimal? GetValue(this Member member)
{
    return member.Readings.SelectMany(r => r.Measurements).GetLatestValue();
}

GetLatestValue is another extension that only uses other LINQ extensions: OrderBy, Where, Select and First.

I'd expect this to perform a JOIN query. Instead, when I look in SQL Profiler, it is performing a separate query to select all measurements for each reading.

I understand from this and this question that I could get a JOIN if I passed in the database context and used that, however this is not an option for me.

What is happening here? Why is the Readings property an ICollection, not an IQueryable? How can I get a single query here, without having to change the extension methods signature?

score 1 · Answer 1 · answered Feb 21 '14 at 11:07

1

What is happening here?

Your description of the problem is accurate.

"Why is the Readings property an ICollection, not an IQueryable?"

It's a design mistake in Entity Framework.

How can I get a single query here, without having to change the extension methods signature?

This is impossible. Your method forces the query to be evaluated. Even if member.Readings was IQueryable you still would be forcing evaluation here.

Note, that EF will never be able to remote GetLatestValue to SQL (I assume it is a custom function of yours). There's no workaround for that. EF cannot generate SQL for arbitrary C# functions.

Unfortunately, there is no great solution for your situation. You will have to refactor your code so that it plays well with Entity Framework and its limitations. The posts you linked to are relevant for doing that.

answered Feb 21 '14 at 11:07

usr

168,620
35
240
369

The `GetLatestValue` is just a bunch of chained `OrderBy`, `Where`, `Select` and `First` methods. That's a shame - this extension is used in over 20 places in my code :( – Connell Feb 21 '14 at 12:12
Thanks for pointing out its a design mistake y Microsoft. Because of this I've came up with my own solution that tries to 'hide' Microsofts mistake. +1 – Connell Feb 21 '14 at 15:14
1

I'm not sure how it is a design mistake, it needs to be an ICollection so you can add and remove items from it. How do you think it should be to fix this? – Mant101 Nov 14 '14 at 16:45
@Mant101 they could use some type derived from ICollection *and* IQueryable (for example EntitySet, or an interface if you don't like coupling to an implementation). – usr Nov 14 '14 at 16:50

score 0 · Accepted Answer · answered Feb 21 '14 at 15:13

I've came up with a solution that allows me to use my calling code as I'd expect it to work, but with a little hack:

In my extension method and any other places where similar queries are needed, I just have to make sure I call this code:

EvilHackyContextUtilities.SetReadingsQueryableHack(member);

What this does is replace the member.Readings property with my own ICollection that is also an IQueryable. The IQueryable side of things uses the same query suggested in the other questions I've linked to. I've managed to get the ObjectContext using reflection and then pass that in into the constructor of my new MyDbContext. I've had to change the .tt source a little to add a constructor that uses DbContext(ObjectContext objectContext, bool dbContextOwnsObjectContext) as it's base.

public static class EvilHackyContextUtilities
{
    private static MyDbContext GetDbContext(object entity)
    {
        var entityWrapper = entity.GetType().GetField("_entityWrapper").GetValue(entity);
        var objectContext = entityWrapper.GetType().GetProperty("Context").GetValue(entityWrapper, null) as ObjectContext;
        return new MyDbContext(objectContext, false);
    }

    public static void SetReadingsQueryableHack(Member entity)
    {
        if (entity.Readings is EvilHackyQueryableCollection<Reading>)
            return;

        IQueryable<Reading> query = GetDbContext(entity).Readings.Where(r => r.MemberID == entity.MemberID);
        entity.Readings = new EvilHackyQueryableCollection<Reading>(entity.Readings, query);
    }
}

internal class EvilHackyQueryableCollection<TEntity> : ICollection<TEntity>, IQueryable<TEntity>
    where TEntity : class
{
    private readonly IQueryable<TEntity> _baseQuery;
    private readonly ICollection<TEntity> _baseCollection;

    public EvilHackyQueryableCollection(ICollection<TEntity> baseCollection, IQueryable<TEntity> baseQuery)
    {
        _baseQuery = baseQuery;
        _baseCollection = baseCollection;
    }

    #region ICollection members
    //All middle-man methods wrapping up the _baseCollection field.
    #endregion

    #region IQueryable members
    //All middle-man methods wrapping up the _baseQuery field.
    #endregion
}

Why does this SelectMany perform several SQL queries instead of a single join?

2 Answers2