如何在c# /ASP中实现搜索功能。NET MVC

I am developing an ASP.NET MVC 3 application using C# and Razor.

我正在开发ASP。NET MVC 3应用使用c#和Razor。

I have a search form that looks like this:

我有这样一个搜索表单:

The search form works in the following way:

搜索表单的工作方式如下:

1. The user selects which property they want to search on.
2. 用户选择要搜索的属性。
3. The user selects how they want to match the search string (e.g. contains, starts with, ends with, equals, etc).
4. 用户选择他们希望如何匹配搜索字符串(例如，包含、开始、结束、等号等)。
5. The user enters a search term and clicks Search.
6. 用户输入搜索词并单击search。

The selections in the first drop down related directly to a property in my ADO.NET Entity Framework model class (and therefore directly to a table column).

第一个下拉列表中的选择与ADO中的属性直接相关。NET实体框架模型类(因此直接到表列)。

Users need the ability to explicitly select which property and which matching method when searching, e.g. a user will explicitly search for all matches of process number that equals '132'.

用户需要能够在搜索时显式地选择哪个属性和哪个匹配方法，例如，用户将显式地搜索等于“132”的所有进程号匹配项。

My first approach was to use dynamic linq to construct a Where clause from the search criteria (see my original question). However I'm starting to think that this isn't the best way to do it.

我的第一种方法是使用动态linq从搜索条件中构造Where子句(参见我最初的问题)。但是我开始认为这不是最好的方法。

I'm also hoping for a solution that doesn't require me to hard code the result for each property + matching criteria combination.

我还希望有一种解决方案，它不需要我为每个属性加上匹配标准组合硬编码结果。

Any suggestions on how I should implement this search? It doesn't have to be using my current search form, totally open to any other ideas that fit the requirements.

对于如何实现这个搜索有什么建议吗?它不需要使用我目前的搜索表单，完全开放给任何其他符合需求的想法。

7 个解决方案

#1

You can build expression tree for where predicate using code. For example,

您可以使用代码为where谓词构建表达式树。例如,

public static IQueryable<T> DynamicWhere<T>(this IQueryable<T> src, string propertyName, string value)
{
    var pe = Expression.Parameter(typeof(T), "t");
    var left = Expression.Property(pe, typeof(T).GetProperty(propertyName));
    var right = Expression.Constant(value);
    // Illustrated a equality condition but you can put a switch based on some parameter
    // to have different operators
    var condition = Expression.Equal(left, right);

    var predicate = Expression.Lambda<Func<T, bool>>(condition, pe);
    return src.Where(predicate);
}

Use it as Orders.DynamicWhere(searchBy, searchValue). You can add one more parameter to accept the operator such as Equals, Greater Than etc to complete the function.

把它作为订单。DynamicWhere(searchBy searchValue)。您可以再添加一个参数来接受操作符，例如Equals, Greater Than etc来完成函数。

See these links for more info:

更多信息请参见这些链接:

http://msdn.microsoft.com/en-us/library/bb882637.aspx

http://msdn.microsoft.com/en-us/library/bb882637.aspx

http://msdn.microsoft.com/en-us/library/bb397951.aspx

http://msdn.microsoft.com/en-us/library/bb397951.aspx

Also check list of methods on the Expression class to get an idea.

还可以检查表达式类上的方法列表，以获得想法。

#2

Have you looked into using Lucene.NET for this project? given the nature of your searches it would be very simple to build that using Lucene, as it allows you to combine filters on different columns just like your requirements

你考虑过使用Lucene吗?网络对于这个项目?考虑到您的搜索特性，使用Lucene构建它将非常简单，因为它允许您将不同列的过滤器结合起来，就像您的需求一样。

#3

You can use Dynamic Linq and you can create the Where clausole with a utility class like this:

您可以使用动态Linq，并可以创建Where clausole与一个实用程序类如下:

public class Criteria 
{
   StringBuilder sb = new StringBuilder();
   bool first = true;

   public void And(string property, string dbOperator, string value) {
       if (first)
       {
           sb.Append(" ").Append(property).Append(" ");
           sb.Append(" ").Append(dbOperator).Append(" ");
           sb.Append(" ").Append(value).Append(" ");
           first = false;
       }
       else
       {
           sb.Append(" && ").Append(property).Append(" ");
           sb.Append(" ").Append(dbOperator).Append(" ");
           sb.Append(" ").Append(value).Append(" ");
       }
   }

