Writing Computed Members with Entity Framework Core

Question

We are using Entity Framework Core 3 with SqlServer Database. Business program needs to create many columns which are not in the Database, due to storage, high querying cost etc. Currently, the team is Copying the whole Database Layer, and Creating whole another layer adding computed members in new entities. Currently taking database layer and applying AutoMapper to new layer. For some reason, this does not seem like optimal method.

In this example, we require computed members called

FullName => FirstName + LastName

AccountValue => Quantity * StockPrice

Entity Framework 3 does not allow Client Side Evaluation anymore, https://devblogs.microsoft.com/dotnet/announcing-ef-core-3-0-and-ef-6-3-general-availability/ so curious what is standardized way for computed members in Entity Framework Core 3?

Reading this article, curious wondering what is up to date, and can be used? Or does Entity Framework Core 3 Offer New Syntax?

https://daveaglick.com/posts/computed-properties-and-entity-framework

1) We could Materialize the entities. Seems okay, however this forces developer to remember utilize ToList(), had issues where developers forget, causing long db scanning queries, or clientside evaluation caused error.

var result = ctx.Customers
  .ToList()
  .Select(c => new
  {
    FullName = c.FullName,
    AccountValue = c.AccountValue
  });

2) Create Queryable Extension. This only extracts the computed columns, or forces developers to create computed members all in one class (breaks SRP single responsibility idea). Unless there is an alternative modification which address this. This also brings composition chain issues, and possible performance problems like option 1.

public static IQueryable<CustomerData> SelectCustomerData(this IQueryable<Customer> customers) {   return customers.Select(c => new CustomerData   {
    FullName = c.FirstName + " " + c.LastName,
    AccountValue = c.Holdings.Sum(h => h.Quantity * h.Stock.Price)   }); }

3) Expression Projection, does not allow assignment in Select without Linq Expression Project. Company does not allow this third party tool, unless built from Microsoft.

public readonly Expression<Func<Customer, decimal>> AccountValueExpression = c => c.Holdings.Sum(h => h.Quantity * h.Stock.Price);

Or does Entity Framework Core 3 offer newer syntax?

Solution needs to be where, (a) person can extract some or All of the existing members of original DBEntity, (b) and some or all of New Members,

Example, need FirstName (Existing) and AccountValue (New Member)

Or FullName, FirstName, LastName, StockPrice,

Or Everything, FirstName, LastName, FullName ,Quantity, StockPrice, AccountValue, etc, etc

Any mix or match from entities.

Actually migrating from 2.2 to Core 3, however 2.2 has ClientSide Evaluation Disabled. Cannot utilize third party tools, like Linq.Translations, or DelegateCompiler unless they are created from Microsoft vendor .

Prefer not to use SqlServer Computed columns, as we are relying on DBA team. Additionally there are more intricate calculations.

Answer 1

Client side evaluation is evil, thus developers of EF Core 3 has made a good decision to forbid it. The code which could evaluates on client often leads to annoying performance issues. So I wouldn't recommend you to use computed properties in EF Core 2.* as well.

what is standardized way for computed members in Entity Framework Core

If you want to do a computation, sorting, modification, etc. as a part of your query, you should project your entity into DTO at first. In such a case, the query will be compiled into SQL query (and not evaluated on client).

For this task you can use AutoMapper library. It automatically maps properties with the same name. Other properties (computed properties) can be mapped using custom expression.

var config = new MapperConfiguration(cfg =>
{
    cfg.CreateMap<Customer, CustomerDto>()
        .ForMember(x => x.FullName, x => x.MapFrom(z => z.FirstName + " " + z.LastName))
        .ForMember(x => x.AccountValue, x => x.MapFrom(z => z.Quantity * z.StockPrice));
});
var mapper = config.CreateMapper();

Then, you can use ProjectTo extension method. ProjectTo internally call Select so it doesn't materialize entity. Hence, Where statement is parsed into SQL query.

