WAQS Querying: Avoid useless data loading using With method

7 reasons to use WAQS

WAQS documentation

 

Imagine that you want to edit an order and, in your form, you want to show (read only) the customer name.

In a previous post, we defined the following GetCustomerName specification method:

public static string GetCustomerName(this Customer c)
{
     return string.Concat(c.CompanyName, " - ", c.ContactName); }


Now, we add a GetCustomerName in Order:



public static string GetCustomerName(this Order o)
{
     return o.Customer.GetCustomerName(); }


So, to get the Order.CustomerName, we need to have already loaded the Order.Customer.



Of course you can do it using Include method but it is useless and inefficient to load the Order.Customer if you only want to get the Order.CustomerName with a read only usage.



 



For this, base on the last specification method we wrote, WAQS generates a WithCustomerName extension method on Order class that you could use in LINQ To WAQS:



var orders = await _context.Orders.AsAsyncQueryable().WithCustomerName().ExecuteAsync();


With this, you have only one SQL query that just loads what we really need:



SELECT
[Extent1].[Id] AS [Id],
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[EmployeeId] AS [EmployeeId],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[RequiredDate] AS [RequiredDate],
[Extent1].[ShippedDate] AS [ShippedDate],
[Extent1].[ShipVia] AS [ShipVia],
[Extent1].[Freight] AS [Freight],
[Extent1].[ShipName] AS [ShipName],
[Extent1].[ShipAddress] AS [ShipAddress],
[Extent1].[ShipCity] AS [ShipCity],
[Extent1].[ShipRegion] AS [ShipRegion],
[Extent1].[ShipPostalCode] AS [ShipPostalCode],
[Extent1].[ShipCountry] AS [ShipCountry],
[Extent2].[CompanyName] + N’ – ‘ + [Extent2].[ContactName] AS [C1]
FROM  [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[Id]











 



How does it work?



The WCF Service Execute method (used to execute queries) has a string array parameter that is used to specify the different calculated properties we want to load with.



In the server, we just have to execute a LINQ query with a Select to get calculated properties in addition to mapped properties.



 



In theory, it could be very easy to build this query.



 



For example, in our previous sample, we could build this LINQ query:



var orders = context.Orders.Select(o => new Order { CustomerId = o.CustomerId, EmployeeId = o.EmployeeId, 
Freight = o.Freight, OrderDate = o.OrderDate, RequiredDate = o.RequiredDate, ShipAddress = o.ShipAddress,
ShipCity = o.ShipCity, ShipCountry = o.ShipCountry, ShipName = o.ShipName, ShipPostalCode = o.ShipPostalCode,
ShipRegion = o.ShipRegion, ShipVia = o.ShipVia, ShippedDate = o.ShippedDate,
CustomerName =
string.Concat(o.Customer.CompanyName, " - ", o.Customer.ContactName) });


However, we have a LINQ To Entities restriction: it is not possible to use a constructor in EF model entities.



In addition, if you do it, you lose entity inheritance.



 



Another approach could be this one:



var orders = context.Orders.Select(o => 
new { Order = o, CustomerName = string.Concat(o.Customer.CompanyName, " - ", o.Customer.ContactName) })
.AsEnumerable().Select(o =>
     {
     o.Order.CustomerName = o.CustomerName;
         return o;
     });


When you build a LINQ expression, you need every used types known by the server and it would be a shame to generate a new type at runtime.



When WAQS generates the code, it knows the model. So it generates a type per entity that inherits of the entity.



class WithOrder : Order
{
    public Order Order { get; set; }
}


Using this type, we can get our entities with the expected calculated properties.



 



Now in the client CustomerName property, WAQS generates this code:



public string CustomerName
{
    get
    {
        try
        {
            if (Specifications != null && Specifications.HasCustomerName)
                return Specifications.CustomerName;
            return this.Customer.CustomerName;
        }
        catch (System.NullReferenceException)
        {
            return default (string);
        }
        catch (System.InvalidOperationException)
        {
            return default (string);
        }
    }
    set
    {
        throw new System.InvalidOperationException();
    }
}


If you get the calculated properties using a With method, the property get returns the value got from the server without trying to calculate it in the server.



 



Note that, with this way, we can get the value of a property only calculable in the server as you saw in this post.

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

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>