WAQS: Querying on calculated properties

7 reasons to use WAQS

WAQS documentation

 

In this post, we saw how to create a calculated property with WAQS.

Base on this sample, we now have a property CustomerName on the Customer class.

So, if you don’t know that it’s a calculated property, we could want to use it in a LINQ query.

So for example like this:

var customers = await _context.Customers.AsAsyncQueryable().Where(c => c.CustomerName.Contains("wi")).ExecuteAsync();


With LINQ To Entities, we have an error because EF model does not know the CustomerName property. So with L2E we have to duplicate the CustomerName logic into the L2E query which is not good.



The very cool thing is the fact that WAQS supports it!



In this case, if we take a look on the executed SQL query, we can see that the calculated property logic is included in the SQL query:



exec sp_executesql N’SELECT
CASE WHEN ([Extent1].[IsVIP] = 0) THEN 0X ELSE 0X0X END AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CompanyName] + N” - + [Extent1].[ContactName] LIKE @p__linq__0 ESCAPE N”~”’,N’@p__linq__0 nvarchar(4000)‘,@p__linq__0=N’%wi%











 



How does our code C# is injected into the SQL query?



Using our GetCustomerName method, DAL generation added a new class: CustomerDALSpecifications.cs. This one exposes a property CustomerNameExpression:



Expression<Func<WebApplication25.Customer, string>> ICustomerDALSpecifications.CustomerNameExpression
{
     get { return CustomerDALSpecifications.GetCustomerNameExpression (); } }


protected
 internal static Expression<Func<WebApplication25.Customer, string>> GetCustomerNameExpression (
bool isCoalesceEnabled = true) {
     Expression<Func<WebApplication25.Customer, string>> exp = (c) => string.Concat(c.CompanyName, " - ", c.ContactName);
     GetCustomerNameExpression (ref exp);
     return exp; } static partial void GetCustomerNameExpression (ref Expression<Func<WebApplication25.Customer, string>> exp);


When SerializableExpression to Expression transformation sees the CustomerName property, it replaces it by the CustomerNameExpression.



 



Note that in the current version, you need an expression usable by LINQ To Entities (which only implies a return statement in your method body). In the future, I plan to be able to switch to L2O else.

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>