在Linq到实体查询表达式中使用元组或其他复杂类型。

Question

因此，我想在一个客户表中搜索所有的客户，每个客户都有自己的名字、电子邮件地址或电话号码，它们匹配所有的查询关键字。

..。这在代码中可能比用英语更容易理解：

public IQueryable<Contact> SearchCustomers(string query)
{
    var ws = from w in query.Split()
                where !String.IsNullOrWhiteSpace(w)
                select w;

    var q =
        from c in Customers
        where ws.All(w =>
                c.FirstName == w
                || c.LastName == w
                || c.EmailAddress == w
                || c.HomePhone == PhoneNumber.Pack(w)
                || c.CellPhone == PhoneNumber.Pack(w))
        select c;

    return q;
}

但是我不能在数据库上调用PhoneNumber.Pack，所以我需要使w成为一种既存储w原始值又存储Packed值的格式，并且我必须在客户端这样做。问题是Linq不喜欢在表达式参数中有元组或数组，而且它不支持String.IndexOf，所以我不能在一个字符串中抛出两个字符串，然后取子字符串。

还有其他方法可以绕过这件事吗？或者可能是查询的重述？

编辑：生成的如下所示：

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
(etc)
FROM [dbo].[Contacts] AS [Extent1]
WHERE ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    WHERE ( NOT ([Extent1].[FirstName] = N'rei' OR [Extent1].[LastName] = N'rei' OR [Extent1].[EmailAddress] = N'rei' OR [Extent1].[HomePhone] = N'rei' OR [Extent1].[CellPhone] = N'rei')) OR (CASE WHEN ([Extent1].[FirstName] = N'rei' OR [Extent1].[LastName] = N'rei' OR [Extent1].[EmailAddress] = N'rei' OR [Extent1].[HomePhone] = N'rei' OR [Extent1].[CellPhone] = N'rei') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[FirstName] = N'rei' OR [Extent1].[LastName] = N'rei' OR [Extent1].[EmailAddress] = N'rei' OR [Extent1].[HomePhone] = N'rei' OR [Extent1].[CellPhone] = N'rei')) THEN cast(0 as bit) END IS NULL)
))

Answer 1

public IQueryable<Contact> SearchCustomers(string query)
{
    var ws = from w in query.Split()
                where !String.IsNullOrWhiteSpace(w)
                select new { Unpacked = w , Packed = PhoneNumber.Pack(w) };

    var q = Customers;
    foreach(var x in ws)
    {
        string ux = x.Unpacked;
        string px = x.Packed;
        q = q.Where(
               c=> 
                c.FirstName == ux
                || c.LastName == ux
                || c.EmailAddress == ux
                || c.HomePhone == px
                || c.CellPhone == px
            );
    }
    return q;
}

这将产生所需的结果，foreach中的temp变量将解决您的问题。

Answer 2

我会创建一个私有结构：

private struct UnpackedAndPacked
{
    public string Unpacked {get;set;}
    public string Packed {get;set;}
}

var ws = from w in query.Split()
         where !String.IsNullOrWhiteSpace(w)
         select new UnpackedAndPacked
                    {
                        Unpacked=w, 
                        Packed=PhoneNumber.Pack(w)
                    };  

然后更改条件：

    where ws.All(w => 
                 c.FirstName == w.Unpacked
                  || c.LastName == w.Unpacked
                  || c.EmailAddress == w.Unpacked
                  || c.HomePhone == w.Packed
                  || c.CellPhone == w.Packed)
    select c;

我进一步调查了这件事，我认为你不可能按原样完成这件事。问题是，由于ws.All，它希望为ws序列中的每个值创建一组SQL子句。它需要一个基本类型的序列，比如字符串。

如果您可以将代码更改为有两个查询参数，那么我认为它可能会工作。对于不需要打包的东西，您需要一组参数，对于那些不需要打包的东西，您需要一组参数。然后将其转换为LINQ方法链，并在两者之间进行联合。以身作则。

啊，真灵。我的密码在下面。请注意，我使用的是AdventureWorks2008R2数据库，所以我的数据库比您的数据库要复杂一些--我有大量的电子邮件地址和电话要处理；其中任何一个都可以匹配：

public static IQueryable<Person> SearchCustomers(
    AdventureWorksEntities entities, string nameQuery, string phoneQuery)
{
    var wsu = from w in nameQuery.Split()
        where !String.IsNullOrWhiteSpace(w)
        select w;
    var wsp = from w in phoneQuery.Split()
        where !String.IsNullOrWhiteSpace(w)
        select Pack(w);
    return
        entities.People.Where(
            c => wsu.All(w => c.FirstName == w || c.LastName == w)).
            Union(
                entities.People.Where(
                    c =>
                    wsp.All(
                        w =>
                        c.PersonPhones.Any(p => p.PhoneNumber == w) ||
                        c.EmailAddresses.Any(a => a.EmailAddress1 == w))));
}

还请注意，我找到了另一种获取跟踪输出的方法

IQueryable<Person> query = SearchCustomers(entities, "w1 w2",
                                           "(602) (408)");
var oc = (ObjectQuery<Person>) query;
Console.WriteLine(oc.ToTraceString());

Answer 3

请注意，query.Where(a).Where(b)与query.Where(a & b)是相同的，qry.All()本质上是接受一系列条件，并将AND语句链接在一起，类似于(word 1 is found) && (word 2 is found) && (word 3 is found).

您可以使用它执行以下操作(我使用扩展方法，以便将其链接到任何其他IQueryable<Customer>的末尾)。

    [System.Runtime.CompilerServices.Extension()]
    public static IQueryable<Customer> Search(this IQueryable<Customer> query, string searchTerm)
    {
        string[] queryWords = searchTerm.Split(" ");

        foreach (string w in queryWords) {
            string word = w;
            string packedWord = Pack(word);

            query = query.Where(c => c.FirstName == word || c.LastName == word || c.HomePhone == packedWord || c.CellPhone == packedWord);
        }
        return query;
    }

或VB等效

<System.Runtime.CompilerServices.Extension()>
Public Function Search(query As IQueryable(Of Customer), searchTerm As String) As IQueryable(Of Customer)
    Dim queryWords = searchTerm.Split(" ")

    For Each w In queryWords
        Dim word = w
        Dim packedWord = Pack(word)

        query = query.Where(Function(c) c.FirstName = word OrElse
                                c.LastName = word OrElse
                                c.HomePhone = packedWord OrElse
                                c.CellPhone = packedWord)
    Next
    Return query
End Function