忽略where子句linq到sql中的null参数

Question

我需要忽略where子句中的null参数，这样我就可以从用户端使用应用的过滤器获取适当的数据。为了实现这一点，我目前正在使用if.else嵌套方法，该方法的大小随着参数数量的增加而增加。我想知道是否有其他有效的方法来处理这种情况，避免线路数量和复杂性，并提高的可用性

public List<Members> GetMembers(int currentPosition, string memberStatus,
string package, string packageStatus, string branch)
{
var members = new List<Members>();
if (package != null)
{
//include package
if (packageStatus != null)
{
//include package, packageStatus
if (branch != null)
{
//include package,packageStatus,branch
members = db.Members.Where(x => x.PackageName == package && x.PackageStatus == packageStatus && x.Branch == branch).ToList();
}
else
{
//include package,packageStatus
members = db.Members.Where(x => x.PackageName == package && x.PackageStatus == packageStatus).ToList();
}
}
else
{
if (branch != null)
{
//include package,branch
members = db.Members.Where(x => x.PackageName == package && x.Branch == branch).ToList();
}
else
{
//include package
members = db.Members.Where(x => x.PackageName == package).ToList();
}
}
}
else
{
if (packageStatus != null)
{
//include packageStatus
if (branch != null)
{
//include packageStatus,branch
members = db.Members.Where(x => x.PackageStatus == packageStatus && x.Branch == branch).ToList();
}
else
{
//include packageStatus
members = db.Members.Where(x => x.PackageStatus == packageStatus).ToList();
}
}
else
{
if (branch != null)
{
//include packageStatus,branch
members = db.Members.Where(x => x.PackageStatus == packageStatus && x.Branch == branch).ToList();
}
else
{
//include nothing
members = db.Members.ToList();
}
}
}
return members;
}

Answer 1

您可以将这些条件添加到查询中。它不会产生可读性最高的SQL，但假设您发现可读代码更重要，并信任SQL Server的优化器：

members = db.Members.Where(x =>
(package == null || x.PackageName == package) &&
(packageStatus == null || x.PackageStatus == packageStatus) &&
(branch == null || x.Branch == branch)
).ToList();

或者，您可以有条件地将Where()附加到类型为IQueryable<Member>的变量。请参阅实体框架示例：条件过滤器。

Answer 2

我想推荐一个可读版本的方法：

public List<Members> GetMembers(int currentPosition, string memberStatus,
string package, string packageStatus, string branch)
{
var members = new List<Members>();
members = db.Members.ToList();
if (package != null)
{
members = members.Where(x => x.PackageName == package);
}
if (packageStatus != null)
{
members = members.Where(x => x.PackageStatus == packageStatus);
}
if (branch != null)
{
members = members.Where(x => x.Branch == branch);
}
return members.ToList();
}

Answer 3

我认为这是可行的(还没有尝试编译它，但在我的脑海中它是有意义的(

public List<Members> GetMembers(int currentPosition, string memberStatus,
string package, string packageStatus, string branch)
{
List<Members> ret = db.Members()
if(memberStatus != null || package != null || packageStatus != null || branch != null)
{
ret = db.Members.Where(x => 
(memberStatus == null) ? (true) : (memberStatus == x.MemberStatus)
&& (package == null) ? (true) : (package == x.PackageName)
&& (packageStatus == null) ? (true) : (packageStatus == x.PackageStatus)
&& (branch == null) ? (true) : (branch == x.Branch)
).ToList();
}
return ret;
}