Linq与实体比较Where子句中的两个字符串[英] Linq to Entity compare two string in Where clause




linq到实体不识别方法" int32 toint32(system.string)"方法,并且该方法无法转换为存储表达式.


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


plist是 iQueryable . P.Period是字符串键入的. syslockperiod是int.





  • 将Period属性/字段转换为适当的类型.如果是整数类型,那么为什么将其存储在DB中?
  • 创建一个函数(在数据库中)以执行转换并使用计算的属性(或视图). LINQ到实体现在将具有适当类型的 true 列(无需转换)或要调用的函数(在SQL侧)进行转换.请参阅MSDN上有关如何调用自定义数据库功能.
  • 在Where()之前使用ToList()转换为IList,现在将在本地执行where子句(如果列表非常大,这可能会很可怕) .


请勿将该syslockPeriod转换为LINQ查询外的字符串.这将有助于摆脱错误(如果您还删除了"转换",但会返回错误的结果.这将给出一个"真"结果:"12" < "2".



这是LINQ中的一个常见问题,因为您不能在LINQ表达式中使用普通功能. (这使LINQ可以进行懒惰的评估,并减少调用功能可能发生的状态相关问题).幸运的是,我们可以通过创建一个业务对象来为您在配件中进行转换,或者使用LINQ将其转换为SQL来解决此问题.



请参阅 htttp :// 用于样品.



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?


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).


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.


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 for samples.