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
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();