Using WAQS as a layer on top of Entity Framework

7 reasons to use WAQS

WAQS documentation

 

We saw in previous posts that WAQS adds some improvements on LINQ To WAQS (comparing to LINQ To Entities).

Now the point is the fact that it would be a shame if we could only use these features on the client.

So WAQS also adds these features in the server side too. It means that you can use these improvements in the WCF service but also in any project using Entity Framework (with an edmx only with the current WAQS version).

 

How to use it?

After installing WAQS NuGet package WCFAsyncQueryableServices.Server and running the command to generate code, you can use WAQS as a layer on top of Entity Framework.

You just have few things to do for it:

  • Call the service Init static method at the beginning.
  • Set the context property UseWAQSProvider to true. //Note that WAQS adds it on service methods generated from specifications.
  • Add two usings: WCFAsyncQueryableServices.DAL.Interfaces and Service

 

How does it works?

As I explained on one of my first posts about it, WAQS adds a lot of abstraction. So, IMHO, it’s better if, when you query on your context, you have no reference to Entity Framework.

To achieve it, you won’t manipulate directly the context but an interface on the context. This interface don’t expose some ObjectSet (or DbSet) but it’s own IEntitySet.

The EF implementation of this IEntitySet encapsulates the Entity Framework ObjectSet. Then, instead of using Entity Framework LINQ provider, EntitySets use WAQS LINQ provider that encapsulates EF one and that just transforms the LINQ expression to an EF supported one.

 

 

So imagine that, using Contoso, you want a list of the 50 first customers with their online sales in last 2000 days (Contoso is an old DB) with their store name, product, product sub category and product category.

If you want a basic query with EF, you can use this one:

var customers =
context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50)
    .Include(c => c.FactOnlineSales.Select(os => os.DimProduct.DimProductSubcategory.DimProductCategory))
    .Include(c => c.FactOnlineSales.Select(os => os.DimStore)).ToList();



But the point here is the fact that you will load too many data. Indeed, you will get all online sales and not only the ones from the last 2000 days and you will load the whole associated store entities instead of only loading the store name. // Note that this is a way many code generation tools, like ASP.NET MVC Scaffolding for example, work.


In addition, this query will be very slow: 66 seconds in my test (with EF5 and EF6)!


 


With EF, you can only load what is expected with this way:


var customersGraph = context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50)
    .Select(c => new 
    {
        Customer = c,
        OnlineSales = c.FactOnlineSales.Where(os => EntityFunctions.DiffDays(DateTime.Now, os.DateKey) <= 2000).Select(os => new 
        {
            OnlineSale = os,
            StoreName = os.DimStore.StoreName,
            Products = os.DimProduct,
            SubCategory = os.DimProduct.DimProductSubcategory,
            Category = os.DimProduct.DimProductSubcategory.DimProductCategory
        })
    }).ToList();




Then, in addition to get some customers you will use select the customers and to get the store name, we will add a property StoreName in FactOnlineSale entity.


So we need to add this code:


foreach (var os in customersGraph.SelectMany(c => c.OnlineSales))
    os.OnlineSale.StoreName = os.StoreName;
var customers = customersGraph.AsEnumerable().Select(c => c.Customer).ToList();




Note that EF engines adds relationships between our entities itself.


It’s better (28 seconds) but it’s still very long, the query is complex and, in addition, we add a dependence on Entity Framework in our query with EntityFunctions class because, as I explained here, LINQ To Entities does not support DateTime calculation yet.


 


To improve efficiency, it’s better to use many queries to get this:


var customersQuery = context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50);
var customers = customersQuery.ToList();
var onlineSalesQuery = customersQuery.SelectMany(c => c.FactOnlineSales).Where(os => EntityFunctions.DiffDays(DateTime.Now, os.DateKey) <= 2000);
foreach (var os in onlineSalesQuery.Select(os => new { OnlineSale = os, StoreName = os.DimStore.StoreName }))    
    os.OnlineSale.StoreName = os.StoreName;
var productQuery = onlineSalesQuery.Select(os => os.DimProduct);
foreach (var _ in productQuery) ; // usefull to let EF engines makes the relationships
var productSubCategoryQuery = productQuery.Select(p => p.DimProductSubcategory);
foreach (var _ in productSubCategoryQuery) ;
foreach (var _ in productSubCategoryQuery.Select(psc => psc.DimProductCategory)) ;




In this case, we got it in only 3.4 seconds.


 


