SQL RIGHT function equivalent in Entity framework Core

.net-core c# entity-framework-core mysql

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

1
2
9/7/2018 7:22:40 AM

Accepted Answer

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();
8
9/7/2018 1:19:50 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow