LINQ to SQL的 - 场包含已知字符串 [英] LINQ to SQL - field is contained in known string

问题描述

我建立一个LINQ查询动态地根据用户输入的，我要处理的情况下用户是基于字符串str和现场foo其中str.Contains（r.foo）寻找一个记录r。现在，反向（r.foo.Contains（STR））很简单，但LINQ是给我的悲伤关于做它的其他方式。

I'm building a LINQ query dynamically based on user input, and I want to handle the case where the user is searching for a record r based on a string str and field foo where str.Contains(r.foo). Now, the reverse (r.foo.Contains(str)) is simple, but LINQ is giving me grief about doing it the other way.

下面是我到目前为止有：

Here's what I have so far:

private Expression SqlNotIn(Expression left, Expression right)
{
    return Expression.Equal(
        Expression.Call(
            null,
            typeof(SqlFunctions).GetMethod("CharIndex", BindingFlags.Static | BindingFlags.Public, null, new[] { typeof(string), typeof(string) }, null),
            new[] { right, left }
        ),
        Expression.Constant(0)
    );
}

这是应该采取一个防爆pression 离开，这是属性访问器和防爆pression 右键，这是字符串的常量来搜索，并返回一个防爆pression 重新presenting（本质）（SqlFunctions.CharIndex（左，右）== 0）。当我运行这一点，我得到的二元运算符等于没有为类型System.Nullable 1 [System.Int32]'和'System.Int32定义。明确铸造 0 到诠释？`用的当然pression似乎导致LINQ到早期运行查询。

This is supposed to take an Expression left, which is the property accessor, and Expression right, which is a constant of the string to search in, and return an Expression representing (essentially) (SqlFunctions.CharIndex(right, left) == 0). When I run this, I get "The binary operator Equal is not defined for the types 'System.Nullable1[System.Int32]' and 'System.Int32'." Explicitly casting the0toint?` with an as expression seemed to cause LINQ to run the query early.

有没有一种简单的方法来做到这一点？

Is there a simple way to do this?

编辑：

private Expression SqlNotIn(Expression left, Expression right)
{
    return Expression.Equal(
        Expression.Call(
            right,
            typeof(string).GetMethod("IndexOf", new[] { typeof(string) }),
            new[] { left }
        ),
        Expression.Constant(-1)
    );
}

这工作，但它生成的SQL是这样的：

This works, but the SQL it generates looks like this:

(CASE 
    WHEN DATALENGTH([t0].[Destination]) = 0 THEN 0
    ELSE CHARINDEX([t0].[Destination], @p0) - 1
 END) = @p1

我会很高兴，如果我能使用CHARINDEX。

I'd be happy to use CharIndex if I could.

推荐答案

您可以尝试第一部分转换为 INT ，或第二部分诠释？与防爆pression.Convert 的 http://msdn.microsoft.com/en-us/library/bb292051.aspx

You can try to convert first part to int, or second part to int? with Expression.Convert http://msdn.microsoft.com/en-us/library/bb292051.aspx

例如。未经测试。

private Expression SqlNotIn(Expression left, Expression right) {
            return Expression.Equal(
                Expression.Convert(Expression.Call(
                    null,
                    typeof(SqlFunctions).GetMethod("CharIndex", BindingFlags.Static | BindingFlags.Public, null, new[] { typeof(string), typeof(string) }, null),
                    new[] { right, left }
                ), typeof(int)),
                Expression.Constant(-1)
            );
        }

顺便说一句，你可以使用 inputValue的字符串作为第二个参数，并用防爆pression.Constant（inputValue将）使用

By the way you could use a string inputValue as second parameter, and use it with Expression.Constant(inputValue)

修改

public static Expression SqlNotIn(Expression left, string right) {
            var method = typeof(string).GetMethod("IndexOf",
                new[] { typeof(string)});

            var call = Expression.Call(Expression.Constant(right), method, new []{left});

            var result =  Expression.Equal(call, Expression.Constant(0));
            return result;
        }

编辑2：

private Expression SqlNotIn2(Expression left, Expression right) {
            return Expression.Equal(
                Expression.Call(
                    null,
                    typeof(SqlFunctions).GetMethod("PatIndex", BindingFlags.Static | BindingFlags.Public, null, new[] { typeof(string), typeof(string) }, null),
                    new[] { right, left }
                ),
                Expression.Convert(Expression.Constant(0), typeof(int ?))
            );
        }

这篇关于LINQ to SQL的 - 场包含已知字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！