CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-6
Connell
Connell

Reputation: 14411

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

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?

Upvotes: 1

Views: 458

Answers (2)

Connell
Connell

Reputation: 14411

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
}

Upvotes: 0

usr
usr

Reputation: 171178

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.

Upvotes: 1

Related Questions