However, if you saw my session “Entity Framework: patterns and anti-patterns”, you know that, with SQL Server, it will be better with these queries:


var customersQuery = context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50);
var customers = customersQuery.ToList();
var onlineSalesQuery =
    context.FactOnlineSales
        .Where(os => customersQuery.Any(c => c.CustomerKey == os.CustomerKey) &&
EntityFunctions.DiffDays(DateTime.Now, os.DateKey) <= 2000);
foreach (var os in onlineSalesQuery.Select(os => new { OnlineSale = os, StoreName = os.DimStore.StoreName}))
    os.OnlineSale.StoreName = os.StoreName;
var productQuery = context.DimProducts.Where(p => onlineSalesQuery.Any(os => os.ProductKey == p.ProductKey));
foreach (var _ in productQuery) ;
var productSubCategoryQuery = context.DimProductSubcategories.Where(psc => productQuery.Any(p => p.ProductSubcategoryKey == psc.ProductSubcategoryKey));
foreach (var _ in productSubCategoryQuery) ;
foreach (var _ in context.DimProductCategories.Where(pc => productSubCategoryQuery.Any(psc => psc.ProductCategoryKey == pc.ProductCategoryKey))) ;


With it, we got it in 2.6 seconds in my tests.


It’s great but you have to take in mind that there are three possible issues with this code:


  • Because we use many SQL queries we reduce scalability if the DB is the contention point
  • Because we use many SQL queries, you could have some inconsistence graph if a concurrent DB access removes a data row between two queries for example
  • Because we use many queries, we can’t use streaming which can be a problem if you want to load millions of entities

 


Anyway, if the risk is low or null, the efficiency improvement is too much important to ignore it!


But the bad point is the fact that this code is more complex than the first one with Include methods and because it is more complex, I know that a lot of developers will continue to use the first way even if it’s 23.5 slower in this sample!


 


So WAQS tries to solve the following challenge: having a L2EW (LINQ To Entities + WAQS) query as easy as the first one, with the same performance than the last one.


First you have to generate the code using WAQS NuGet commands.


With WAQS, you won’t define a StoreName property on FactOnlineSale class. You will define a specifications Get method (and WAQS will generate the property for you).


public static class FactOnlineSaleSpecifications
{
    public static string GetStoreName(this FactOnlineSale os)
    {
        return os.DimStore.StoreName;
    }
}




After regenerating T4 templates, you can use this query:


using L2EWDemo.DAL;
using L2EWDemo.DAL.Interfaces;
using L2EWDemo.Service;
using System;
using System.Linq;
using WCFAsyncQueryableServices.DAL.Interfaces;
 
namespace
L2EWDemo
{
     class Program
     {
         static void Main(string[] args)
         {
             ContosoService.Init();
             using (IContosoRetailDWEntities context = new ContosoRetailDWEntities())
             {
                 context.UseWAQSProvider = true;
                 var customers = context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50)
                     .IncludeFactOnlineSalesWithExpression (factOnlineSales => factOnlineSales.Where(os => (DateTime.Now – os.DateKey).Days <= 2000)
                         .WithStoreName()
                         .IncludeDimProductWithExpression (p => p
                             .IncludeDimProductSubcategoryWithExpression (sc => sc
                                 .IncludeDimProductCategory())))
                     .ToList();
            }
        }
    }
}




FYI, this query runs in 2.6 seconds (as the previous one).


In addition, note that we remove the dependence of our query to Entity Framework.


 


Now if you plan to update some entities and to save changes and you want to use specification validations and metadata, you have to use the service instead of the ObjectContext directly. So you will use this code instead:


using (ContosoService context = new ContosoServiceL2E.Create<ContosoRetailDWEntities>())
{
    var customers = context.DimCustomers.OrderBy(c => c.CustomerKey).Take(50)
        .IncludeFactOnlineSalesWithExpression (factOnlineSales => factOnlineSales.Where(os => (DateTime.Now – os.DateKey).Days <= 2000)
            .WithStoreName()
            .IncludeDimProductWithExpression (p => p
                .IncludeDimProductSubcategoryWithExpression (sc => sc
                    .IncludeDimProductCategory())))
        .ToList();
}




So I think WAQS passed the challenge: L2EW is almost as easy as the Include method and so much more efficient!


What about you?

This entry was posted in 12253, 16868, 7674. Bookmark the permalink.

One Response to Using WAQS as a layer on top of Entity Framework

  1. This is a wonderful post. I enjoyed the information lot. I will bookmark this page. Thanks for sharing this information.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>