why my Linqued query is not getting executed

Question

I have this linqued query

  var moreThen1dayLeavefed = (from LApp in db.LeaveApplications
                                 join Emp in db.Employees
                                on LApp.Employee equals Convert.ToInt32(Emp.EmployeeNumber)
                                 join LBrk in db.LeaveBreakups
                                on LApp.Id equals LBrk.LeaveApplication
                                 where Emp.Team == 8 && LBrk.StartDate.Year == 2015 && LBrk.StartDate.Month == 5 
                                 select new { StartDate = LBrk.StartDate.Day, EndDate = LBrk.EndDate.Day, diff = (DbFunctions.DiffDays(LBrk.StartDate, LBrk.EndDate) + 1) }).ToList();

it gives error LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression. on line 3, i.e

 on LApp.Employee equals Convert.ToInt32(Emp.EmployeeNumber)

as I am converting the string to int during inner join

Possible duplicate of [LINQ to Entities does not recognize the method 'Int32 ToInt32 Converting String to Int](http://stackoverflow.com/questions/18637985/linq-to-entities-does-not-recognize-the-method-int32-toint32-converting-string) — Ivan Stoev, May 11 '16 at 09:52
See also http://stackoverflow.com/search?q=LINQ+to+Entities+does+not+recognize+the+method+%27Int32+ToInt32%28System.String%29%27+ — Ivan Stoev, May 11 '16 at 09:52
I know it's confusing, but LINQ to Entities does not support many of the methods normally available (in LINQ to Objects for instance) even the query compiles w/o error. It's a bad practice to store numbers as strings in the database. Anyway, you can try the opposite conversion `on LApp.Employee.ToString() equals Emp.EmployeeNumber` (it's supported) and see if it works. — Ivan Stoev, May 11 '16 at 10:09
Then you are out of luck. Check the links if you can find a workaround there. — Ivan Stoev, May 11 '16 at 10:21

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

Just saw your related question. Your EmployeeNumber field seems to be filled with fixed size (5) zero left padded string representation of a number. If that's true, you can use the trick from how to sort varchar column containing numeric values with linq lambdas to Entity to solve the issue.

Just replace

on LApp.Employee equals Convert.ToInt32(Emp.EmployeeNumber)

with

on DbFunctions.Right("0000" + LApp.Employee.ToString(), 5) equals Emp.EmployeeNumber

edited May 23 '17 at 11:44

Community

1
1

answered May 11 '16 at 11:42

Ivan Stoev

195,425
15
312
343

But LApp.Employee is a integer field – akash May 11 '16 at 11:57
Yep - the above will convert it to string like "00001" etc. – Ivan Stoev May 11 '16 at 12:06
Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types. – akash May 11 '16 at 12:10
Ok, last attempt - add `ToString()` call as in the update. – Ivan Stoev May 11 '16 at 12:12
getting this exception – akash May 12 '16 at 06:43
LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression. – akash May 12 '16 at 06:43
1

Both my posted code versions execute w/o any error in EF6.1.3, so you probably are using outdated EF version. I give up, good luck. – Ivan Stoev May 12 '16 at 07:18
thanks brother @Ivan Stoev...thank you very much ....I just upgraded my EF 6.0 to 6.1.3 and it worked smoothly..... :) – akash May 12 '16 at 09:37

why my Linqued query is not getting executed

1 Answers1