質問編集履歴
3
タグを追加 \(LINQ to Entities, Entity Framework\)
test
CHANGED
|
File without changes
|
test
CHANGED
|
File without changes
|
2
StackOverflowで見つけた内容
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -424,6 +424,40 @@
|
|
|
424
424
|
|
|
425
425
|
|
|
426
426
|
|
|
427
|
+
### 追記：StackOverflowで見つけた内容
|
|
428
|
+
|
|
429
|
+
|
|
430
|
+
|
|
431
|
+
http://stackoverflow.com/questions/10642421/using-equal-and-not-equal-in-a-linq-join
|
|
432
|
+
|
|
433
|
+
|
|
434
|
+
|
|
435
|
+
> A join clause performs an equijoin. In other words, you can only base matches on the equality of two keys.
|
|
436
|
+
|
|
437
|
+
> Other types of comparisons such as "greater than" or "not equals" are not supported.
|
|
438
|
+
|
|
439
|
+
|
|
440
|
+
|
|
441
|
+
|
|
442
|
+
|
|
443
|
+
> join節が等価結合を実行します。 言い換えれば、2つのキーが等しいかどうかに基づいて照合を行うことができます。
|
|
444
|
+
|
|
445
|
+
> 「より大きい」または「等しくない」などの他のタイプの比較はサポートされていません。
|
|
446
|
+
|
|
447
|
+
|
|
448
|
+
|
|
449
|
+
上記の質問内容は `LEFT JOIN AAA ON (AAA.RoleName <> 'Admin')` みたいなのはできないの？という内容で 私の問題とは少し違うのですが… 系統的には似てるかなと。
|
|
450
|
+
|
|
451
|
+
|
|
452
|
+
|
|
453
|
+
結局 単純な等価評価しかサポートしていないということでしょうか。
|
|
454
|
+
|
|
455
|
+
|
|
456
|
+
|
|
457
|
+
＃ EntityFramework を使う人は適用期間を見たりするSQLとかって組まないのでしょうか…？
|
|
458
|
+
|
|
459
|
+
|
|
460
|
+
|
|
427
461
|
###一応考えた方法
|
|
428
462
|
|
|
429
463
|
|
1
IEquatableを実装してみました
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -248,6 +248,182 @@
|
|
|
248
248
|
|
|
249
249
|
|
|
250
250
|
|
|
251
|
+
###追記：IEquatable を実装してみた版（うまくいかず）
|
|
252
|
+
|
|
253
|
+
|
|
254
|
+
|
|
255
|
+
```VBnet
|
|
256
|
+
|
|
257
|
+
Public Class JoinParamMainAndAAA : Implements IEquatable(Of Object)
|
|
258
|
+
|
|
259
|
+
Public Property ID As String = Nothing
|
|
260
|
+
|
|
261
|
+
Public Property StartYmd As String = Nothing
|
|
262
|
+
|
|
263
|
+
Public Property EndYmd As String = Nothing
|
|
264
|
+
|
|
265
|
+
Public Property Ymd As String = Nothing
|
|
266
|
+
|
|
267
|
+
Public Enum TableTypes
|
|
268
|
+
|
|
269
|
+
Main
|
|
270
|
+
|
|
271
|
+
AAA
|
|
272
|
+
|
|
273
|
+
End Enum
|
|
274
|
+
|
|
275
|
+
Public Property TableType As TableTypes
|
|
276
|
+
|
|
277
|
+
|
|
278
|
+
|
|
279
|
+
Public Overloads Function Equals(ByVal obj As Object) As Boolean Implements IEquatable(Of Object).Equals
|
|
280
|
+
|
|
281
|
+
' 想定している型でなければ抜ける
|
|
282
|
+
|
|
283
|
+
If obj Is Nothing OrElse obj.GetType() IsNot GetType(JoinParamMainAndAAA) Then
|
|
284
|
+
|
|
285
|
+
Return False
|
|
286
|
+
|
|
287
|
+
End If
|
|
288
|
+
|
|
289
|
+
Dim chk = CType(obj, JoinParamMainAndAAA)
|
|
290
|
+
|
|
291
|
+
|
|
292
|
+
|
|
293
|
+
Dim m As JoinParamMainAndAAA = Nothing
|
|
294
|
+
|
|
295
|
+
Dim a As JoinParamMainAndAAA = Nothing
|
|
296
|
+
|
|
297
|
+
Select Case(Me.TableType)
|
|
298
|
+
|
|
299
|
+
Case TableTypes.Main:
|
|
300
|
+
|
|
301
|
+
' 自分がMainの場合は相手はAAA
|
|
302
|
+
|
|
303
|
+
If chk.TableType = TableTypes.AAA Then
|
|
304
|
+
|
|
305
|
+
m = Me
|
|
306
|
+
|
|
307
|
+
a = chk
|
|
308
|
+
|
|
309
|
+
End If
|
|
310
|
+
|
|
311
|
+
Case TableTypes.AAA:
|
|
312
|
+
|
|
313
|
+
' 自分がAAAの場合は相手はMain
|
|
314
|
+
|
|
315
|
+
If chk.TableType = TableTypes.Main Then
|
|
316
|
+
|
|
317
|
+
m = chk
|
|
318
|
+
|
|
319
|
+
a = Me
|
|
320
|
+
|
|
321
|
+
End If
|
|
322
|
+
|
|
323
|
+
End Select
|
|
324
|
+
|
|
325
|
+
If m Is Nothing OrElse a Is Nothing Then Return False
|
|
326
|
+
|
|
327
|
+
|
|
328
|
+
|
|
329
|
+
' 比較
|
|
330
|
+
|
|
331
|
+
If m.ID = a.ID AndAlso
|
|
332
|
+
|
|
333
|
+
a.StartYmd <= m.Ymd AndAlso m.Ymd <= a.EndYmd Then
|
|
334
|
+
|
|
335
|
+
Return True
|
|
336
|
+
|
|
337
|
+
End If
|
|
338
|
+
|
|
339
|
+
|
|
340
|
+
|
|
341
|
+
Return False
|
|
342
|
+
|
|
343
|
+
End Function
|
|
344
|
+
|
|
345
|
+
|
|
346
|
+
|
|
347
|
+
Public Overrides Function GetHashCode() As Integer
|
|
348
|
+
|
|
349
|
+
Return CType(Me.ID.GetHashCode() ^ Me.Ymd.GetHashCode() ^ Me.StartYmd.GetHashCode() ^ Me.EndYmd.GetHashCode(), Integer)
|
|
350
|
+
|
|
351
|
+
End Function
|
|
352
|
+
|
|
353
|
+
End Class
|
|
354
|
+
|
|
355
|
+
```
|
|
356
|
+
|
|
357
|
+
|
|
358
|
+
|
|
359
|
+
```VBnet
|
|
360
|
+
|
|
361
|
+
...
|
|
362
|
+
|
|
363
|
+
Group Join a In dbContext.AAA On New JoinParamMainAndAAA With {
|
|
364
|
+
|
|
365
|
+
.TableType = JoinParamMainAndAAA.TableTypes.Main,
|
|
366
|
+
|
|
367
|
+
.ID = m.ID,
|
|
368
|
+
|
|
369
|
+
.Ymd = m.Ymd,
|
|
370
|
+
|
|
371
|
+
.StartYmd = Nothing,
|
|
372
|
+
|
|
373
|
+
.EndYmd = Nothing
|
|
374
|
+
|
|
375
|
+
} Equals New JoinParamMainAndAAA With {
|
|
376
|
+
|
|
377
|
+
.TableType = JoinParamMainAndAAA.TableTypes.AAA,
|
|
378
|
+
|
|
379
|
+
.ID = a.ID,
|
|
380
|
+
|
|
381
|
+
.Ymd = Nothing,
|
|
382
|
+
|
|
383
|
+
.StartYmd = a.StartYmd,
|
|
384
|
+
|
|
385
|
+
.EndYmd = a.EndYmd
|
|
386
|
+
|
|
387
|
+
}
|
|
388
|
+
|
|
389
|
+
...
|
|
390
|
+
|
|
391
|
+
|
|
392
|
+
|
|
393
|
+
' 不要な値にもNothingをセットしているのは、そうしないと以下のエラーが出た為
|
|
394
|
+
|
|
395
|
+
' 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.
|
|
396
|
+
|
|
397
|
+
```
|
|
398
|
+
|
|
399
|
+
|
|
400
|
+
|
|
401
|
+
実際に発行されたSQLを確認したところ、LEFT JOIN の結合が 1 = 0 となっていました。
|
|
402
|
+
|
|
403
|
+
というかEqualsの先頭にブレイクポイントをはっても止まりませんでしたが… 実装がおかしい？
|
|
404
|
+
|
|
405
|
+
|
|
406
|
+
|
|
407
|
+
```SQL
|
|
408
|
+
|
|
409
|
+
SELECT
|
|
410
|
+
|
|
411
|
+
1 AS [C1],
|
|
412
|
+
|
|
413
|
+
[Extent1].[ID] AS [ID],
|
|
414
|
+
|
|
415
|
+
[Extent2].[StartYmd] AS [StartYmd],
|
|
416
|
+
|
|
417
|
+
[Extent2].[EndYmd] AS [EndYmd]
|
|
418
|
+
|
|
419
|
+
FROM [dbo].[MAIN] AS [Extent1]
|
|
420
|
+
|
|
421
|
+
LEFT OUTER JOIN [dbo].[AAA] AS [Extent2] ON 1 = 0
|
|
422
|
+
|
|
423
|
+
```
|
|
424
|
+
|
|
425
|
+
|
|
426
|
+
|
|
251
427
|
###一応考えた方法
|
|
252
428
|
|
|
253
429
|
|