Click here to Skip to main content
15,393,169 members
Articles / Database Development / SQL Server
Project
Posted 28 Feb 2022

Stats

3.7K views
10 downloads
3 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
28 Feb 2022GPL32 min read
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
PowerShell
Install-Package NevaleeBusinessSolutions.EntityFrameworkCore.SqlServer.TryParse

EF Core 5.x

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

EF Core 6.x

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

Register the functions in your DbContext's OnModelCreating method:

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

Then call the functions as part of a query:

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

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

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

SQL
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#
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(<span class="pl-pds">$" AS {sqlTypeName}"</span>);
    }

    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#
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#
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(<span class="pl-pds">"TRY_PARSE"</span>, 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#
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(<span class="pl-pds">"TRY_PARSE"</span>, 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, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

Richard Deeming
Software Developer CodeProject
United Kingdom United Kingdom
I started writing code when I was 8, with my trusty ZX Spectrum and a subscription to "Input" magazine. Spent many a happy hour in the school's computer labs with the BBC Micros and our two DOS PCs.

After a brief detour into the world of Maths, I found my way back into programming during my degree via free copies of Delphi and Visual C++ given away with computing magazines.

I went straight from my degree into my first programming job, at Trinet Ltd. Eleven years later, the company merged to become ArcomIT. Three years after that, our project manager left to set up Nevalee Business Solutions, and took me with him. Since then, we've taken on four more members of staff, and more work than you can shake a stick at. Smile | :)

Between writing custom code to integrate with Visma Business, developing web portals to streamline operations for a large multi-national customer, and maintaining RedAtlas, our general aviation airport management system, there's certainly never a dull day in the office!

Outside of work, I enjoy real ale and decent books, and when I get the chance I "tinkle the ivories" on my Technics organ.

Comments and Discussions

 
-- There are no messages in this forum --