SQL RIGHT function equivalent in Entity framework Core

Question

I'm working with a Net Core project, using entity framework, mysql database and pomelo framework. I need to perform this query, in order to compare the last X characters of a property in my model, against a pattern:

_context.Cars
.Where(c => EF.Functions.Like(c.CarName.ToString().Right(5), pattern))
.ToList();

I want to know if there is any SQL RIGHT function equivalent in Entity framework Core.

Thanks in advance

Answer 1

Update (EF Core 5.0+):

EF Core 5.0 introduces IsBuiltIn property on DbFunctionAttribute and IsBuiltIn(bool) fluent API, so providing translation is no more necessary. The minimal mapping is the same as in EF Core 2.x, just

[DbFunction("RIGHT", "")]

is replaced with

[DbFunction("RIGHT", IsBuiltIn = true, IsNullable = true)]

EF Core 5 also allows Configuring nullability of user-defined function based on its arguments, but it can only be done fluently, so you might consider using explicit mapping rather than generic code. e.g.

public static class MyDbFunctions
{
    public static string Right(this string s, int length)
        => throw new InvalidOperationException();

    public static void Register(ModelBuilder modelBuider)
    {
        var fb = modelBuilder.HasDbFunction(() => Right(default, default))
            .HasName("RIGHT").IsBuiltIn(true).IsNullable(true);
        fb.HasParameter("s").PropagatesNullability(true);
    }
}

---

Update (EF Core 3.0+):

Starting with Ef Core 3.0, empty string schema is treated the same as null, i.e. prepends the default schema to to function name. This way, in case if you want to add built-in function, you have to provide "translation" (weird decision).

So you need to add

using Microsoft.EntityFrameworkCore.Query.SqlExpressions;

and modify the code as follows

modelBuider.HasDbFunction(dbFunc).HasTranslation(args =>
    SqlFunctionExpression.Create(dbFunc.Name, args, dbFunc.ReturnType, null));                    

---

Original:

Since currently there is neither CLR string nor EF.Functions method called Right, the answer is that EF Core currently does not provide equivalent of SQL RIGHT function.

Fortunately EF Core allows you to add it using the EF Core 2.0 introduced Database scalar function mapping.

For instance, add the following class:

using System;
using System.Linq;

namespace Microsoft.EntityFrameworkCore
{
    public static class MyDbFunctions
    {
        [DbFunction("RIGHT", "")]
        public static string Right(this string source, int length)
        {
            if (length < 0) throw new ArgumentOutOfRangeException(nameof(length));
            if (source == null) return null;
            if (length >= source.Length) return source;
            return source.Substring(source.Length - length, length);
        }

        public static void Register(ModelBuilder modelBuider)
        {
            foreach (var dbFunc in typeof(MyDbFunctions).GetMethods().Where(m => Attribute.IsDefined(m, typeof(DbFunctionAttribute))))
                modelBuider.HasDbFunction(dbFunc);
        }
    }
}

(Later on you can add more functions like this if needed).

Then add call to Register from your context OnModelCreating override:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // ...
    MyDbFunctions.Register(modelBuilder);
    // ...
}

And you are done. Now you should be able to use the desired:

_context.Cars
.Where(c => EF.Functions.Like(c.CarName.ToString().Right(5), pattern))
.ToList();