5

Any ideas what the problem might be here on an EF DbContext query? As far as I have understood, this should work according to a fellow SO poster.

I've tried Lists, null, not null checks, to no avail. The query does work however, if I remove the null check and just leave the Contains(). However, it is a requirement to return all records if testIDs is null.

var testIDs = new int[] { 1, 3 };
var test = session.All<VendorBooking>(x => testIDs == null || testIDs.Contains(x.VendorServiceID)).ToList();

(session.All simply utilises context.Set<T>.Where())

Exception thrown: 'System.NotSupportedException' in EntityFramework.SqlServer.dll

Additional information: Cannot compare elements of type 'System.Int32[]'. Only primitive types, enumeration types and entity types are supported.

enter image description here

Many thanks

Community
  • 1
  • 1
GONeale
  • 26,302
  • 21
  • 106
  • 149

4 Answers4

5

Something like this maybe. Also, you can extract common parts and make it shorter.

var testIDs = new int[] { 1, 3 };
if (testIDs == null)
{
    var test = session.All<VendorBooking>()
        .ToList();
}
else
{
    var test = session.All<VendorBooking>(x => testIDs.Contains(x.VendorServiceID))
       .ToList();
}
Backs
  • 24,430
  • 5
  • 58
  • 85
  • I see. Or just append to the Where() clause through if/else. Good idea. Thanks. I'm pretty surprised at EF, I would think null checking of arrays should be fundamentally supported. – GONeale Sep 11 '15 at 04:15
  • @GONeale somethimes EF is very strange, yes – Backs Sep 11 '15 at 04:16
  • @GONeale i think, i have an idea, why its happening. In c# (.net) we have collections (arrays, lists and so on), but we have no collections in sql-server. So, linq2sql can not pass collection to server to check it for null – Backs Sep 11 '15 at 04:21
  • I would think in the preparation of the query in EF it would simply recognise the array 'testIDs == null' and know to write it as `1 = 1 OR IN (1,2)` etc.. in the T-SQL. That could be done if the EF team took the feature on I think. – GONeale Sep 11 '15 at 05:03
  • Don't have to do that , You can just do this as I posted in my answer : !testIDs .Any() ... – sasan Mar 12 '22 at 11:27
0

testIds cannot be null, so try this

var testIDs = new int[] { 1, 3 };
var test = session
   .All<VendorBooking>(x => testIDs.Contains(x.VendorServiceID))
   .ToList();

Revised

var results = (testIds == null || testIds.Length == 0)
  ? session.All<VendorBooking>()
  : session.All<VendorBooking>(x => testIDs.Contains(x.VendorServiceID));
var test = results.ToList();
Richard Schneider
  • 34,944
  • 9
  • 57
  • 73
0
    var test = session.All<VendorBooking>(x => testIDs.Contains()==0 || 
    testIDs.Contains(x.VendorServiceID)).ToList();

This is normally working. Don't get confused by the OR operator so only one condition must be true for the full expression to be true. testIDs.Contains(x.VendorServiceID) evaluates to true when any value was passed to testIDs, otherwise testIDs.Contains()==0 is true when null was passed.

Noel Widmer
  • 4,444
  • 9
  • 45
  • 69
Av.Raj
  • 55
  • 9
-1

You can try this :

var testIDs = new int[] { 1, 3 };
var test = session.All<VendorBooking>(x => !testIDs.Any() || 
testIDs.Contains(x.VendorServiceID)).ToList();
sasan
  • 111
  • 11