带有可选Where子句和SQL Server CE的Linq

问题描述

我有一个带有可选用户名"字段的搜索表单.如果未提供用户名，则应返回所有匹配项.

I have a search form with an optional User Name field. If the User Name is not supplied then all matches should be returned.

我正在使用Linq和Sql Server CE 4.0.

I am using Linq and Sql Server CE 4.0.

linq代码如下所示->

The linq code looks like the following ->

from p in context.Accounts
where (name==string.Empty || p.UserName.Contains(name))

对于Sql Server CE，这会引发以下错误

With Sql Server CE this throws the following error

此位置不允许使用参数.请确保此SQL语句中的'@'符号位于有效位置或所有参数均有效."

"A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement."

我还可以采用其他方法在Linq中使用可选的Where子句吗?

Is there some other approach I can take to have optional Where clauses in Linq?

仅供参考

from p in context.Accounts
where (string.IsNullOrEmpty(name) || p.UserName.Contains(name))

给我错误

该函数的指定参数值无效.[参数#= 1，函数名称(如果已知)= isull]"}

"The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]"}

这是由于Sql Server CE不支持IsNull.如果Name参数为Null，我将简单地执行以下操作.

This is due to Sql Server CE not supporting IsNull. I simply do the below if the Name parameter is Null.

if (name == null)
    name = string.Empty;

推荐答案

尝试一下:

var query = from p in context.Accounts
            select p;

if (!string.IsNullOrEmpty(name)) {
    query = query.Where(p => p.UserName.Contains(name));
}

没有规则说查询必须在单个语句中.您可以添加到现有查询，直到实际执行该查询为止.

There's no rule saying the query has to be in a single statement. You can add on to an existing query up until the point you actually execute it.

这篇关于带有可选Where子句和SQL Server CE的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！