Avoid Query Client Evaluation error on a query with method definition inside entity class

c# entity-framework entity-framework-core lazy-evaluation sql-server

Question

In a .NET Core 2.1 project, I'm using EF Core with Command pattern (using MediatR library) on a SQL Server database.

I setup the project to avoid client query evaluation, by using these settings:

var phaseOptions = new DbContextOptionsBuilder<PhaseDbContext>().UseSqlServer(configuration.GetConnectionString("PhaseDbContext"),
        sqlServerOptions => sqlServerOptions
            .EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null))
    .ConfigureWarnings(warnings => warnings
        .Throw(RelationalEventId.QueryClientEvaluationWarning)) // Disable Client query evaluation
    .Options;

Now I get a QueryClientEvaluationException with this query:

var articleCodes = await PhaseContext.PhaseArticles
    .Where(a => !request.ArticleFamily.HasValue || a.GetArticleFamily() == request.ArticleFamily.Value)
    .ToListAsync(cancellationToken);

The problem is on the a.GetArticleFamily() method call, because that method now is defined as follows, inside the PhaseArticle entity class:

public class PhaseArticle
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Description { get; set; }
    public string UnitOfMeasure { get; set; }
    public string Category { get; set; }
    public string Group { get; set; }
    public string Family { get; set; }
    public double UnitCost { get; set; }
    public string AdditionalDescription { get; set; }
    public string ExternalCode { get; set;}
    public string ColorCode { get; set;}
    public string Note { get; set; }

    public ArticleFamily GetArticleFamily()
    {
        switch (Family)
        {
            case "CEL":
                return ArticleFamily.Cell;
            case "STR":
                return ArticleFamily.String;
            case "RAW":
                return ArticleFamily.OtherRawMaterial;
            case "SFP":
                return ArticleFamily.SemiFinishedPanel;
            case "FP":
                return ArticleFamily.FinishedPanel;
            default:
                return ArticleFamily.Other;
        }
    }
}

Now, I want to know if it is possible to keep the QueryClientEvaluationWarning option by somehow refactoring (and probably moving away from the entity class) the GetArticleFamily() method.

Update 2019/02/26

@StriplingWarrior I've updated again the code with your suggestion about ValueConverter(), but now it is giving this error:

Cannot convert Lambda expression into a tree of expressions.

Update 2019/02/25

Following @StriplingWarrior suggestion, I'm trying to write a custom converter but I'm not able to make my code compile.

The error with the code below is about return value of the first switch block (it's string but it is expected to be an enum) and about the expected input value of the second switch block (it's a string but it is expected to be an enum).

This is the code:

public static void ApplyPhaseConversions<T>(this ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<PhaseArticle>()
        .Property(e => e.Family)
        .HasConversion(new ValueConverter<ArticleFamily, string> {
            v =>
            {
                switch (v)
                {
                    case ArticleFamily.Cell:
                        return "CEL";
                    case ArticleFamily.String:
                        return "STR";
                    case ArticleFamily.OtherRawMaterial:
                        return "RAW";
                    case ArticleFamily.SemiFinishedPanel:
                        return "SFP";
                    case ArticleFamily.FinishedPanel:
                        return "FP";
                    default:
                        return "";
                }
            },
            v =>
            {
                switch (v)
                {
                    case "CEL":
                        return ArticleFamily.Cell;
                    case "STR":
                        return ArticleFamily.String;
                    case "RAW":
                        return ArticleFamily.OtherRawMaterial;
                    case "SFP":
                        return ArticleFamily.SemiFinishedPanel;
                    case "FP":
                        return ArticleFamily.FinishedPanel;
                    default:
                        return ArticleFamily.Other;
                }
            }});
}
1
2
2/27/2019 7:34:24 AM

Accepted Answer

Finally, the solution was almost there, as also @StriplingWarrior said.

Due to limitations in the C# compiler, such that it can't create expression trees for this code, the solution is to factory the conversion code into methods and then call those in HasConversion.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<PhaseArticle>()
        .Property(e => e.Family)
        .HasConversion(new ValueConverter<ArticleFamily, string>(
            v => StringFromArticleFamily(v),
            v => ArticleFamilyFromString(v));
}

private static ArticleFamily ArticleFamilyFromString(string family)
{
    switch (family)
    {
        case "CEL":
            return ArticleFamily.Cell;
        case "STR":
            return ArticleFamily.String;
        case "RAW":
            return ArticleFamily.OtherRawMaterial;
        case "SFP":
            return ArticleFamily.SemiFinishedPanel;
        case "FP":
            return ArticleFamily.FinishedPanel;
        default:
            return ArticleFamily.Other;
    }
}

private static string StringFromArticleFamily(ArticleFamily articleFamily)
{
    switch (articleFamily)
    {
        case ArticleFamily.Cell:
            return "CEL";
        case ArticleFamily.String:
            return "STR";
        case ArticleFamily.OtherRawMaterial:
            return "RAW";
        case ArticleFamily.SemiFinishedPanel:
            return "SFP";
        case ArticleFamily.FinishedPanel:
            return "FP";
        default:
            return "";
    }
}
0
2/27/2019 2:05:38 PM

Popular Answer

It looks like you're using GetArticleFamily() to convert between the database values and your C# enums. EF Core has a built-in feature called Value Conversions which is meant to address this: https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions

You should be able to define a ValueConverter to translate to and from ArticleFamily values, and then change the type of the Family property to ArticleFamily, and use that property in your query:

var articleCodes = await PhaseContext.PhaseArticles
    .Where(a => !request.ArticleFamily.HasValue || a.Family == request.ArticleFamily.Value)
    .ToListAsync(cancellationToken);

PS--I'm not sure what kind of query the code above will produce, but it might be better to compose your query like this:

var articleQuery = PhaseContext.PhaseArticles.AsQueryable();
if(request.ArticleFamily.HasValue)
{
    articleQuery = articleQuery.Where(a => a.Family == request.ArticleFamily.Value);
}
var articleCodes = await articleQuery.ToListAsync(cancellationToken);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow