建立动态查询 [英] Build dynamic query

问题描述

我正在开发MVC3和EF.4.1应用程序，其中用户必须能够执行自定义"搜索.

例如，我必须能够搜索活动，以指定它们必须是我的活动"，或者它们是针对特定客户"的活动，或者两者兼而有之.

我想知道的是，是否有一种方法可以基于用户输入以类似于以下方式动态构建查询:

注意:像addcondition()这样的函数名是发明的"，希望可以更好地解释我的需求.

I''m developing an MVC3 and EF.4.1 application where the user must be able to perform "custom" searches.

For example, I must be able to search activities specifing that they must be "my activities", or that they are activities for a "specific customer", or both.

What I would like to know, is if there''s a way to build the query dynamically based on user input in a way similar to this:

NOTE: function names like addcondition() are "invented" to better explain, hopefully, what I need.

public actionresult search(bool filterbyuser, int userid, bool filterbycontact, int contactid )
{
activities = db.activities;

//add where clause to restrict search result

if (filterByUser) {
   activites.addcondition(model=>model.userid == userid);
}

if (filterByContact){
  activities.addcondition(model=>model.contactid == contactid);
}

return PartialView(activities);
}

这是一个简化的示例，因为我应该能够搜索例如对许多客户所做的许多用户的活动，以及许多其他可能的过滤器(例如联系人分类，用户角色，活动类型等)，因此解决方案"，例如:

This is a simplified example, because I should be able to search, for example, activities of many users done to many customers, and with many other possible filters (for example contact categorization, user role, activity type and so on), so a "solution" like:

if (filterbyuser && filterbycontact){
  //filter both fields
}
else if (filterbyuser){
  //filter user
}
else {
  //filter contact
}

必须避免，否则会乱成一团，如果用户设置了要检查哪个字段的条件，这将使我在未来几年内在耻辱大厅中处于一个安全的位置；).

预先感谢您，

Alberto

must be avoided, otherwise there will be a mess of if condition to check wich fields have been set by user, granting me a safe place in the hall of shame for years to come ;) .

Thank you in advance,

Alberto

推荐答案

好吧，这似乎比我想象的要简单.我可以简单地做到:

Well, it seems like It''s all simpler than I thought. I can simply do:

/*
Example class Product
*/

public class Product{
  public int id {get;set;}
  public string name {get;set;}
  public string category {get;set;}
}

//controller action for search

public ActionResult searchProducts(string name, string category){

  IQueryable<product> searchResult = db.Products.asQueryable();

  if(!String.IsNullOrEmpty(name)){
    searchResult = searchResult.Where(m=>m.name == name);
  }

  if(!String.IsNullOrEmpty(category)){
    searchResult = searchResult.Where(m=>m.category == category);
  }
  

  return View(searchResult);
}

</product>

我认为这将执行多个查询，每个后续的哪里"都会添加一个查询.相反，实际上是每次我在集合中循环时才执行查询，在这种情况下，当我将数据传递给视图进行渲染时.

I thought that this would perform multiple queries, one for each subsequent "Where" added; instead, the query is actually performed each time I loop in the collection, in this case when I pass the data to the view for rendering.

&edit; gt;糟糕...我只是注意到您正在使用ASP.NET，并且我链接到WinForms控件.我只是将链接放在此处作为参考，如果您知道自己的交易，则无需单击它们；)</edit>

不确定要尝试实现的目标，但是听起来像您想要的是动态SQL [ ^ ] us/library/bb399572.aspx>实体框架 [

<edit>Oops... I just noticed you are using ASP.NET and I linked to WinForms Controls... This doesn''t matter for the idea though. I just put the links there as a reference, you shouldn''t need to click them if you know your trade ;)</edit>

Not sure what you are trying to achieve exactly, but it sounds like you want something like dynamic SQL[^] in Entity Framework[^].
Let''s say you have a table called Product in your database, which is mapped to an Entity called Product.
Product is always a CD and has the following fields:
ID, Number, Artist, Album, Price

And the following products:

1 - CD001 - Amy Winehouse - Back To Black - 19,99
2 - CD002 - Amy Winehouse - Frank - 15,99
3 - CD003 - Beach Boys - Surfin' USA - 9,99
4 - CD004 - Björk - Homogenic - 15,99

现在，假设您希望用户能够过滤Amy Winehouse的所有CD.
这可以通过以下代码实现:

Now, let''s say you want the users to be able to filter for all CD''s by Amy Winehouse.
This could be achieved by the following code:

String name = TextBox1.Text; // Amy Winehouse
_context.Products.Where(p => p.Artist = name).ToList();

