Casting from string to int using Cast<int?>() throws exception

[Suchiman]

Using Cast<int?> to cast a string in SQL to int produces an exception. This used to work in EF6.

[Index(nameof(License), IsUnique = true)]
public class License
{
    public int Id { get; set; }

    [Required]
    [StringLength(15)]
    public string License { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<License> Licenses { get; set; }
}

var context = new MyContext();
// context.License.Add(new License { License = "DEMO_123" });
// context.SaveChanges();
int currentMaximumLicense = await context.Licenses
    .Where(x => x.License.StartsWith("DEMO_"))
    .Select(x => x.License.Substring(5))
    .Cast<int?>()
    .MaxAsync() ?? 0;

Workaround

int currentMaximumLicense = await context.Licenses
    .Where(x => x.License.StartsWith("DEMO_"))
    .Select(x => (int?)(object)x.License.Substring(5))
    .MaxAsync() ?? 0;

Exception

System.InvalidOperationException: No coercion operator is defined between types 'System.String' and 'System.Nullable`1[System.Int32]'.
   at UnaryExpression System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at UnaryExpression System.Linq.Expressions.Expression.Convert(Expression expression, Type type, MethodInfo method)
   at ShapedQueryExpression Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateCast(ShapedQueryExpression source, Type resultType)
   at Expression Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Expression System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Expression System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Expression Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Expression System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Expression System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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)
   at Task<TSource> Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.MaxAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)

EF Core version: 6.0.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.3 Preview 2

[roji]

This query fails in regular LINQ to Objects, since you can't use the Cast() operator to convert from the string to int. In general, in EF we try to support patterns which work in regular LINQ.

A better way to write this is simply to use Convert to perform your conversion:

int currentMaximumLicense = await context.Licenses
    .Where(x => x.License.StartsWith("DEMO_"))
    .MaxAsync(x => Convert.Int32(x.License.Substring(5)) ?? 0;

(or you can use the double-cast pattern as above)

[Suchiman]

@roji

A better way to write this is simply to use Convert to perform your conversion:

ahhh, i tried using Int32.Parse which didn't work, using Convert didn't came to mind.
Yeah i suppose that makes sense, to align with LINQ to Objects.

EDIT: Although i'll have to use

int currentMaximumLicense = await context.Licenses
    .Where(x => x.License.StartsWith("DEMO_"))
    .MaxAsync(x => (int?)Convert.Int32(x.License.Substring(5)) ?? 0;

as the sequence might be empty

[roji]

Filed #28287 to track translating int.Parse and related, but as discussed we're not going to translate Cast<int?>() as above.