   public void Or(string property, string dbOperator, string value)
   {
       if (first)
       {
           sb.Append(" ").Append(property).Append(" ");
           sb.Append(" ").Append(dbOperator).Append(" ");
           sb.Append(" ").Append(value).Append(" ");
           first = false;
       }
       else
       {
           sb.Append(" || ").Append(property).Append(" ");
           sb.Append(" ").Append(dbOperator).Append(" ");
           sb.Append(" ").Append(value).Append(" ");
       }
   }

   public string ToString() 
   {
       return sb.ToString();
   }

}

So you can build a Criteria with many properties using Or or And methods and put it in the Where operator of Dynamic Linq.

因此，您可以使用Or或和方法构建一个具有许多属性的标准，并将其放在Dynamic Linq的Where操作符中。

#4

We started out resolving similar queries against our Entity Framework model using dynamic linq queries. However, our attempts to generalize query generation resulted in bad performance due to EF being confused by the resulting complex expressions, so in the end horrible SQL was produced.

我们开始使用动态linq查询解决针对实体框架模型的类似查询。然而，由于EF被生成的复杂表达式所迷惑，我们试图推广查询生成的尝试导致了糟糕的性能，因此最终生成了糟糕的SQL。

We resorted to Entity SQL.

我们采用实体SQL。

#5

Not sure if you are using MS SQL. Seems SQL could do most of the work for you, and you can build dynamic queries. Obviously the select/from statement needs work, but you can get the idea from the where clause.

不确定您是否在使用MS SQL。SQL似乎可以为您完成大部分工作，并且您可以构建动态查询。显然，select/from语句需要工作，但是您可以从where子句中获得概念。

DECLARE @SEARCHTYPE VARCHAR(20)
DECLARE @SEARCHTERM VARCHAR(100)

SELECT
    [FIELDS]
FROM
    [TABLE]
WHERE
    (@SEARCHTYPE = 'BEGINSWITH' AND [FIELD] LIKE @SEARCHTERM + '%') OR
    (@SEARCHTYPE = 'ENDSWITH' AND [FIELD] LIKE '%' + @SEARCHTERM) OR
    (@SEARCHTYPE = 'EQUALS' AND [FIELD] = @SEARCHTERM)

#6

You could have the first combo data source set to myEntityObject.GetType().GetProperties(), the second to a list of displayable Funcs<string, string, bool>, like this:

可以将第一个组合数据源设置为myentityobjec . gettype ().GetProperties()，这是第二个可显示函数 的列表，如下所示: ,>

public class ComboPredicate
{
    public Func<string, string, bool> Func {get; set;}
    public string Name {get; set; }
}

Later, when you load the form:

稍后，当您加载表单时:

comboProperty.Datasource = myEntityObject.GetType().GetProperties()
comboOperation.Datasource = new List<Predicate>
    {
        {
            Name = "Contains",
            Predicate = (s1, s2) => s1 != null && s1.Contains(s2),
        },
        {
            Name = "Equals",
            Predicate = (s1, s2) => string.Compare(s1, s2) == 0,
        },
        //...
    }

And later, when you want to select your entities:

之后，当你想选择你的实体:

var propertyInfo = (PropertyInfo)comboProperty.SelectedValue;
var predicate = ((ComboPredicate)comboOperation.SelectedValue).Predicate;
var filteredObjects = objects.Where(o => predicate(propertyInfo.GetValue(o, null).ToString(), textBoxValue.Text));

#7

create method and call it on button click demo below

创建方法并在按钮上调用它，点击下面的demo

