CODEWITHSUNDEEP
c#linqlinq-to-sql
user2275571
user2275571

Reputation: 25

Expressions in Linq Query

I recently found out that i cannot call any methods from within a linq query. I am trying to write a query that, on the where clause compares two byte arrays. The value on the database is a GUID of type Raw(32) and it is returned as a byte array. This is the record ID for this table. I need to compare it to another byte array. the second byte array could be converted to a string but since i cannot call methods from within linq i was unable to compare.

I tied a custom "Compare" method, i also wrote an extension method. All received an error indicating "LINQ to Entities does not recognize the method"

Here is the code for what i am trying to do. The where clause causes this error: LINQ to Entities does not recognize the method 'Boolean SequenceEqual[Byte] (System.Collections.Generic.IEnumerable1[System.Byte], System.Collections.Generic.IEnumerable1[System.Byte])' method, and this method cannot be translated into a store expression."

EPSGEntities dbContex = new EPSGEntities();  
byte[] byteArray = ParseHex(ViewState["itemID"].ToString());
    var q = (from d in dbContex.EPSG_VSOREJECTS
              where d.SDSRECID.SequenceEqual(byteArray)
              select d).First();

Upvotes: 2

Views: 1585

Answers (2)

jstromwick
jstromwick

Reputation: 1206

What version of EntityFramework are you using? On EF6 I am able to simply do the following against a SQL 2012 table with a varbinary column:

var q = dbContext.EPSG_VSOREJECTS.FirstOrDefault(e => e.SDSRECID == byteArray);

Is the SDSRECID property on EPSGEntities of type byte[]?

The alternative here would be to go to straight Sql to get your object. Something like:

dbContext.Database.SqlQuery<EPSG_VSOREJECT>("SELECT TOP 1 *" +
                                              "FROM dbo.EPSGEntities" +
                                              "WHERE SDSRECID = @byteString",
                                       new SqlParameter
                                       {    
                                         ParameterName = "byteString",
                                         Value = ViewState["itemID"].ToString(),
                                       }).FirstOrDefault();

Linq to Entities in EF is awesome for most queries, but I sometimes drop into sql when I need to do something unsupported, complex, or just fast. Hope this helps!

Upvotes: 1

Tieson T.
Tieson T.

Reputation: 21237

I'm not entirely sure this works, but I've found calling .AsEnumerable() on the IQueryable object set lets me apply pretty much any code I wish:

var q = dbContex.EPSG_VSOREJECTS.
    .AsEnumerable()
    .Where(d => d.SDSRECID.SequenceEqual(byteArray));

Doing so seems to prevent EF from trying to translate the Where() clause into SQL syntax, but I have no real idea what the performance hit would/will be.

This is also using method syntax, since I'm not real familiar with query syntax. HTH.

EDIT:

As some others have noted, you have to be careful with how you add any of the iterative methods (AsEnumerable(), ToList(), etc.) since past that point you are no longer building SQL against your data store. Once you start iterating, EF will execute whatever query has been built up to that point, and from then on you are filtering the result set from the LINQ query.

In this case, I don't know that this can be avoided, unless someone can build the same query as a sproc (which EF can execute on your behalf).

Upvotes: 0

Related Questions