NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse.EF6 6.0.2

EF Core 3.x support for SQL Server's TRY_PARSE function

Use

Install the NuGet package:

EF Core 3.1.x

Install-Package NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse

EF Core 5.x

Install-Package NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse.EF5

EF Core 6.x

Install-Package NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse.EF6

Register the functions in your DbContext's OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    TryParse.Register(modelBuilder);
}

Then call the functions as part of a query:

var result = context.Set<SomeEntity>()
    .Select(e => new { e.Id, e.Value, ValueInt32 = TryParse.Int32(e.Value) })
    .ToList();

This will generate the expected SQL:

SELECT Id, Value, TRY_PARSE(Value As int) As ValueInt32 FROM SomeEntities

Background

TRY_PARSE was added in SQL Server 2012. However, EF Core 3.x does not support calling this function by default.

Whilst EF Core provides methods to map user-defined functions, mapping TRY_PARSE is complicated by the way the arguments are passed. EF Core has great support for traditional functions, where the arguments are passed as a comma-separated list - eg:

dbo.SomeFunction(Foo.Bar, @b, 42)

But for TRY_PARSE, the arguments are separated by spaces, not commas:

TRY_PARSE(Foo.Bar AS int)

To enable this, it was necessary to implement a custom SqlExpression class to represent the parameter. This class needs to override both the Print and Accept methods in order to generate the correct SQL.

internal sealed class TryParseArgumentExpression : SqlExpression
{
    private readonly SqlExpression _sourceExpression;
    private readonly SqlFragmentExpression _asExpression;

    public TryParseArgumentExpression(Type type, SqlExpression sourceExpression, string sqlTypeName) 
        : base(type, sourceExpression.TypeMapping)
    {
        _sourceExpression = sourceExpression ?? throw new ArgumentNullException(nameof(sourceExpression));
        _asExpression = new SqlFragmentExpression($" AS {sqlTypeName}");
    }

    private TryParseArgumentExpression(Type type, SqlExpression sourceExpression, SqlFragmentExpression asExpression) 
        : base(type, sourceExpression.TypeMapping)
    {
        _sourceExpression = sourceExpression ?? throw new ArgumentNullException(nameof(sourceExpression));
        _asExpression = asExpression ?? throw new ArgumentNullException(nameof(asExpression));
    }

    protected override Expression VisitChildren(ExpressionVisitor visitor)
    {
        var newSource = (SqlExpression?)visitor.Visit(_sourceExpression) ?? _sourceExpression;
        var newAsExpression = (SqlFragmentExpression?)visitor.Visit(_asExpression) ?? _asExpression;
        if (Equals(newSource, _sourceExpression) && Equals(newAsExpression, _asExpression)) return this;
        return new TryParseArgumentExpression(Type, newSource, newAsExpression);
    }

    protected override Expression Accept(ExpressionVisitor visitor)
    {
        visitor.Visit(_sourceExpression);
        visitor.Visit(_asExpression);
        return this;
    }

    public override void Print(ExpressionPrinter expressionPrinter)
    {
        expressionPrinter.Visit(_sourceExpression);
        expressionPrinter.Visit(_asExpression);
    }
}

NB: For EF Core 5.x, this expression has to inherit from SqlUnaryExpression to avoid an "Unhandled expression" exception from the new SqlNullabilityProcessor:

internal sealed class TryParseArgumentExpression : SqlUnaryExpression
{
    ...
    public TryParseArgumentExpression(Type type, SqlExpression sourceExpression, string sqlTypeName) 
        : base(ExpressionType.Convert, sourceExpression, type, sourceExpression.TypeMapping)
    {
        ...
    }
    
    private TryParseArgumentExpression(Type type, SqlExpression sourceExpression, SqlFragmentExpression asExpression) 
        : base(ExpressionType.Convert, sourceExpression, type, sourceExpression.TypeMapping)
    {
        ...
    }

The Print method also needs to be changed from public to protected.

It was then possible to use this custom expression, along with an internal attribute which specifies the mapped SQL type name, to register the custom functions:

public static void Register(ModelBuilder modelBuilder)
{
    foreach (var dbFunc in typeof(TryParse).GetMethods(BindingFlags.Public | BindingFlags.Static))
    {
        var attribute = dbFunc.GetCustomAttribute<SqlTypeNameAttribute>();
        if (attribute is null) continue;

        modelBuilder.HasDbFunction(dbFunc).HasTranslation(args =>
        {
            var newArgs = args.ToList();
            newArgs[0] = new TryParseArgumentExpression(dbFunc.ReturnType, newArgs[0], attribute.SqlTypeName);
            return SqlFunctionExpression.Create("TRY_PARSE", newArgs, dbFunc.ReturnType, null);
        });
    }
}

NB: For EF Core 5.x, the SqlFunctionExpression.Create method is no longer supported. The registration code needs to be changed to:

private static readonly bool[] ArgumentsPropagateNullability = { true };

public static void Register(ModelBuilder modelBuilder)
{
    foreach (var dbFunc in typeof(TryParse).GetMethods(BindingFlags.Public | BindingFlags.Static))
    {
        var attribute = dbFunc.GetCustomAttribute<SqlTypeNameAttribute>();
        if (attribute is null) continue;

        modelBuilder.HasDbFunction(dbFunc).HasTranslation(args =>
        {
            var newArgs = args.ToList();
            newArgs[0] = new TryParseArgumentExpression(dbFunc.ReturnType, newArgs[0], attribute.SqlTypeName);
            return new SqlFunctionExpression("TRY_PARSE", newArgs, true, ArgumentsPropagateNullability, dbFunc.ReturnType, null);
        });
    }
}

License

Copyright (c) 2021 Richard Deeming All rights reserved.

This code is free software: you can redistribute it and/or modify it under the terms of either

the Code Project Open License (CPOL) version 1 or later; or
the GNU General Public License as published by the Free Software Foundation, version 3 or later; or
the BSD 2-Clause License;

This code is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.