var customers = await context.Customers
    .ProjectTo<CustomerDto>(mapper.ConfigurationProvider)
    .Where(x => x.FullName == "full name" && x.AccountValue > 4)
    .ToListAsync();

Projection of entities is often a good practice. It allows you to select just a few columns from DB and offers you other stuff that is not possible when you are returning just plain entities (e.g. sorting):

var config = new MapperConfiguration(cfg =>
{
    cfg.CreateMap<Invoice, InvoiceDto>();
    cfg.CreateMap<Customer, CustomerDto>()
        .ForMember(x => x.Invoices, x => x.MapFrom(z => z.Invoices.OrderBy(x => x.Date)));
});
var mapper = config.CreateMapper();

// Customers with invoices sorted by date
var customers = await context.Customers
    .ProjectTo<CustomerDto>(mapper.ConfigurationProvider)
    .ToListAsync();

AutoMapper can be also used with DI. However, it is 3rd party library. If your company doesn't permit it, you can create your own mapping layer by hand. Which includes a lot of monkey work ..

Answer 2

Assuming it's supported by your backend, you can mirror your computed properties with computed database columns.

    public string FullName => FirstName + LastName;

    entity.Property(e => e.FullName).HasComputedColumnSql("FirstName + LastName");

Then you can trivially filter, order by, project etc those properties.

Answer 3

I haven't tried this, but it is just a thought - How about creating a custom Extension method of DbFunctions?

I have used EF.Functions.Like method which is an implementation of the SQL LIKE operation. On relational databases this is usually directly translated to SQL.

Check these links -

• DbFunctions Class (Microsoft.EntityFrameworkCore)
• src/EFCore/DbFunctionsExtensions.cs
• Stack Overflow - Entity framework EF.Functions.Like vs string.Contains

Answer 4

First of all, let me quote the EF 3 breaking changes notice here:

Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

I have just successfully tested the following query:

var list = context.Customers.Include(c => c.Stocks).Select(c => new
{
    FullName = c.FirstName + " " + c.LastName,
    TotalInvestment = c.Stocks.Sum(s => s.Price*s.Quantity)
});
list.ToList();
/*
      SELECT ([c].[FirstName] + N' ') + [c].[LastName] AS [FullName], (
          SELECT SUM([s].[Price] * [s].[Quantity])
          FROM [Stocks] AS [s]
          WHERE [c].[Id] = [s].[CustomerId]) AS [TotalInvestment]
      FROM [Customers] AS [c]
*/

But let's explore the topic a bit further and say you want to query your table on a computed field without bringing all evaluation to client side.

var list = context.Customers.Include(c => c.Stocks)
                    .Where(c => string.Concat(string.Concat(c.FirstName, " "), c.LastName) == "John Doe") // notice how we have to do string.Concat twice. observe what hppens if you use the next line instead
                    //.Where(c => string.Concat(c.FirstName, " ", c.LastName) == "John Doe"); // this query will fail to evaluate on SQL and will throw an error, because EF's default concat translator implementation only caters for two parameters
                ;
list.ToList();
/* the following SQL has been generated
      SELECT [c].[Id], [c].[FirstName], [c].[LastName], [s].[Id], [s].[CustomerId], [s].[Price], [s].[Quantity]
      FROM [Customers] AS [c]
      LEFT JOIN [Stocks] AS [s] ON [c].[Id] = [s].[CustomerId]
      WHERE (([c].[FirstName] + N' ') + [c].[LastName]) = N'John Doe'
      ORDER BY [c].[Id], [s].[Id]
*/

Apparently, EF3 comes with a few pre-built functions that allow you to do that: see IMethodCallTranslator implementations (such as StringMethodTranslator for example) to get a bit more insight on how it's doing that (and other translators available to you out of the box).

Okay, what if your translator isn't implemented, I hear you ask. This is where things get a bit more exciting. I have successfully done this for EF 2.2 as outlined in this SO answer (which I invite you to check out). The code unfortunately doesn't directly translate to EF3 1:1 but I'm fairly confident the same approach will work.

UPD: see my github repo for PoC of custom DB functions working with EF Core 3.1