public List gettaskssdata(int c, int userid, string a, string StartDate, string EndDate, string ProjectID, string statusid) {

gettaskssdata公共列表(int c, int userid, string a, string StartDate, string EndDate, string ProjectID, string雕像希德){

        List<tbltask> tbtask = new List<tbltask>();


        var selectproject = entity.tbluserprojects.Where(x => x.user_id == userid).Select(x => x.Projectid);

        if (statusid != "" && ProjectID != "" && a != "" && StartDate != "" && EndDate != "")
        {
            int pid = Convert.ToInt32(ProjectID);
            int sid = Convert.ToInt32(statusid);
            DateTime sdate = Convert.ToDateTime(StartDate).Date;
            DateTime edate = Convert.ToDateTime(EndDate).Date;
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblproject.ProjectId == pid) && (x.tblstatu.StatusId == sid) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a)) && (x.StartDate >= sdate && x.EndDate <= edate)).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (statusid == "" && ProjectID != "" && a != "" && StartDate != "" && EndDate != "")
        {
            int pid = Convert.ToInt32(ProjectID);
            DateTime sdate = Convert.ToDateTime(StartDate).Date;
            DateTime edate = Convert.ToDateTime(EndDate).Date;
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblproject.ProjectId == pid) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a)) && (x.StartDate >= sdate && x.EndDate <= edate)).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (ProjectID == "" && statusid != "" && a != "" && StartDate != "" && EndDate != "")
        {
            int sid = Convert.ToInt32(statusid);
            DateTime sdate = Convert.ToDateTime(StartDate).Date;
            DateTime edate = Convert.ToDateTime(EndDate).Date;
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblstatu.StatusId == sid) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a)) && (x.StartDate >= sdate && x.EndDate <= edate)).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if(ProjectID!="" && StartDate == "" && EndDate == "" && statusid == ""  && a == "")
        {
            int pid = Convert.ToInt32(ProjectID);
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblproject.ProjectId == pid)).OrderByDescending(x => x.ProjectId).ToList();

        }
        else if(statusid!="" && ProjectID=="" && StartDate == "" && EndDate == ""  && a == "")
        {
            int sid = Convert.ToInt32(statusid);
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblstatu.StatusId == sid) ).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (a == "" && StartDate != "" && EndDate != "" && ProjectID != "")
        {
            int pid = Convert.ToInt32(ProjectID);
            DateTime sdate = Convert.ToDateTime(StartDate).Date;
            DateTime edate = Convert.ToDateTime(EndDate).Date;
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.ProjectId == pid) && (x.StartDate >= sdate && x.EndDate <= edate)).OrderByDescending(x => x.ProjectId).ToList();

        }
        else if (StartDate == "" && EndDate == "" && statusid != "" && ProjectID != "" && a != "")
        {
            int pid = Convert.ToInt32(ProjectID);
            int sid = Convert.ToInt32(statusid);
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblproject.ProjectId == pid) && (x.tblstatu.StatusId == sid) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a))).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (a == "" && StartDate == "" && EndDate == "" && ProjectID != "" && statusid != "")
        {
            int pid = Convert.ToInt32(ProjectID);
            int sid = Convert.ToInt32(statusid);
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Include(x => x.tblstatu).Where(x => selectproject.Contains(x.ProjectId) && x.tblproject.company_id == c && x.tblproject.ProjectId == pid && x.tblstatu.StatusId == sid).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (a != "" && StartDate == "" && EndDate == "" && ProjectID == "" && statusid == "")
        {
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a))).OrderByDescending(x => x.ProjectId).ToList();

        }
        else if (a != "" && ProjectID != "" && StartDate == "" && EndDate == "" && statusid == "")
        {
            int pid = Convert.ToInt32(ProjectID);
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.tblproject.ProjectId == pid) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a))).OrderByDescending(x => x.ProjectId).ToList();
        }
        else if (a != "" && StartDate != "" && EndDate != "" && ProjectID == "" && statusid == "")
        {
            DateTime sdate = Convert.ToDateTime(StartDate).Date;
            DateTime edate = Convert.ToDateTime(EndDate).Date;
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && (x.tblproject.company_id == c) && (x.TaskName.Contains(a) || x.tbUser.User_name.Contains(a)) && (x.StartDate >= sdate && x.EndDate <= edate)).OrderByDescending(x => x.ProjectId).ToList();
        }
        else
        {
            tbtask = entity.tbltasks.Include(x => x.tblproject).Include(x => x.tbUser).Where(x => selectproject.Contains(x.ProjectId) && x.tblproject.company_id == c).OrderByDescending(x => x.ProjectId).ToList();
        }
        return tbtask;

    }