EF6: Use reference/lookup data with IQueryable

c# entity-framework-6 iqueryable linq lookup

Question

I want to use a pre-loaded lookup data from a list within a query. I need the query to return as IQueryable because it is used in a grid & being paged (not included here). I need the Labels to load from the lookup to avoid joins (more are in the actual code).

I know I could do a ToList() and post-process it, but I need the IQueryable. Here is the code:

// Run in intialization other code...
var contactLookups = new ContactsDbContext();
List<StatusType> _statusTypes = contactLookups.StatusTypes.ToList();


    public IQueryable GetQuery()
    {
        var contactsCtx = new ContactsDbContext();
        IQueryable query = contactsCtx.Select(contact => new
        {
            Id = contact.Id,
            FullName = contact.FirstName + " " + contact.LastName,
            CompanyName = contact.CompanyName,
            Status = _statusTypes.FirstOrDefault(l => contact.StatusId == l.Id).Label
        });
        return query;
    }

The code above throws an error because EF/LINQ can't form the in-memory list into SQL (for obvious reasons) - unless something is added/changed.

I want to somehow tell EF to apply the lookup after the SQL or something to that effect. I had read about doing something similar with EF Helper code and Expressions, but I can't find that article anymore.

Note, I'm flexible on the lookup itself. The only non-negotiable is the "contact.StatusId" (int), but the rest of the structure "_statusTypes.FirstOrDefault(l => contact.StatusId == l.Id)", like the List type, is open.

Any help is appreciated.

1
3
11/23/2015 10:46:16 PM

Accepted Answer

You can wrap EF's query into your own intercepting implementation of IQueryable, in which you can inject values of in-memory lookups prior to returning objects to application.

