LINQ的String []对多个领域 [英] LINQ string[] against multiple fields

问题描述

可以说我有以下字段

    FName    varchar
    LName    varchar
    Phone    varchar
    DOB      datetime
    Address  varchar

表充满一些示例数据，可以说：

The table is filled with some sample data, lets say:

    John | Smith | 3051112222 | 01/01/1978 | Roosevelt Av 787
    Aron | Frank | 7871112222 | 01/01/1979 | Lambda Street 305
    Dick | Bush  | 9512221111 | 01/01/1980 | John Street 1
    John | Allen | 7872222222 | 01/01/1981 | Liberty Av 555

我们也有一个字符串数组元素的任意数量的，例如：

We also have a string array with an arbitrary number of elements, for example:

    search[0] = "1978"
    search[1] = "John"

我需要一个LINQ查询，将表中的每个字段比较的字符串数组中的每一项增量使用包含或任何 （在SQL意思等），并且只返回匹配所有给定的条件下在一个记录的行的基础上，前一搜索[]示例LINQ查询应该返回只记录＃1

I need a LINQ query that will compare each field of the table against each item in the string array incrementally using "contains" or "any" (meaning LIKE in SQL) and only return the rows that match all the given conditions in a record, based on the previous search[] example the LINQ query should return only record #1.

另一个例子是：

    search[0] = "Bush"
    search[1] = "111"
    search[2] = "John"

应该只有记录＃3按返回。最后的：

and only record #3 should by returned. Finally for:

    search[0] = "John"

记录＃1，＃3，＃4应退还（我认为这个想法是明确的）

Records #1, #3 and #4 should be returned (I think the idea is clear)

有如何在一个字符串[]比较的领域的问题：
的 LINQ -array-的弦：实体字符串字段包含任何字符串数组的

There's a question on how to compare a string[] against field in: LINQ: Entity string field contains any of an array of strings

如果答案是50行C＃程序，我更喜欢直接在通过存储过程的数据库解决这个问题。

If the answer is a 50 lines C# routine I prefer to solve this directly in the database via stored procedure.

这将是真棒，如果有某种反思的把戏，反覆dataContext.Customers所有领域，而做查询（显然是真正的表没有5场）。

It will be awesome if there is some kind of "reflection" trick to iterate all the field on dataContext.Customers while doing the query (obviously the real table don't have 5 fields).

业绩是没有问题的。

我敢肯定，这不可能，因为需要多个匹配的逻辑单LINQ行完成，但它从来没有伤害要求，更不用说学习任何新东西：）

I'm pretty sure this cannot be done in a single LINQ line because of the logic needed for the multiple match, but it never hurt to ask, much less to learn anything new :)

更新：
好​​吧，这里是一个简单的SQL代码来完成任务。请注意，我已经削减搜索变量的数量只是2清晰度。在真实的生活场景，我们可以参数的数量限制在10个搜索参数。我故意没有使用的功能（当然，除了CONVERT）以保持SQL尽可能简单，看看是否有什么办法在LINQ做到这一点。这里的SQL：

UPDATE: Ok, here's a simple SQL code that will accomplish the task. Note that I have cut the amount of search variables to just 2 for clarity. In the real life scenario we can limit the amount of arguments to 10 search parameters. I deliberately did not use functions (well, except for CONVERT) to keep the SQL as simple as possible to see if there is any way to accomplish this in LINQ. Here's the SQL:

    declare @_SEARCH1 varchar(1000)
    select  @_SEARCH1 = 'John'
    declare @_SEARCH2 varchar(1000)
    select  @_SEARCH2 = '111'

    select  *
    from    CUSTOMER
    where
            FName + ' ' + LName + ' ' + Phone + ' ' + CONVERT(varchar, DOB, 101) + ' ' + Address like '%'+@_SEARCH1+'%'
    and     FName + ' ' + LName + ' ' + Phone + ' ' + CONVERT(varchar, DOB, 101) + ' ' + Address like '%'+@_SEARCH2+'%'

所以，问题是，有没有写一个LINQ，将产生这个简单的SQL的方式？ （请注意，比较是在数据库中通过完成喜欢，而不是在应用程序）

So the question is, is there a way to write a LINQ that will generate this simple SQL? (please note that the comparison is done in the database via 'LIKE', not in the application)

更​​新2：虽然喜欢从旧金山一个解决方案，将产生 LIKE语句就会失败做比较。从表中提取所有数据到网络服务器会正​​确地做了比赛，但是是完全不切实际的其他解决方案。

UPDATE 2: Although solutions like the one from Francisco will generate the "LIKE" statement it will fail doing the comparison. The other solutions that pulls all the data from the table to the webserver will do the match correctly, but is totally impractical.

接受的答案，以RUNE FS，因为是干净的解决方案并将工作将任意数量的字段。

Accepted answer to RUNE FS since is the cleanest solution and will work will any number of fields.

推荐答案

假设'\t'永远不会是你可以在数据的一部分做到以下几点。您可以与其他任何字符当然替代。有了这个假设你可以做如下：

Assuming that '\t' would never be part of the data you could do the following. You can of course substitute with any other character. With that assumption you could do as below:

public static IEnumerable<T> Where<T>(this IEnumerable<T> sequence, 
                                      string[] criteria){

var properties = typeof(T).GetProperties()
                          .Where(p=>p.GetGetMethod() != null);
return from s in sequence
       let text = properties.Aggregate("",(acc,prop) => 
                                               acc + 
                                               "\t" + 
                                               prop.GetValue(s,null)
                                       )
       where criteria.All(c => text.Contains(c))
       select s;

}

修改
我本来不包括用法，因为我发现在原来的职位没有收藏，但假定该序列被定义为 IEnumerabl<人> 键，可以访问作为称为财产的人一个变量分贝。该代码将类似于：

EDIT I originally didn't include the usage since I found no collection in the original post but assuming the sequence is defined as IEnumerabl<Person> and can be accessed as a property called Persons on a variable db. the code would look similar to:

IEnumerable<Person> persons = db.Persons.Where(criteria);

这篇关于LINQ的String []对多个领域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！