LINQ to Entities : LEFT JOIN の結合条件にテーブル同士の大小比較

Question

###前提・実現したいこと

SQL (SQL Server) で書く際、LEFT JOIN の結合条件に テーブル同士の値で大小比較 を入れているケースがあるのですが、
これを LINQ to Entities (VB.NET) で書きたいです。

SELECT *
FROM MAIN m
LEFT JOIN AAA a ON (
    a.ID = m.ID
    AND a.IsDeleted = 0
    AND a.BeginYmd <= m.Ymd AND m.Ymd <= a.EndYmd  -- ★こういう条件
)

###発生している問題

LEFT JOIN の結合条件に「テーブル同士の値で大小比較」を指定する方法が分かりません。

###試したソースコード

以下の書き方だと、★の部分で

'Equals' の両辺で、少なくとも 1 つの範囲変数を参照しなければなりません。
'Equals' 演算子の一方で 1 つ以上の範囲変数 'm' を参照し、他方で 1 つ以上の範囲変数 'a' を参照する必要があります。

というエラーになります。

Dim query1 = (
    From m In dbContext.MAIN
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer),
        .CheckYmd = True
    } Equals New With {
        a.ID,
        a.IsDeleted,
        .CheckYmd = (a.BeginYmd <= m.Ymd AndAlso m.Ymd <= a.EndYmd)    ← ★ m.*** でコンパイルエラー
    }
    Into _a = Group From a In _a.DefaultIfEmpty()
    Select New With {
        .ID       = m.ID,
        .StoreId  = a.StoreID,
        .StartYmd = a.StartYmd,
        .EndYmd   = a.EndYmd
    }
)

リテラル値 (IsDeletedの部分) を指定するのと同じよう名前を付けてあげればいけるかと思ったのですが、違うようでした。
検索してみたのですが、解決方法と思われるものを見つけられませんでした。

---

↓ これは通る

    ...
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer),
        .CheckYmd = True
    } Equals New With {
        a.ID,
        a.IsDeleted,
        .CheckYmd = True    ← ★リテラル値なら問題ない
    }
    ...

↓ これも通る

    ...
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer),
        .CheckYmd = True
    } Equals New With {
        a.ID,
        a.IsDeleted,
        .CheckYmd = (a.BeginYmd <> "")    ← ★a の値を見るのも問題ない
    }
    ...

↓ これは駄目

    ...
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer),
        .CheckYmd = True
    } Equals New With {
        a.ID,
        a.IsDeleted,
        .CheckYmd = (m.Ymd <> "")    ← ★ここで m を見ようとすると駄目
    }
    ...

↓ これは駄目

    ...
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer),
        .CheckYmd = (a.Ymd <> "")    ← ★左辺と右辺を入れ替えると、逆に m はOKだが a が駄目になる
    } Equals New With {
        a.ID,
        a.IsDeleted,
        .CheckYmd = True
    }
    ...

なんとなく駄目なパターンはわかったのですが、どうすれば目的の条件で抽出できるのかわかりません。

###追記：IEquatable を実装してみた版（うまくいかず）

Public Class JoinParamMainAndAAA : Implements IEquatable(Of Object)
    Public Property ID As String = Nothing
    Public Property StartYmd As String = Nothing
    Public Property EndYmd As String = Nothing
    Public Property Ymd As String = Nothing
    Public Enum TableTypes
        Main
        AAA
    End Enum
    Public Property TableType As TableTypes

    Public Overloads Function Equals(ByVal obj As Object) As Boolean  Implements IEquatable(Of Object).Equals
        ' 想定している型でなければ抜ける
        If obj Is Nothing OrElse obj.GetType() IsNot GetType(JoinParamMainAndAAA) Then
            Return False
        End If
        Dim chk = CType(obj, JoinParamMainAndAAA)

        Dim m As JoinParamMainAndAAA = Nothing
        Dim a As JoinParamMainAndAAA = Nothing
        Select Case(Me.TableType)
            Case TableTypes.Main:
                ' 自分がMainの場合は相手はAAA
                If chk.TableType = TableTypes.AAA Then
                    m = Me
                    a = chk
                End If
            Case TableTypes.AAA:
                ' 自分がAAAの場合は相手はMain
                If chk.TableType = TableTypes.Main Then
                    m = chk
                    a = Me
                End If
        End Select
        If m Is Nothing OrElse a Is Nothing Then Return False

        ' 比較
        If m.ID = a.ID AndAlso
           a.StartYmd <= m.Ymd AndAlso m.Ymd <= a.EndYmd Then
            Return True
        End If

        Return False
    End Function

    Public Overrides Function GetHashCode() As Integer
        Return CType(Me.ID.GetHashCode() ^ Me.Ymd.GetHashCode() ^ Me.StartYmd.GetHashCode() ^ Me.EndYmd.GetHashCode(), Integer)
    End Function
