Oracle LPad equivalent in entity framework core

Question

I am trying to write a query in entity framework core where I am able to LPad the entity. I build up a PredicateBuilder by going through a loop and compare a string to the entity I'm trying to compare against

For example:

Model (Distributors):

public int Id {get;set;}
public decimal DistNo {get;set;}

For each loop in class

var searchPredicate = PredicateBuilder.New();

foreach (var code in codes)
{
   searchPredicate.Or(x => EF.Functions.Like(x.DistNo.ToString(), $"{code}%"));
}

I have tried this:

searchPredicate.Or(x => EF.Functions.Like(System.Data.Entity.DbFunctions.Left("0" + x.DistNo.ToString(), 10), $"{code}%"))

But had no luck

What I'd like to happen is, when comparing "code" to "x.DistNo" is for the "x.DistNo" to be 0 padded on the left until its reached 10 characters. E.g. if x.DistNo in the db was 202930005 I'd want the query to see it as 0202930005 instead.

The oracle query I'm trying to replicate is: select * from distributors where lpad(dist_no, 10, '0') like '2%'

user_5202563 · Answer

The usual path when seeking for a db function in EF Core is first standard CLR type property/method, then EF.Functions extension method (do not use anything from System.Data.Entity namespace, these are for "classic" Entity Framework (a.k.a. EF6) which is completely different from EF Core, and is not used/recognized by EF Core at all).

In this case, string.PadLeft CLR method looks appropriate, but the experiment shows that it is not supported by the EF Core database providers (most probably because MSFT owned SqlServer has no such function, and many design decisions of MSFT owned EF Core are still based on that database). Also there is no EF.Functions extension method, so looks like there is no solution.

But actually there is a solution thanks to the EF Core User-defined function mapping capability, which allows you to relatively easy add support for the desired translation.

For Oracle LPAD, you'll need a custom static class holding the method definition like this

public static class OracleDbFunctions
{
    [DbFunction(IsBuiltIn = true)]
    public static string LPad(this string text, int totalWidth, string padding)
        => throw new InvalidOperationException();
}

then registering it inside you context OnModelCreating override:

modelBuilder.HasDbFunction(() => OracleDbFunctions.LPad(default, default, default));

Then you can use it in your query as any other:

x => EF.Functions.Like(OracleDbFunctions.LPad(x.DistNo.ToString(), "0"), $"{code}%")

It's not a perfect and general solution, since it is not database agnostic, but more like a workaround for unsupported specific database function.

Oracle LPad equivalent in entity framework core

1 Answers