Entity Framework 'ArrayIndex' is not supported in LINQ to Entities

Question

in my SQL Server DB table, I have the binary column type.

I can't execute the query, why ?

var data = (from x in model.MyTable
            where x.BinaryColumn[0] == 1
            select x).FirstOrDefault();

I get the The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities error

Because the Entity Framework doesn't know how to translate it to TSQL. — xanatos, May 11 '15 at 17:44
This isn't the kind of calculation that can be performed by the SQL server; The only way you'll be able to do this would be to pull the entire table and then filter the table at the client, which isn't ideal. What is the context of this query? Maybe there is another way to structure the query? — Claies, May 11 '15 at 17:48
Depending on how you are using Entity Framework (edx file, code first, ...) perhaps it is possible to define a user defined function to return a scalar (the first byte of the BinaryColumn). In SQL you could retrieve it with a `SUBSTRING(BinaryColumn, 1, 1)` — xanatos, May 11 '15 at 17:59

score 4 · Answer 1 · edited May 23 '17 at 12:21

4

These expressions are translated to SQL queries, and this is one of the things you cant do. Take a look here for more detail: LINQ To Entities doesn't recognize array index

edited May 23 '17 at 12:21

Community

1
1

answered May 11 '15 at 18:02

Jon Koivula

899
1
11
24

score 3 · Answer 2 · answered May 11 '15 at 18:13

In TSQL the SUBSTRING function can be used on binary/varbinary.

Somewhere define:

[DbFunction("SqlServer", "SUBSTRING")]
public static byte[] SubString(byte[] field, int start, int length)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

then

var data = (from x in model.MyTable
            where Substring(x.BinaryColumn, 1, 1) == new byte[] { 1 }
            select x).FirstOrDefault();

haim770 · Answer 3 · 2015-05-11T18:15:13.457

1

If you're willing to change your query a bit, this would work:

var data = (from x in model.MyTable
            where SqlFunctions.CharIndex(new byte[] { 1 }, x.BinaryColumn) == 1
            select x).FirstOrDefault();

See MSDN

edited May 11 '15 at 18:15

answered May 11 '15 at 17:59

haim770

48,394
7
105
133

it would return `1`, not `0` I think, because SQL counts from 1 – xanatos May 11 '15 at 18:02

Entity Framework 'ArrayIndex' is not supported in LINQ to Entities

3 Answers3