End Class

    ...
    Group Join a In dbContext.AAA On New JoinParamMainAndAAA With {
        .TableType = JoinParamMainAndAAA.TableTypes.Main,
        .ID = m.ID,
        .Ymd = m.Ymd,
        .StartYmd = Nothing,
        .EndYmd = Nothing
    } Equals New JoinParamMainAndAAA With {
        .TableType = JoinParamMainAndAAA.TableTypes.AAA,
        .ID = a.ID,
        .Ymd = Nothing,
        .StartYmd = a.StartYmd,
        .EndYmd = a.EndYmd
    }
    ...

    ' 不要な値にもNothingをセットしているのは、そうしないと以下のエラーが出た為
    ' The type 'TestTest.JoinParamMainAndAAA' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

実際に発行されたSQLを確認したところ、LEFT JOIN の結合が 1 = 0 となっていました。
というかEqualsの先頭にブレイクポイントをはっても止まりませんでしたが… 実装がおかしい？

SELECT 
    1 AS [C1], 
    [Extent1].[ID] AS [ID], 
    [Extent2].[StartYmd] AS [StartYmd], 
    [Extent2].[EndYmd] AS [EndYmd]
    FROM  [dbo].[MAIN] AS [Extent1]
    LEFT OUTER JOIN [dbo].[AAA] AS [Extent2] ON 1 = 0

追記：StackOverflowで見つけた内容

http://stackoverflow.com/questions/10642421/using-equal-and-not-equal-in-a-linq-join

A join clause performs an equijoin. In other words, you can only base matches on the equality of two keys.
Other types of comparisons such as "greater than" or "not equals" are not supported.

join節が等価結合を実行します。 言い換えれば、2つのキーが等しいかどうかに基づいて照合を行うことができます。
「より大きい」または「等しくない」などの他のタイプの比較はサポートされていません。

上記の質問内容は LEFT JOIN AAA ON (AAA.RoleName <> 'Admin') みたいなのはできないの？という内容で 私の問題とは少し違うのですが… 系統的には似てるかなと。

結局 単純な等価評価しかサポートしていないということでしょうか。

＃ EntityFramework を使う人は適用期間を見たりするSQLとかって組まないのでしょうか…？

###一応考えた方法

Where 句で見る…？ というのを一応考えたのですが、他に方法がないものかと思い質問させて頂きました。

Dim query1 = (
    From m In dbContext.MAIN
    Group Join a In dbContext.AAA On New With {
        m.ID,
        .IsDeleted = CType(0, Integer)
    } Equals New With {
        a.ID,
        a.IsDeleted
    }
    Into _a = Group From a In _a.DefaultIfEmpty()
    Where (a.ID = Nothing OrElse (a.BeginYmd <= m.Ymd AndAlso m.Ymd <= a.EndYmd))
    Select New With {
        .ID       = m.ID,
        .StoreId  = a.StoreID,
        .StartYmd = a.StartYmd,
        .EndYmd   = a.EndYmd
    }
)

###補足情報(言語/FW/ツール等のバージョンなど)
開発環境等

Windows10
Visual Studio 2013
.NET Framework 4
VB.NET

足りない情報等ありましたらご指摘ください。

御存知の方がいらっしゃいましたらよろしくお願いします。

Answer 1

LINQ to Entities では単一テーブルの絞込と抽出だけ行って、結合などは一度配列なりListにしてから行うのが吉です。

Comments

[sk_3122]

なるほど… 日付をもっていて、指定日以降の～ みたいな取り方をしたかったのですが
そういう使い方にはあまり向いていなさそうですね…
こういう場合は生SQL投げようかな…

ありがとうございます ´ `

[hihijiji]

Entity Framework は好きで良く使ってますが、Entity Framework を使うためにはデータモデリングの時点で Entity Framework 向けに設計しないと、まず使い物にならないんですよね。

[sk_3122]

そういうことなのですね。
今回「既存システムのSQL部分を EntityFramework にできるならしたい」と思って色々書いてみていたのですが、
単純な結合で使えそうなところでは使って、複雑なSQLの部分は生SQL... みたいに使い分ける必要がありそうですね。
使えるケースであれば、すごく便利で私も好きです^^

Answer 2

IEquatable<T>.Equalsを実装して欲しい条件の時にTrueを返すようにしてはどうでしょう
確認していないのでできないかもしれませんが..

Comments

[sk_3122]

回答ありがとうございます。
IEquatableを継承して Equals を実装してみたのですが、コンパイルは通るようになったのですがうまくいきませんでした。
（質問内容に追記しました）

質問投下後も色々調べていたのですが、やはり LINQ to Entities は等価評価はできるけど複雑な条件（<> など）はできないよ！みたいな記事をいくつか見かけました。単純な JOIN 以外は向かないということなのでしょうか ´ `

[dojikko]

EntityFrameworkのクエリビルダがアホなのかもですね
私ではわかりませんが、式木からクエリを生成している部分に手を入れられればなんとかなる気もしないでもない

[dojikko]

まあそこまでするならSQLを投げた方がってなりますよね