Using a tuple or some other complex type in a Linq-to-Entities query expression

Question

So I want to search within a table of customers all of the customers which in each has its name, email address, or phone numbers match all of the query keywords.

... Which is probably easier to understand in code than in English:

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;
}

But I can't call PhoneNumber.Pack on the database, so I need to make w a format which will store both the raw value of w as well as the Packed value, and I have to do that on the client's side. The problem is that Linq doesn't like having tuples or arrays in the expression arguments, and it doesn't support String.IndexOf, so I can't throw two strings in one and then take substrings.

Any other ways to get around this? Or maybe a restatement of the query?

Edit: The generated SQL looks like this:

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;
}

This will produce the desired result, and temp variable inside foreach will resolve your issue.

Answer 2

I'd create a private struct:

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)
                    };  

Then change the condition:

    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;

---

I looked into this further, and I think you're not going to get this done as-is. The problem is that, because of the ws.All, it wants to create a set of SQL clauses once for each value in the ws sequence. It needs that to be a sequence of primitive types, like string.

If you could change your code to have two query parameters, then I think it might work. You'd need one set of parameters for the things that don't need packing, and one for those that do. You would then change this into a LINQ methods chain and do a Union between the two. Example to follow.

---

It worked. My code is below. Note that I used the AdventureWorks2008R2 database, so mine is a bit more complicated than yours - I have a collection of email addresses and of phones to deal with; a match on either of those is accepted:

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))));
}

Note also that I found another way to get trace output:

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