Linq to Entity compare two string in Where clause

1
votes

I've got an error:

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

The piece of code:

plist= plist.Where(p => Convert.ToInt(p.Period) >= syslockPeriod);

p.Period example: 201206

pList is IQueryable. p.Period is string typed. sysLockPeriod is int.

How to fix it?

3
c#linqlinq-to-entities
You can't use Convert.ToInt... in linq2entities. By the way, why is Period a string, if it's an int ? – Raphaël Althaus
It's not int, because in Sql Server Database initially Period is string typed. I don't know why it's not an int )) – loviji
Well, it i MUST be an int, change it to int in Sql Server, maybe ? – Raphaël Althaus
Workarounds for this kind of issue don't make any sense, change that string to be int and save yourself the pain. – Alex

3 Answers

0
votes

LINQ to entities will try to translate Convert.ToInt32() into SQL instructions, because it's not one of the known methods it can translate then it'll generate that error.

You have some workaround but for all of them you have to change your code. Let's see what you can do (in order of preference, for me).

  • Convert Period property/field to the proper type. If it's an integer type then why you store it in your DB as a string?
  • Create a function (in your database) to perform the conversion and use a calculated property (or a view). LINQ to Entities will now have a true column of the proper type (no need to convert) or a function to call (on SQL side) to do the conversion. See this example on MSDN about how to call custom database functions.
  • Convert IQueryable to IList with ToList() before Where(), now the where clause will be executed locally and not on the database (this may be something terrible to do if the list is pretty big).
0
votes

Do not convert that syslockPeriod to a string outside the LINQ query. It would help to get rid of the error (if you also remove the "Convert.ToInt"), but would return wrong results. This would give a "true" result: "12" < "2".

The correct solution would be to convert that string-column "Period" to an integer column.

0
votes

This is a common problem in LINQ because you cannot use normal functions in the LINQ expression. (This allows LINQ to do lazy evaluation and reduces state related problems that can occur from invoking functions). Fortunately, we can work around this problem by creating a business object to do the conversions for you in the accessors, or use Linq to SQL which will convert it.

I prefer the former, as it is good practice to create the business objects as it encouraged reuse.

See the excellent post at http://mosesofegypt.net/post/LINQ-to-Entities-Workarounds-on-what-is-not-supported.aspx for samples.