LINQ to Entities ゼロパディングしてソートするOrderByの拡張メソッドを作成したい

Question

前提・実現したいこと

LINQ to Entitiesの拡張メソッドでOrderByメソッドは既にあると思いますが、
指定された項目を頭ゼロパディングした指定桁数でOrderBy句を生成する拡張メソッドを作成したいです。

該当ソースコードような感じで実装したいのですが
DbFunctions.Rightメソッドの第一引数のstringArgumentを動的に設定する方法がわからず困っております。
式木などを利用すればもしかしたらできるかなと考えたのですが理解しきれず・・・
式木以外でも他の方法や代替案、アドバイスなどあればご教授いただますか？
コードはVBで記述しておりますが、C#でご回答いただいてもOKです。

該当のソースコード

Public Function OrderBy(Of TSource)(source As IQueryable(Of TSource), keySelector As Linq.Expressions.Expression(Of Func(Of TSource, String)), length As Integer) As IOrderedQueryable(Of TSource)
    Dim zeroPadString = String.Empty.PadLeft(length, "0"c)
    Return source.OrderBy(Function(x) DbFunctions.Right(zeroPadString + "ここにkeySelectorで指定された「x.Table.OrderNo」や「x.Table.OrderNo」といったものを挿入したい", length))
End Function

補足情報（FW/ツールのバージョンなど）

VisualStudio 2015 Community
ASP.net MVC5
EntityFramework 6.3.0
.Net Framework 4.6.2

Answer 1

Linq to Entities の Linq 式で Transact-SQL に変換できない C# のメソッドに代えて、Transact-SQL に変換できる SqlFunctions クラスのメソッドを利用できませんか？

SqlFunctions Class
https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.sqlserver.sqlfunctions?view=entity-framework-6.2.0

上記には質問者さんが望む Transact-SQL の RIGHT に対応しているメソッドは見当たりませんが、質問者さんの目的に使えるものがないか探してみてはいかがですか？

例えば、以下の記事の SqlFunctions.Replicate が Transact-SQL の REPLICATE を生成して SQL Server に投げてくれるそうです。

SqlFunctions.Replicate(String, Nullable<Int32>) Method
https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.sqlserver.sqlfunctions.replicate?view=entity-framework-6.2.0

質問者さんのコードで利用できるかは分かりませんが、Microsoft のサンプルデーターベース Northwind の Employees テーブルを使って、SqlFunctions.Replicate で少なくともパディングはできることは確認できました。

Enployees テーブルの内容は以下の画像を見てください。　

上記から Visual Studio のウィザードを利用して EDM を生成し、SqlFunctions.Replicate メソッドで FirstName に文字 '0' でパディングを入れるコード例を書いておきます。環境は、VS2015, .NET 4.6.1, EF 6.2 です。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleAppJoinByLinq
{
    class Program
    {
        static void Main(string[] args)
        {
            NORTHWINDEntities context = new NORTHWINDEntities();

            int length = 10;

            var emp = from e in context.Employees
                      select new
                      {
                          Id = e.EmployeeID,

                          // これは System.NotSupportedException: LINQ to Entities does not recognize ... となる
                          //PFname = System.Data.Objects.SqlClient.SqlFunctions.Replicate("0", length - e.FirstName.Length) + e.FirstName

                          // これは OK
                          PFname = System.Data.Entity.SqlServer.SqlFunctions.Replicate("0", length - e.FirstName.Length) + e.FirstName
                      };

            foreach (var e in emp)
            {
                Console.WriteLine($"Id: {e.Id}, PFname: {e.PFname}");
            }

            // 結果は：
            // Id: 1, PFname: 00000Nancy
            // Id: 2, PFname: 0000Andrew
            // Id: 3, PFname: 00000Janet
            // Id: 4, PFname: 00Margaret
            // Id: 5, PFname: 0000Steven
            // Id: 6, PFname: 000Michael
            // Id: 7, PFname: 0000Robert
            // Id: 8, PFname: 00000Laura
            // Id: 9, PFname: 000000Anne

        }
    }
}

コメントにも書きましたが、自分の環境では System.Data.Objects.SqlClient 名前空間の SqlFunctions クラスのメソッドは System.NotSupportedException: LINQ to Entities does not recognize ... となります。質問者さんの環境では分かりませんが注意してください。

Answer 2

SQL Server - System.Data.SqlClient 限定ですが、結果的には同等の並べ替えになるかと

using(var db = new MyDbContext())
{
    var q = db.MyItems
        .OrderBy(i => System.Data.Entity.SqlServer.SqlFunctions.DataLength(i.OrderNo.Replace("0", " ").TrimStart()))
        .ThenBy(i => i.OrderNo.Replace("0", " ").TrimStart())
        .Select(i => new 
        { 
            i.OrderNo, 
            Length = System.Data.Entity.SqlServer.SqlFunctions.DataLength(i.OrderNo.Replace("0", " ").TrimStart()), 
            a = i.OrderNo.Replace("0", " ").TrimStart() 
        })
        .ToArray();
}

Answer 3

LINQ to Entities はプロバイダーを通して LINQ を SQL に直します。
プロバイダーを作ってください。

Entity Framework 6 プロバイダー

Comments

[Zuishin]

拡張メソッドを新規作成するのではなく、ラムダ式で単純に変換することでできそうな気もします。やってみなければわかりませんが。どのデータベースを使っているかによってもかわってくると思います。

[mikupedia]

早速コメントありがとうございます。
プロバイダーを作成するのは敷居が高そうですね・・・
できればDBの種類を意識せずにもう少し上位側でなるべく単純に済ませたいのが本音です。

＞ラムダ式で単純に変換することでできそうな気もします。
このレベルで対応できれば一番いいのですが・・・

[Zuishin]

「VB.NET 0埋め」で検索すれば 0 パディングの方法はみつかると思いますが、そういうことではないんでしょうか？

[mikupedia]

LINQ to EntitiesのOrderBy句でのゼロパディングですのでDatabase側でゼロパディングされるようにしたいのです。
なので最終的にLinq文が「Order By RIGHT('00000' + fieldName,5) ASC」のようなSQL文に展開されることを期待しています。

[Zuishin]

プロバイダによって文字列の連結が SQL に自動的に変換されることもあります。これはやってみなければわかりません。一度やってみてください。できなければやはりプロバイダを作る必要があるかもしれません。

[Zuishin]

https://docs.microsoft.com/ja-jp/dotnet/api/system.linq.iqueryable-1?view=netframework-4.8

具体的には IQueryable<T> の拡張メソッドを作り、それの Provider を見て対応するデータベースのプロバイダかどうか判別し、対応してなければ例外を発生します。
対応していれば式木を作成して Expression に挿入し、次に送ります。

[Zuishin]

SQL Server Express でやってみましたが、string.Format を使った場合、対応していませんでした。また、ToString("00000") も対応していませんでした。一度文字列に直して足りない分を埋めるのは複文になるのでできませんでした。

SQL Server Express の場合は、プロバイダを作るしかなさそうです。ほかの対応状況も似たようなものだと思います。

[Zuishin]

データベースが変更できる場合は、ストアドプロシージャやビューや計算列などを使ってモデルそのものにデータベース側から 0 埋めしたプロパティを作るという方法もあるかもしれません。