带有可选WHERE选项的Linq

本文关键字：选项 Linq WHERE | 更新日期: 2023-09-27 18:16:07

我有一个。net函数，它接受3个参数，所有参数都是可选的。像这样:

public List<MyObject> Search(string colour, string size, string name)
{
     var result = (from c in MyTable where .... select c).ToList();     
}

我的问题是，做where部分的最好方法是什么?最好是创建动态连线吗?在linq中，使用可选where参数的最佳模式是什么?

因此，在SQL中，像这样: 
SELECT *
FROM MyTable
WHERE (@colour <> '' AND colour = @colour)
  AND (@size <> '' AND size = @size)
  AND (@name <> '' AND name = @name)

但是我希望在linq中有一个更整洁，更容易接受的模式。

带有可选WHERE选项的Linq

链接Where子句并检查null

var result = context.MyTable
    .Where(t => color == null || color == t.Color)
    .Where(t => size == null || size == t.Size)
    .Where(t => name == null || name == t.Name)
    .ToList();

另一种方法是仅在需要时添加条件

var query = context.MyTable;
if (color != null) query = query.Where(t => t.Color == color);
if (size != null) query = query.Where(t => t.Size == size);
if (name != null) query = query.Where(t => t.Name == name);
var result = query.ToList();

在这种情况下，我建议您使用PredicateBuilder来生成查询。你可以从这里复制代码，或者你可以安装LinqKit Nuget包。

使用此代码将允许您动态生成查询，并将防止您编写大量的if/else语句。

语句像…

p => p.Price > 100 &&
 p.Price < 1000 &&
 (p.Description.Contains ("foo") || p.Description.Contains ("far"))

将由这样的代码生成:

var inner = PredicateBuilder.False<Product>();
inner = inner.Or (p => p.Description.Contains ("foo"));
inner = inner.Or (p => p.Description.Contains ("far"));
var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 100);
outer = outer.And (p => p.Price < 1000);
outer = outer.And (inner);

我认为这是相当简洁的，它也会让你了解表达式是多么强大。

var results = olstOfObjects.Where(x => 
    (x.size == size || x.size == "") &&
    (x.color == color || x.color == "") &&
    (x.name == name || x.name == "")).ToList();;

您可以在Search方法中执行以下操作:

var query = from c in MyTable select c;
if (!String.IsNullOrEmpty(colour))
  query = from c in query where c.colour == colour select c;
if (!String.IsNullOrEmpty(size))
  query = from c in query where c.size == size select c;
if (!String.IsNullOrEmpty(name))
  query = from c in query where c.name == name select c;
return query.ToList();

如何:

public List<MyObject> Search(string colour, string size, string name)
{
    IEnumerable<MyObject> result = MyTable;
    if(colour != null)
        result = result.Where(o => o.Colour == colour);
    if(size != null)
        result = result.Where(o => o.Size == size);
    ...
    return result.ToList();
}

这里有一个包含所有条件的查询:

public List<object> Search(string colour, string size, string name)
{
    var query = from c in MyTable
        where
            (string.IsNullOrEmpty(colour) || c.colour == colour) &&
            (string.IsNullOrEmpty(size) || c.size == size) &&
            (string.IsNullOrEmpty(name) || c.name == name)
        select c;
    return query.ToList();
}
相关文章：