Entity Framework 6, CountAsync and PredicateBuilder

My previous subject was about the ToListAsync() method used with the PredicateBuilder from LinqKit which was not working due to the AsExpandable() call and the not implemented IDbAsyncEnumerable interface...

After the modification that you can find in the previous post, I have tried to call the CountAsync() method and a similar exception occured:

"System.InvalidOperationException : The provider for the source IQueryable doesn't implement IDbAsyncQueryProvider. Only providers that implement IDbAsyncQueryProvider can be used for Entity Framework asynchronous operations." 

Another modification on LinqKit is needed for this new case: we just need to take my previous modification and use this implementation of ExpandableQueryProvider class with the following code:

   1:  public class ExpandableQueryProvider<T> : IDbAsyncQueryProvider

   2:  {

   3:      readonly ExpandableQuery<T> _query;

   4:      

   5:      internal ExpandableQueryProvider(ExpandableQuery<T> query)

   6:      {

   7:          _query = query;

   8:      }

   9:   

  10:      IQueryable<TElement> IQueryProvider.CreateQuery<TElement>(Expression expression)

  11:      {

  12:          return new ExpandableQuery<TElement>(_query.InnerQuery.Provider.CreateQuery<TElement>(expression.Expand()));

  13:      }

  14:   

  15:      IQueryable IQueryProvider.CreateQuery(Expression expression)

  16:      {

  17:          return _query.InnerQuery.Provider.CreateQuery(expression.Expand());

  18:      }

  19:      

  20:      TResult IQueryProvider.Execute<TResult>(Expression expression)

  21:      {

  22:          return _query.InnerQuery.Provider.Execute<TResult>(expression.Expand());

  23:      }

  24:      

  25:      object IQueryProvider.Execute(Expression expression)

  26:      {

  27:          return _query.InnerQuery.Provider.Execute(expression.Expand());

  28:      }

  29:      

  30:      public Task<object> ExecuteAsync(Expression expression, CancellationToken cancellationToken)

  31:      {

  32:          return Task.FromResult(_query.InnerQuery.Provider.Execute(expression.Expand()));

  33:      }

  34:   

  35:      public Task<TResult> ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken)

  36:      {

  37:          return Task.FromResult(_query.InnerQuery.Provider.Execute<TResult>(expression.Expand()));

  38:      }

  39:  }

With that new stuff, CountAsync() will work as usual even after a call to AsExpandable()!

Entity Framework 6, ToListAsync and PredicateBuilder

For a new personal project which uses Entity Framework 6, I needed a powerful way to play with a lot of criteria in a search request.

The query has to been done on a TPC Code First model: all the classes inherit from a common base class with other abstract classes and interfaces for some of them, etc. In conclusion, the model is pretty complex and the query possibilities are wide!

Thanks to AutoMapper, the usage of this model from the front-end to the back-end is really easy but I encounter a problem with the way to manage my criteria logic: how to correctly create some Expression<Func<TEntity, bool>> for the .Where(...) method?

For that kind of needs, PredicateBuilder is a very good helper! You just need to create a basic "true" predicate like that:

   1:  var where = PredicateBuilder.True<TEntity>();

And you add your conditions according to your logic with that kind of code:

   1:  if (condition)

   2:  {

   3:      where = where.Add(t => t.Something > 0);

   4:  }

