Convert.ToDecimal in Projections

[Mike-E-angelo]

Ask a question

I have been spending the past hour or so tracking this down. I would like to do something similar to the behavior found in this thread:
#11295

Basically, I am currently calling Convert.ToDecimal in my projections, but they are only yielding 2 decimal places of precision. I would like to move this to 4 or 8, depending on the projection.

The issue above was made prior to .NET5.0 and while it does land on post-5.0 code, it does so for the JsonValue method and not the decimal convert method. I am also curious if there's anything that has changed and/or improved around this since .net60.

Include your code

Currently I am calling Convert.ToDecimal in EFCore projections:

context.Set<Amount>().Select(x => Convert.ToDecimal(x.Value))...

Include stack traces

NA

Include verbose output

NA

Include provider and version information

EF Core version: 6.0.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Microsoft Visual Studio Community 2022 (64-bit) - Preview Version 17.4.0 Preview 2.1

Thank you for any assistance you can provide!

[ajcvickers]

@Mike-E-angelo There is a long thread on that issue; can you be specific about what problems you are running into? Ideally, please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Mike-E-angelo]

Hi @ajcvickers thank you for your reply. There isn't an issue per se, but more of a question of what is the best way of doing this in .NET6.0. The code provided in that thread is (from what I understand) pre-.NET5.0 and the .NET5.0 portion for what I am interested in ("DECIMAL(31, 6)") is no longer part of that conversation.

So, put succinctly: how does one override the default DECIMAL(18, 2) Convert.ToDecimal conversion using the latest version of EFCore?

[ajcvickers]

@Mike-E-angelo Something like this:

modelBuilder.HasDbFunction(typeof(SomeDbContext).GetMethod(nameof(Convert))!)
    .HasTranslation(args =>
        {
            var arguments = args.ToList();
            
            arguments[0] = new SqlFragmentExpression((string)((SqlConstantExpression)arguments[0]).Value!);
            return new SqlFunctionExpression("CONVERT", arguments, true, new[] { true }, typeof(decimal), null);
        });

[Mike-E-angelo]

Excellent, thank you @ajcvickers! I will give that a shot and reopen if I encounter any issues. I appreciate your time!

[Mike-E-angelo]

OK thanks to @AndriySvyryd's great suggestion here I was able to get a compiled model with the RuntimeDbFunction. However, now I am getting a weird error that I did not get before when I was using Convert.ToDecimal:

System.InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.
   at void Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplySetOperation(SetOperationType setOperationType, SelectExpression select2, bool distinct)
   at ShapedQueryExpression Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateConcat(ShapedQueryExpression source1, ShapedQueryExpression source2)
   at Expression Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) x 6
   at Func<QueryContext, TResult> Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor<TResult>(Expression query)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync<TResult>(Expression query, CancellationToken cancellationToken)+() => { }
   at Func<QueryContext, TResult> Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery<TResult>(object cacheKey, Func<Func<QueryContext, TResult>> compiler)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync<TResult>(Expression query, CancellationToken cancellationToken)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken)
   at TResult Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync<TSource, TResult>(MethodInfo operatorMethodInfo, IQueryable<TSource> source, Expression expression, CancellationToken cancellationToken) x 2
   at Task<bool> Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AnyAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)

This would seem to imply that the the RuntimeDbFunction call is on the client (or causing a client projection), when it's supposed to be on the database server, correct? That is, this CONVERT call is being made on the database as far as I can tell, so why would it trigger a client projection? Or have I completely misunderstood how this works (quite possible, of course!)?

Again, if I switch back to Convert.ToDecimal this does not occur. Thank you for any clarification anyone can provide here.

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Mike-E-angelo]

Thank you for your reply @ajcvickers. I very much understand and appreciate your request but unfortunately, I cannot reproduce this easily for you. However, what is important to me is to verify my understanding/sanity here: the RuntimeDbFunction should not cause a client projection, correct? Is there anything obvious that would cause this to work differently from using a Convert.ToDecimal? Looking for another lucky suggestion, basically. :D

[ajcvickers]

@Mike-E-angelo Possibly where it is in the query, possibly something else.

[Mike-E-angelo]

Great, thank you @ajcvickers. I know you deal with dozens of these a week if not a day, so I definitely appreciate your time and patience. I'll see if I can poke around and see what's going on here.

[Mike-E-angelo]

I have been taking a look at this and it appears that this is related to optimized models. If I remove the optimized model and use the context directly as standard, no exception is generated and everything works as expected.

Unfortunately I cannot get a simple repro for you and have been trying to get one. There is something about my model when optimized generates this error.

[Mike-E-angelo]

Egads, what an ordeal. OK figured this out and as you probably would expect this is due to user error:

My registration was something like this (where I put the function in a partial class):

functions["Starbeam.Entities.Database.Functions.ConvertNetwork(double)"] = CreateConvertNetwork();

And it should have been this:

functions["Starbeam.Entities.Database.Functions.ConvertNetwork(decimal)"] = CreateConvertNetwork();

The reason it was complaining about the client evaluation is that the function was not properly registered.

All is working now. Been a while since shooting myself in the foot and I was a bit overdue. 😅😭 Thank you again for your time and patience!