Comparing byte array in Entity Framework where clause

Question

Is it possible to compare a byte array in the where clause using Entity Framework?

I've got a list of bytes like this:

List<byte[]> VisitorIDList

I need to pull some data like this:

var VisitorList = context.Visitors
     .Where(a => VisitorIDList.Contains(a.VisitorID))
     .ToList();

The VisitorID field is interpreted as a byte[] by EF. I can't use the SequenceEqual() method as that doesn't translate to SQL and Contains won't match the records. So am I just SOL on using EF for this?

I know I could do something like this:

var VisitorList = context.Visitors
     .ToList()
     .Where(a => VisitorIDList.Any(b => b.SequenceEqual(a.VisitorID)))
     .ToList();

But obviously that is not practical. I'm using C#, .NET 4.5 and EF 6.

Dai · Accepted Answer · 2018-11-25 01:02:18Z

5

Entity Framework exhibits the correct behavior when you call Contains as you describe.

Given an entity:

public class Visitor
{
    public int Id { get; set; }

    [MaxLength(2)]
    public byte[] VisitorId { get; set; }
}

The following Linq to Entities query:

var visitorIdList = new byte[][]
    {
        new byte[] { 2, 2 },
        new byte[] { 4, 4 },
    }.ToList();

var visitors = context.Visitors
    .Where(v => visitorIdList.Contains(v.VisitorId))
    .ToList();

Generates this SQL (reformatted for readability):

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[VisitorId] AS [VisitorId]
FROM
    [dbo].[Visitors] AS [Extent1]
WHERE
    (
        [Extent1].[VisitorId] IN ( 0x0202 ,  0x0404 )
    )
    AND
    (
        [Extent1].[VisitorId] IS NOT NULL
    )

Your assertion that "The VisitorID field is interpreted as a byte[] by EF." may be incorrect.

It's valuable to note that Entity framework translates the c# equality operator into the SQL Equals operator. This behavior can be a little unexpected and create confusion.

enter image description here

edited Nov 25, 2018 at 1:02

Dai

157k31 gold badges314 silver badges435 bronze badges

answered Sep 29, 2014 at 16:15

rtev

1,12212 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Over a year ago

Strangely, this does not in work the in-memory database. So while the actual software functions, the test cases fail.

Andreas Gyr Over a year ago

@Mike integration tests should always test to a database like in production. For unit tests, you can abstract your db access as a repository which then can be mocked.

Collectives™ on Stack Overflow

Comparing byte array in Entity Framework where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related