CODEWITHSUNDEEP
.netsql-serverentity-frameworklinq-to-entities
Can Poyrazoğlu
Can Poyrazoğlu

Reputation: 34810

Function import from SQL Server to Entity Framework runtime exception

I'm trying to map a stored procedure at my database to a function on EF to build queries within my code with LINQ.

My stored procedure returns/selects (tried BOTH, no difference) an integer value. I can map into a function, and see it in code (with correct input/output types), but whenever I try to execute the query, I get this exception:

LINQ to Entities does not recognize the method 'System.Data.Objects.ObjectResult`1[System.Nullable`1[System.Int32]] HammingDistance(System.Nullable`1[System.Int64], System.Nullable`1[System.Int64])' method, and this method cannot be translated into a store expression.

The LINQ that causes the error is this:

db.HammingDistance(targetPhoto.Analysis.Hash, p.Analysis.Hash).FirstOrDefault().Value < 10

which is in a where clause. Without this part, the query runs with no problems.

Yes, my procedure called HammingDistance takes in two longs and returns an int. I tried to create it as a sql server function instead of a stored procedure too, but then I cannot import it as a function, which is what I want. I have complex queries involving the procedure (and tested on management studio, it DOES work and RETURN the value correctly), there are no nulls neither on both the DB or the code, it's not an issue. I've double checked my procedure IS there and working AFTER the error too, with no changes to the SQL code. But LINQ doesn't recognize this. And finally, YES, I need this to run on SQL Server, I can't handle pulling all the fields from the DB and run on ASP.NET. I know it can be done, and I'm pretty sure this is a configuration issue, as both my DB and website and LINQ queries runs perfectly without calling the HammingDistance, but I just can't tie them at that point.

Upvotes: 2

Views: 2367

Answers (2)

Steve Morgan
Steve Morgan

Reputation: 13091

Turn HammingDistance into a scalar function, then create either a stored procedure to do the select or create a view.

You can import either of these into your model and get your entities back that way.

Alternatively, create a view that adds a new computed column for the Hamming Distance, then you can use the column in your where clause - bit it will add an extra property to your entity.

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

You cannot call stored procedure from linq-to-entities query. It is the same as when using SQL directly - you cannot have stored procedure call as part of SQL SELECT. You must create SQL function instead and import it to your EF model.

Upvotes: 2

Related Questions