12

I am using Entity Framework, and I have a line of code that is taking a var and translating it back to an iint for the database.

var record = context.enrollments.SingleOrDefault
  (row => row.userId == int.Parse(UserID) && row.classId == int.Parse(ClassID));

Whenever I try to run it I receive rhis error. "LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression."

I have tried this as well

 var record = context.enrollments.FirstOrDefault
  (row => row.userId == Convert.ToInt32(UserID) 
  && row.classId == Convert.ToInt32(ClassID));

and all I receive is this error message, "LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression

and finally I have tried this as well, which I know is unusual, but it has worked in the past for similar situations.

var record = context.enrollments.SingleOrDefault
  (row => row.userId == CommonDLL.Sanitize<int>.ConvertType(UserID) 
  && row.classId == CommonDLL.Sanitize<int>.ConvertType(ClassID));

In which I get this error. As you can see I have tried seveal different things and nothing is working, so any help would be great.

SinceForever
  • 232
  • 1
  • 2
  • 12
  • If I remember correctly, you _should_ be able to replace `int.Parse` with `Convert.ToInt32` to make it work. – Joachim Isaksson Apr 22 '14 at 04:52
  • I should have mentioned this in the comments, but I have tried that and I receive this error message, "{"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression."}" – SinceForever Apr 22 '14 at 12:17
  • Did you try using the full namespace like `System.Convert.ToInt32`? I don't if this is specifically something that linq doesn't support. Just guessing here. – Bensonius Apr 24 '14 at 14:30
  • Actually, there is already a question regarding this, with an answer: http://stackoverflow.com/questions/7399602/convert-string-to-int-in-linq-to-entities and here http://stackoverflow.com/questions/13887296/linq-to-entities-does-not-recognize-the-method-int32-int32system-string-meth – Bensonius Apr 24 '14 at 14:47

3 Answers3

20

in Linq to Entity, you should use the methods in your query which is supported by your provider to convert them to expression tree to run on your Data Base side.

all providers must support some methods by default called Canonical Functions (Read More Here), and also you can define your user defined function and stored procedure as edm functions to use in linq query (Read More Here) and (Here).

in addition you can use methods which is supported by providers and can be converted to expression tree which are in EntityFunctions and SqlFunctions.

and finally about your question, you can convert UserID and ClassID before your query, like this:

var UID = int.Parse(UserID);
var CID = int.Parse(ClassID);
var record = context.enrollments.SingleOrDefault
    (row => row.userId == UID && row.classId == CID);
Amir Sherafatian
  • 2,083
  • 2
  • 20
  • 32
1

This works for me:

  var iId = int.Parse(TextBox1.Text);
 var item = db.Items.Where(p => p.ItemID == iId ).FirstOrDefault();

To make that work you should convert your textbox value outside of the lambda expression.

Romano Zumbé
  • 7,893
  • 4
  • 33
  • 55
Nur Uddin
  • 1,798
  • 1
  • 28
  • 38
-2

You can use AsEnumerable() as follows:

var record = context.enrollments.AsEnumerable().FirstOrDefault(row => row.userId == Convert.ToInt32(UserID) && row.classId == Convert.ToInt32(ClassID));
colourCoder
  • 1,394
  • 2
  • 11
  • 18
Yogesh Bansal
  • 1
  • 1
    There is already valid answer, and yours, with invalid code, is not working as you can see in others comment? – Leszek P Sep 24 '20 at 12:42
  • 1
    Sorry, very bad solution, `context.enrollments.AsEnumerable()` pulls all data into memory and only then selects just one. Be careful with this `AsEnumerable()` "fix". – Gert Arnold Sep 24 '20 at 15:02