"LINQ to Entities does not recognize the method" when Convert to Int 32

Question

Before post this question i try search based on my problem i couldn't find one (may be am not search that well :(, i was trying to convert my string to integer in the linq query i got this exception.

**

LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression

**

Posting full query might be time wasting for you guys so i just dropped the main line where i get stuck

 int intBookingNumber = Convert.ToInt32(Booktime);
    var query =
               (from PROJECTS in db.PROJECTS
                join WOes in db.WOes on PROJECTS.PRJ_ID equals WOes.PRJ_ID
                join SEVTs in db.SEVTs on WOes.SEQNUM equals SEVTs.SEQNUM
                join RSRCEs in db.RSRCEs on SEVTs.RESID equals RSRCEs.RESID
                join PERS in db.PERS on RSRCEs.RECID equals PERS.RECID into PERS_join
                from PERS in PERS_join.DefaultIfEmpty()
                join RESTYPEs in db.RESTYPEs on new { RTYPE = SEVTs.RTYPE } equals new { RTYPE = RESTYPEs.CODE }
                join RESCATs in db.RESCATs on new { RCAT = SEVTs.RCAT } equals new { RCAT = RESCATs.CODE }
                join SEVT_EX in db.SEVT_EX on SEVTs.SESID equals SEVT_EX.SESID into SEVT_EX_join
                from SEVT_EX in SEVT_EX_join.DefaultIfEmpty()
                where
                  (new string[] { "1", "2" }).Contains((PROJECTS.STAT.TrimEnd()).TrimStart()) &&
                  (WOes.STAT.TrimEnd()).TrimStart() == "6" &&
                  ((SEVTs.RESTYPE == 5 ||
                  SEVTs.RESTYPE == 0) &&
                  (RESTYPEs.USER2.Substring(2 - 1, 1) == "F" &&
                  RESTYPEs.USER2.Substring(6 - 1, 1) == "S") &&
                  SEVTs.TYPE == 0) ||
                  (SEVTs.RESTYPE == 4 &&
                  SEVTs.TYPE == 0) &&
                  RESCATs.GROUPID==0 &&
                  RESTYPEs.GROUPID==0 &&
                  (int?)(WOes.INVOICE.TrimStart()).Length > 0 &&
                  WOes.INVOICE.TrimStart() != "PENDING" &&
                  WOes.USERFLAG1 != 1 &&
                //(SEVTs.T_START.TrimStart()) == (Booktime)
                 //Convert.ToInt32(SEVTs.T_START.TrimStart()) >= Convert.ToInt32(Booktime)
                 Convert.ToInt32(SEVTs.T_START) >= intBookingNumber
                orderby
                 PROJECTS.PRJ_ID,
                 WOes.WONUM
                select new
                {
                    PROJECTS.PRJ_ID,
                    PROJECTS.USER3,
                    PROJECTS.USER9,
                    WOes.WONUM,
                    WOes.JOBDESC,
                    SEVTs.SESID,
                    SEVTs.RESTYPE,
                    SEVTs.TYPE,
                    SEVTs.T_START,
                    SEVTs.T_END,
                    SEVTs.MEALEND,
                    SEVTs.MELSTART3,
                    SEVTs.MELSTART2,
                    SEVTs.MELEND2,
                    Column1 = SEVTs.MELSTART2,
                    SEVTs.MELEND3,
                    SEVTs.USER2,
                    SEVTs.SUBACTID,
                    SEVTs.OT_EXEMPT,
                    USER5 = SEVT_EX.USER5,
                    SEVTs.GMT_OFFSET,
                    SEVTs.MEALSTART,
                    SEVTs.STANDARD,
                    RESCATs.USER1,
                    SEVTs.RESID
                });

SEVTs.T_START.TrimStart() and bookingStart both data types are string. Obviously they are getting numbers here . how can i use the logic operator here.

Any help much appreciated.

Find the work around guys, this help me to solve my problem

 **String.Compare(SEVTs.T_START.TrimStart(), Booktime) >= 0**

Look here for your answer: http://stackoverflow.com/questions/5984919/convert-string-to-int-for-ordering-using-linq — Koenyn, Feb 19 '13 at 06:19
@Koenyn that question is lightspeed... all LINQ providers support different operations — Marc Gravell, Feb 19 '13 at 06:20
@Marc Gravell ,yeah i tried Int.Parse, it throws the same exception — Usher, Feb 19 '13 at 06:23
do you mean Convert.ToInt32 in the title, if so please correct it — Vamsi, Feb 19 '13 at 06:23
I think LINQ-to-SQL/LINQ to Entities only supports casting like `(int)SEVTs.T_START` — Shane Andrade, Feb 19 '13 at 06:27
@ShaneAndrade would that cast work? My guess is that it only works on casting numerics. — CodeMonkeyKing, Feb 19 '13 at 06:33
Wouldn't it be also an option to just compare the two strings (with trimming)? — robert.oh., Feb 19 '13 at 06:34
@CodeMonkeyKing the way the expression tree is converted to sql is something like `CONVERT(int, T_START)`, if I remember correctly. — Shane Andrade, Feb 19 '13 at 06:34
For something this complex - I might in jest suggest create a view that casts or trims the strings as needed and then use EF against the view. I can't imagine what the SQL that will be produced by this. You could end up on your Dba's most wanted list :) — CodeMonkeyKing, Feb 19 '13 at 06:48
@ShaneAndrade a simple cast won't pass the compiler. I was able to do a (int)(object) but my guess is this would fail when the expression tree is created. — CodeMonkeyKing, Feb 19 '13 at 07:18
thanks for all the comments guys,our database is third party controlled so we can't create a Store proc or view. — Usher, Feb 19 '13 at 08:37

Usher · Accepted Answer · 2013-03-04T23:58:02.407

0

Found the work around guys, this help me to solve my problem

 **String.Compare(SEVTs.T_START.TrimStart(), Booktime) >= 0**

edited Mar 04 '13 at 23:58

answered Feb 20 '13 at 02:04

Usher

2,146
9
43
81

score 0 · Answer 2 · answered Feb 20 '13 at 05:41

0

Whats the actual DataType of the T_START? if Int and nullable this might be your solution. notice the .Value and .HasValue

Where ...
SEVTs.T_START.HasValue && (SEVTs.T_START.Value >= intBookingNumber)

...

answered Feb 20 '13 at 05:41

spajce

7,044
5
29
44

T_START data type is CHARm, already figured out the solution but not sure is the right way to do but its working for me String.Compare(SEVTs.T_START.TrimStart(), Booktime) >= 0 – Usher Feb 20 '13 at 22:43

"LINQ to Entities does not recognize the method" when Convert to Int 32

2 Answers2