实体框架中具有OR条件的动态查询 [英] Dynamic query with OR conditions in Entity Framework

问题描述

我正在创建一个创建应用程序，该应用程序搜索数据库并允许用户动态添加任何条件(大约50个条件)，非常类似于以下SO问题:

I am creating a creating an application that searches the database and allows the user to dynamically add any criteria (around 50 possible), much like the following SO question: Creating dynamic queries with entity framework. I currently have working a search that checks each criteria, and if it is not blank it adds it to the query.

C#

var query = Db.Names.AsQueryable();
  if (!string.IsNullOrWhiteSpace(first))
      query = query.Where(q => q.first.Contains(first));
  if (!string.IsNullOrWhiteSpace(last))
      query = query.Where(q => q.last.Contains(last));
  //.. around 50 additional criteria
  return query.ToList();

此代码在sql server中产生类似于以下内容的内容(为了简化起见，我对其进行了简化)

This code produces something similar to the following in sql server (I simplified for easier understanding)

SQL

SELECT
    [Id],
    [FirstName],
    [LastName],
    ...etc
FROM [dbo].[Names]
WHERE [FirstName] LIKE '%first%'
  AND [LastName] LIKE '%last%'

我现在正尝试添加一种通过实体框架使用C#生成以下SQL的方法，但使用 OR 而不是 AND ，同时仍保持以下功能:动态添加条件.

I am now trying to add a way to generate the following SQL with C# through entity framework but with an OR instead of an AND, while still keeping the ability to add criteria dynamically.

SQL

SELECT
    [Id],
    [FirstName],
    [LastName],
    ...etc
  FROM [dbo].[Names]
WHERE [FirstName] LIKE '%first%'
  OR [LastName] LIKE '%last%' <-- NOTICE THE "OR"

通常，查询的标准不会大于两个或三个项目，但是将它们组合成一个巨大的查询是不可行的.我尝试了concat，union和intersect，它们都重复了查询并与UNION合并.

Usually the criteria wont be larger than two or three items for a query but combining them into one gigantic query is not an option. I have tried concat, union, and intersect and they just all duplicate the query and join them with UNION.

是否存在一种简单明了的方法来使用实体框架向动态生成的查询中添加或"条件?

Is there a simple and clean way to add "OR" conditions to a dynamically generated query using entity framework?

使用我的解决方案进行编辑-2015年9月29日

自发布此消息以来，我注意到它已经引起了一些注意，因此我决定发布我的解决方案

Since posting this, I have noticed this has received a little attention, so I decided to post my solution

// Make sure to add required nuget
// PM> Install-Package LinqKit

var searchCriteria = new 
{
    FirstName = "sha",
    LastName = "hill",
    Address = string.Empty,
    Dob = (DateTime?)new DateTime(1970, 1, 1),
    MaritalStatus = "S",
    HireDate = (DateTime?)null,
    LoginId = string.Empty,
};

var predicate = PredicateBuilder.False<Person>();
if (!string.IsNullOrWhiteSpace(searchCriteria.FirstName))
{
    predicate = predicate.Or(p => p.FirstName.Contains(searchCriteria.FirstName));
}

if (!string.IsNullOrWhiteSpace(searchCriteria.LastName))
{
    predicate = predicate.Or(p => p.LastName.Contains(searchCriteria.LastName));
}

// Quite a few more conditions...

foreach(var person in this.Persons.Where(predicate.Compile()))
{
    Console.WriteLine("First: {0} Last: {1}", person.FirstName, person.LastName);
}

推荐答案

您可能正在寻找类似 Predicate Builder ，它使您可以更轻松地控制where语句的AND和OR.

You're probably looking for something like Predicate Builder which allows you to control the AND's and OR's of the where statement easier.

还有

There's also Dynamic Linq which allows you to submit the WHERE clause like a SQL string and it will parse it into the correct predicate for a WHERE.

这篇关于实体框架中具有OR条件的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！