但是，现在您要检索所有以B开头的艺术家(Björk和Beach Boys).

However, now you want to retrieve all artists that start with a B (Björk and Beach Boys).

String name = TextBox1.Text; // B
_context.Products.Where(p => p.Artist.StartsWith = name).ToList();

此时，您的用户界面将需要一个选项，以按艺术家和 CheckBox [^ ]/ RadioButton [

At this point your user interface would need an option to filter by artist and a CheckBox[^]/RadioButton[^] to check if it''s the full name or only the first letter.
The user interface will become pretty clogged up and before you know it a user can select many CheckBoxes and RadioButtons to get a filter.
What''s worse (from a coders point of view) is that you will have many cases and if/then/else''s for each scenario.
Does the user want to filter Artist, Album, Price? And does the user want to know the full name, first letter, last letter, anything lower than, higher than, equal to? etc.

This is where entity sql comes to the rescue!
Consider the following:

IEnumerable<Product> GetFilteredProducts(String whereClause)
{
    return _context.Products.Where(whereClause);
}

现在您可以按以下方式调用此函数:

Now you could call this function as follows:

 // Returns artists that start with B.
List<Product> products = GetFilteredProducts("it.Artist LIKE 'B%'");
// Returns products that cost less than 10.
List<Product> products = GetFilteredProducts("it.Price < 10");

在这种情况下B或10可以是用户输入，其余查询可以由您从数据库中预先设置，因此用户可以选择您为其创建的过滤器之一，然后他们可以输入要过滤的值通过一个或多个文本框 [控件 [ ^ ]解析为所需的过滤器字符串.但这是一项非常艰巨的任务.通过使用DevExpress FilterControl并将操作数解析为其各自的过滤器字符串，我实现了一些类似的操作，从而实现了非常细粒度但仍对半用户友好的过滤.

因此，我们来看一下上面的例子.我们有一个组合框 [

In this case B or 10 could be user input, the rest of the query could be pre-set by you from a database, so the user could select one of the filters you made for them and they could enter the value they want to filter by in one or more TextBox[^]es.
Or you could create something fancy where they can select the fieldname, function, type etc. from a Control[^] which gets parsed to the filter string you want. But this is a very difficult task. I achieved something similiar by using the DevExpress FilterControl and parsing the operands to their respective filter strings, making for very fine-grained, but still semi-user friendly filtering.

So let''s revice the above example. We have a ComboBox[^] on the form with two values: ''Artist starts with'' and ''Price lower than''.

List<Product> products = GetFilteredProducts("it.Artist LIKE '" + TextBoxArtist.Text + "%'");
Decimal price;
if (Decimal.TryParse(TextBoxPrice.Text, price))
{
    List<Product> products = GetFilteredProducts("it.Price < " + price.ToString());
}
else
{
    // No valid price was entered.
}

现在，如果您可以从数据库中获得不包含值(但包括值类型)的完整过滤器字符串，则可以轻松检查TextBox中的文本是否与给定类型匹配(甚至向用户显示一个Control仅将期望的类型作为输入，例如Boolean的CheckBox)并将值粘贴到过滤器字符串中.也许通过使用正则表达式 [ ^ ]相对于动态SQL而言，这是一个巨大的优势！而且在这种情况下，使用动态SQL也不容易.但是，这种方法是未来的证明(将更多字段添加到表中只会导致键入一些额外的过滤器而无需更改源代码！)并且非常灵活.

我希望这能回答(至少是一些)您的问题，使您走上正确的道路或给您一些想法.祝你好运！ :)

实体SQL上的一些链接:
实体SQL概述 [实体SQL参考 [实体SQL语言 [

Now if you could get the complete filter string excluding values (but including the value type) from the database you could easily check if the text in the TextBox matches the given type (or even present the user with a Control that only takes the expected type as input, like a CheckBox for Boolean) and paste the value in the filter string. Perhaps by using a Regular Expression[^]. For example: it.Artist LIKE ''[@PARAM; STRING]''. Here you can replace [@PARAM...] with the user input and you also know the user input is of the type String.

I isn''t easy to create ''dynamic filters'' this way, but using Entity SQL your queries ARE parameterized[^] which is a HUGE advantage over dynamic SQL! And in this case using dynamic SQL wouldn''t be easy either. However, this approach is future proof (adding more fields to your table only results in typing some extra filters without having to alter your source code!) and very flexible.

I hope this answered (at least some of) your question, put you on the right track or gave you some idea''s. Good luck! :)

Some links on Entity SQL:
Entity SQL Overview[^]
Entity SQL Reference[^]
Entity SQL Language[^]

这篇关于建立动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！