1

How can EF Core project computed properties to SQL instead of calculating them on the client side?

public enum OrderType
{
    Normal,
    Special,
}

public class Order
{
    public int SpecialContainerId? { get; set; }
    public OrderType CalculatedOrderType { get => SpecialContainerId == null ? OrderType.Normal: OrderType.Special; }
}

// Version 1
dbContext.Order.Where(o => o.CalculatedOrderType == OrderType.Special);
// Version 2
dbContext.Order.Where(o => o.SpecialContainerId != null);

There are two versions for my EF Core query.

The first one is slow, the second fast, because the first one is computed at the client side, and the other in the database.

Is it possible to write computed properties on the Order class which are projected to SQL in an easy way? I do not want to repeat my query all the time.

Writing a method on order which returns expression would work of course. But is there an easy way available meanwhile?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alois
  • 361
  • 2
  • 18
  • 2
    Why do you think that one of that queries is executed client side? What means "slow"? How many entries does your DB have? Does `CalculatedOrderType` have an index? `SpecialContainerId` probably has one - that would be an explanation for different execution times. – nilsK Feb 08 '23 at 15:39
  • The second is probably fast because the results are cached in your context from the first call. You can always help the querying by doing stuff like giving it the exact values needed (so instead of `CalculatedOrderType == OrderType.Special`, try `SpecialContainerId == 1`) – MPelletier Feb 08 '23 at 16:38
  • Which EF Core version do you use? Why first query even executes? Does `CalculatedOrderType ` is mapped to table? – Svyatoslav Danyliv Feb 08 '23 at 16:59
  • 1
    I'm pretty sure Svyatoslav is going to propose their favorite LinqKit, but I would suggest looking at https://stackoverflow.com/questions/62115690/ef-core-queries-all-columns-in-sql-when-mapping-to-object-in-select/62138200#62138200, especially the update for EF Core 7.0+. It is the simplest solution from source code point of view, as all it needs is to decorate your computed properties/methods with attribute. But not changing the implementation, or bothering with funcs and expressions as other alternative libraries do. – Ivan Stoev Feb 08 '23 at 18:17
  • None of the shared code would cause EF to interact with DB. Please share the *actual* code you run. – n0rd Feb 08 '23 at 20:32
  • @IvanStoev, you know me and what EF can do and what is not ;) – Svyatoslav Danyliv Feb 08 '23 at 20:37

0 Answers0