When all your criteria have been added (OfType is used because it's a TPC model):

   1:  context.Entities.OfType<TEntity>().AsExpandable().Where(where).ToList();

AsExpandable() is a method from LinqKit that you have with the PredicateBuilder and which is necessary.

The problem that I have encountered  I am using Entity Framework 6 and I want to use the async/await capacities of this new version. So all my code in my repositories are using the new xxxAsync methods of Entity Framework. But if you use the actual version of LinqKit, and you call the AsExpandable method before the call to ToListAsync(), you will have this exception:

"System.InvalidOperationException : The source IQueryable doesn't implement IDbAsyncEnumerable..."

The source code of LinqKit is open an available here. For my project, I downloaded the source code, included the project in my solution and I have done the following modifications:

   1:  public class ExpandableQuery<T> : IQueryable<T>, IOrderedQueryable<T>, IOrderedQueryable, IDbAsyncEnumerable<T>

   2:  {

   3:      ExpandableQueryProvider<T> _provider;

   4:      IQueryable<T> _inner;

   5:   

   6:      internal IQueryable<T> InnerQuery { get { return _inner; } }

   7:   

   8:      internal ExpandableQuery (IQueryable<T> inner)

   9:      {

  10:          _inner = inner;

  11:          _provider = new ExpandableQueryProvider<T> (this);

  12:      }

  13:   

  14:      Expression IQueryable.Expression { get { return _inner.Expression; } }

  15:      Type IQueryable.ElementType { get { return typeof (T); } }

  16:      IQueryProvider IQueryable.Provider { get { return _provider; } }

  17:      public IEnumerator<T> GetEnumerator () { return _inner.GetEnumerator (); }

  18:      IEnumerator IEnumerable.GetEnumerator () { return _inner.GetEnumerator (); }

  19:      public override string ToString() { return _inner.ToString(); }

  20:      

  21:      public IDbAsyncEnumerator<T> GetAsyncEnumerator()

  22:      {

  23:          return new ExpandableDbAsyncEnumerator<T>(this.AsEnumerable().GetEnumerator());

  24:      }

  25:      

  26:      IDbAsyncEnumerator IDbAsyncEnumerable.GetAsyncEnumerator()

  27:      {

  28:          return GetAsyncEnumerator();

  29:      }

  30:  }

And I have added this new class:

   1:  public class ExpandableDbAsyncEnumerator<T> : IDbAsyncEnumerator<T>

   2:  {

   3:      private readonly IEnumerator<T> _inner;

   4:      public ExpandableDbAsyncEnumerator(IEnumerator<T> inner)

   5:      {

   6:          _inner = inner;

   7:      }

   8:      public void Dispose()

   9:      {

  10:          _inner.Dispose();

  11:      }

  12:      public Task<bool> MoveNextAsync(CancellationToken cancellationToken)

  13:      {

  14:          return Task.FromResult(_inner.MoveNext());

  15:      }

  16:      public T Current

  17:      {

  18:          get { return _inner.Current; }

  19:      }

  20:      object IDbAsyncEnumerator.Current

  21:      {

  22:          get { return Current; }

  23:      }

  24:  }

Thanks to this change, you can use the excellent PredicateBuilder and you can still use the ToListAsync() methods of the new version of Entity Framework!

I hope it will help someone!

News

That's a long time since my latest article on this blog!

Since the previous article, many things have changed for me: I quit my job at Emakina Belgium, I am now freelancer and I moved from Brussels to London for a contract of 6 months for my current client: Ink.

Next steps: creating my own company in Belgium at my return in Belgium and continuing to work on my next personal project!

New project for the belgian market

Edit: To comply with the laws of Belgium, the site has been put offline (some exchanges were not legal and I do not want to be responsible of that).

Last week, I launched a new site dedicated to the Belgian market (and maybe I will be targeting other countries after...): ChequeChange

The site is prety simple and is dedicated to the exchange of meal/eco vouchers in Belgium: this is not a business that fascinates me but making this project will be useful for some people and has help me to improve my skills! (the code and the databases of my next projects will be better!)

The website has been realised with the following technologies:

- ASP.NET MVC 4
- Entity Framework 5.x (but I still prefer using stored procedures that EF can map...)
- Windows Azure (2 small instances for the moment)
- MetricsHub (for monitoring and in case of automatic scale out needed... very good product!)
- SQL Azure
- Azure Storage
- Azure CDN
- Azure AppFabric Caching (thanks to MVC Donut Caching...)

The design is -almost- responsive and the site can be used on all the devices ;-)

This is my second experience with Windows Azure... I like this product! The SDK is excellent and up to date, the portal is well done and easy to use, the analytics are useful, performance are good, etc. A great product!

My team won the first place in Belgium!

The second step of the "Super Hackathon" in Belgium ended on April 19... And my team won the competition! Congrats to my teammates: Benoit Jadinon, Giuliano Dore and Esteban Goffaux.

You can see here three screenshots of "Fit Beast":

There are still some missing functionalities but the app is now available on the belgian market (not other countries for the moment, more to come...)!

You can find our app here but we plan to remove it from my Microsoft account and put it on the Emakina account: the link will become unavailable.

Thanks to this first place, I will attend to TechEd Europe 2013 at madrid! I never attended to a so big Microsoft event except for the TechDays in Belgium and France and I am delighted to go there!

Thanks Microsoft for the free ticket and thanks to Emakina for the journey!