Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

EF Core 3.x Support for SQL Server's TRY_PARSE Function

0.00/5 (No votes)
28 Feb 2022 1  
EF Core methods to call SQL Server's TRY_PARSE function
In this project, you will see the methods in EF Core used to call SQL Server's TRY_PARSE function.

Use

Install the NuGet package:

EF Core 3.1.x

PowerShell
Install-Package NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse

EF Core 5.x

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

EF Core 6.x

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

Register the functions in your DbContext's OnModelCreating method:

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

Then call the functions as part of a query:

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

This will generate the expected SQL:

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 - e.g.:

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

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

SQL
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.

C#
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:

C#
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:

C#
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:

C#
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here