实体框架/Linq EXpression 从字符串转换为 int [英] Entity Framework/Linq EXpression converting from string to int

问题描述

我有一个像这样的表达式:

I have an Expression like so:

var values = Enumerable.Range(1,2);

return message => message.Properties.Any(
    p => p.Key == name 
    && int.Parse(p.Value) >= values[0] 
    && int.Parse(p.Value) <= values[1]);

这编译得很好，但是当它命中数据库时，它抛出异常 'LINQ to Entities 无法识别方法 'Int32 Parse(System.String)' 方法，并且此方法无法转换为存储表达式 '

This compiles fine but when it hits the database it throws the exception 'LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression '

如果我不进行解析并且值为 string[] 我就不能使用 >= 和 <= 字符串操作符.

If I don't do the parse and have values be a string[] I can't then use the >= and <= operators on strings.

p.Value 是一个包含各种值的字符串，但在本例中它是 int

p.Value is a string which holds various values but in this case it is int

有没有办法可以查询数据库来执行这种介于语句之间的操作?

Is there a way I can query the database to do this sort of between statement?

推荐答案

正如其他人在评论中指出的那样，你必须解析这个值的事实应该是一个危险信号，你应该使用不同的数据输入您的数据库.

As pointed out by others in the comments, the fact that you're having to parse this value should be a red flag that you should be using a different data type in your database.

幸运的是，有一种解决方法是强制查询由 LINQ to Objects 而不是 LINQ to Entities 执行.不幸的是，这意味着可能会将大量数据读入内存

Fortunately, there is a workaround by forcing the query to be executed by LINQ to Objects rather than LINQ to Entities. Unfortunately, it means potentially reading a large amount of data into memory

编辑

根据您的其他评论，值"列中的值不能保证是数字.因此，您必须尝试将值转换为数字，然后根据转换的失败/成功进行处理:

Based on your other comments, the value in the Value column ins't guaranteed to be a number. Therefore, you'll have to try converting the value to a number and then handling things based on the failure/success of that conversion:

return message
       .Properties
       .AsEnumerable()
       .Any(p => 
            {
                var val = 0;
                if(int.TryParse(p.Value, out val))
                {
                    return p.Key == name &&
                           val >= values[0] &&
                           val <= values[1])
                }
                else
                {
                    return false;
                }
           );

编辑 2

您实际上可以在数据库中摆脱这种情况.我不确定这是否适合你，但试一试:

You might actually be able to get away with this in the database. I'm not sure if this will work or not for you but give it a shot:

return message.Properties
              .Where(p => p.Key == name && SqlFunctions.IsNumeric(p.Value) > 0)
              .Any(p => Convert.ToInt32(p.Value) >= values[0] &&
                        Convert.ToInt32(p.Value) <= values[1]);

这篇关于实体框架/Linq EXpression 从字符串转换为 int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！