It may sound complex, but actually is not that hard to implement. The following needs to be done:

  1. Mark the Status property in your entity as non-mapped (using Ignore() with Fluent API or [NotMapped] attribute on the property).

  2. Write InterceptingQueryable<T> (let's name it so) implementation of IOrderedQueryable<T>, which wraps an IQueryable<T> object from the EF (returned by the Select method in your example).

  3. Write InterceptingQueryProvider<T> implementation of IQueryProvider<T>, which in turn wraps query provider obtained from the EF.

  4. Write InterceptingEnumerator<T> implementation of IEnumerator<T>, which relays to enumerator object returned by the EF. This enumerator will inject the value of Status property (can easily be generalized to populate any lookup property this way), right after it performs MoveNext, so that object returned by Current is fully populated.

The above chain is connected as follows:

  1. InterceptingQueryable relays to EF's query object, passed in the constructor.

  2. InterceptingQueryable.Provider property returns InterceptingQueryProvider.

  3. InterceptingQueryable.GetEnumerator method returns InterceptingEnumerator.

  4. InterceptingQueryProvider.CreateQuery methods obtain query object from the EF query provider, then return it wrapped in another instance of InterceptingQueryable.

  5. InterceptingQueryProvider.Execute invokes Execute on the EF query provider, then in the case it gets an entity which is subject to lookup injection, it injects the lookup values in the same way as InterceptingEnumerator does (extract a method for reuse).

UPDATE

Here is the code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Examples.Queryables
{
    public class InterceptingQueryable<T> : IOrderedQueryable<T>
    {
        private readonly Action<T> _interceptor;
        private readonly IQueryable<T> _underlyingQuery;
        private InterceptingQueryProvider _provider;

        public InterceptingQueryable(Action<T> interceptor, IQueryable<T> underlyingQuery)
        {
            _interceptor = interceptor;
            _underlyingQuery = underlyingQuery;
            _provider = null;
        }
        public IEnumerator<T> GetEnumerator()
        {
            return new InterceptingEnumerator(_interceptor, _underlyingQuery.GetEnumerator());
        }
        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
        public Expression Expression 
        {
            get { return _underlyingQuery.Expression; }
        }
        public Type ElementType 
        {
            get { return _underlyingQuery.ElementType; }
        }
        public IQueryProvider Provider 
        {
            get
            {
                if ( _provider == null )
                {
                    _provider = new InterceptingQueryProvider(_interceptor, _underlyingQuery.Provider); 
                }
                return _provider;
            }
        }

        private class InterceptingQueryProvider : IQueryProvider
        {
            private readonly Action<T> _interceptor;
            private readonly IQueryProvider _underlyingQueryProvider;

            public InterceptingQueryProvider(Action<T> interceptor, IQueryProvider underlyingQueryProvider)
            {
                _interceptor = interceptor;
                _underlyingQueryProvider = underlyingQueryProvider;
            }
            public IQueryable CreateQuery(Expression expression)
            {
                throw new NotImplementedException();
            }
            public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
            {
                var query = _underlyingQueryProvider.CreateQuery<TElement>(expression);

                if ( typeof(T).IsAssignableFrom(typeof(TElement)) )
                {
                    return new InterceptingQueryable<TElement>((Action<TElement>)(object)_interceptor, query);
                }
                else
                {
                    return query;
                }
            }
            public object Execute(Expression expression)
            {
                throw new NotImplementedException();
            }
            public TResult Execute<TResult>(Expression expression)
            {
                var result = _underlyingQueryProvider.Execute<TResult>(expression);

                if ( result is T )
                {
                    _interceptor((T)(object)result);
                }

                return result;
            }
        }

        private class InterceptingEnumerator : IEnumerator<T>
        {
            private readonly Action<T> _interceptor;
            private readonly IEnumerator<T> _underlyingEnumerator;
            private bool _hasCurrent;

            public InterceptingEnumerator(Action<T> interceptor, IEnumerator<T> underlyingEnumerator)
            {
                _interceptor = interceptor;
                _underlyingEnumerator = underlyingEnumerator;
                _hasCurrent = false;
            }
            public void Dispose()
            {
                _underlyingEnumerator.Dispose();
            }
            public bool MoveNext()
            {
                _hasCurrent = _underlyingEnumerator.MoveNext();

                if ( _hasCurrent )
                {
                    _interceptor(_underlyingEnumerator.Current);
                }

                return _hasCurrent;
            }
            public void Reset()
            {
                _underlyingEnumerator.Reset();
            }
            public T Current 
            {
                get
                {
                    return _underlyingEnumerator.Current;
                }
            }
            object IEnumerator.Current
            {
                get { return Current; }
            }
        }
    }

    public static class QueryableExtensions
    {
        public static IOrderedQueryable<T> InterceptWith<T>(this IQueryable<T> query, Action<T> interceptor)
        {
            return new InterceptingQueryable<T>(interceptor, query);
        }
    }
}

And here is test case/example. First, we should not forget to add non-mapped Status property to Contact entity:

public partial class Contact
{
    [NotMapped]
    public StatusType Status { get; set; }
}

Then, we can use the interceptor mechanism as follows:

var contactLookups = contactsCtx.StatusTypes.ToList();

Action<Contact> interceptor = contact => {
    contact.Status = contactLookups.FirstOrDefault(l => contact.StatusId == l.Id);
};

// note that we add InterceptWith(...) to entity set
var someContacts = 
    from c in contactsCtx.Contacts.InterceptWith(interceptor) 
    where c.FullName.StartsWith("Jo")
    orderby c.FullName, c.CompanyName
    select c;

Console.WriteLine("--- SOME CONTACTS ---");
foreach ( var c in someContacts )
{
    Console.WriteLine(
        "{0}: {1}, {2}, {3}={4}", 
        c.Id, c.FullName, c.CompanyName, c.StatusId, c.Status.Name);
}

which prints:

--- SOME CONTACTS ---
1: John Smith, Acme Corp, 3=Status Three
3: Jon Snow, The Wall, 2=Status Two

and the query gets translated into:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FullName] AS [FullName], 
    [Extent1].[CompanyName] AS [CompanyName], 
    [Extent1].[StatusId] AS [StatusId]
    FROM [dbo].[Contacts] AS [Extent1]
    WHERE [Extent1].[FullName] LIKE 'Jo%'
    ORDER BY [Extent1].[FullName] ASC, [Extent1].[CompanyName] ASC
3
11/23/2015 10:32:44 PM

Popular Answer

I'm not sure what is the benefit of avoiding the joins compared to the obvious drawback of not being able to process the whole query at the database side, but what you are asking for can be achieved by doing as much as possible (filtering, ordering, grouping, projection) with Linq to Entities, then turn it into IEnumerable and do the rest with Linq To Objects. You can always use Enumerable.AsQueryable to switch to IQueryable implementation over IEnumerable. Something like this

public IQueryable GetQuery()
{
    var db = new ContactsDbContext();
    var query = db.Contacts.Select(contact => new
    {
        Id = contact.Id,
        FullName = contact.FirstName + " " + contact.LastName,
        CompanyName = contact.CompanyName,
        StatusId = contact.StatusId
    })
    .AsEnumerable()
    .Select(contact => new
    {
        Id = contact.Id,
        FullName = contact.FullName,
        CompanyName = contact.CompanyName,
        Status = _statusTypes.FirstOrDefault(l => contact.StatusId == l.Id).Label
    })
    .AsQueryable();
    return query;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow