Merging 2 C# IQueryable

Question

I don't have a lot of experience with IQueryable

What I am trying to do is search for a user based on a list of passed in constraints that can either be a username, or phone number. Depending on the type I want to return limited information. I then want to combine the 3 IQueryables into one and combine entries with matching id and username to maintain the most information.

Here is what i have so far:

public IQueryable<User> Search(String[] criteria)
{
        var query = Database.Get<User>();

        IQueryable<User> phoneQuery = null;
        IQueryable<User> emailQuery = null;
        IQueryable<User> nameQuery = null;

        foreach (String str in criteria)
        {
            // Check if it is a phone number
            if (Regex.IsMatch(str, @"([0-9]{3})?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$"))
            {
                phoneQuery = query.Where(
                       u => u.PhoneNumber.ToLower() == (str))
                       .Select(i => new User
                       {
                           Id = i.Id,
                           UserName = i.Name,
                           PhoneNumber = i.PhoneNumber
                       })
            }
            // check if it is an email 
            else if (criteria.Contains("@"))
            {
                emailQuery = query.Where(
                       u => u.Email.ToLower() == (str))
                       .Select(i => new User
                       {
                           Id = i.Id,
                           UserName = i.Name,
                           Email = i.Email
                       })
            }
            else
            {
                nameQuery = query.Where(
                       u => u.UserName.ToLower() == (str))
                       .Select(i => new User
                       {
                           Id = i.Id,
                           UserName = i.Name,
                       })
            }
        }
        // Merge the 3 queries combining entries if the username and id match and maintain the maximum amount of information

        return query;

    }

Answer 1

There are a few issues with your code:

ToList() will execute the query. If you call AsQueryable() later, you simply create an object query on the local objects. This basically loses the notion of IQueryable, so you'd better delete all ToList() and AsQueryable() calls.

You can make it a single query instead of merging the three queries, like so:

Expression predicateBody = Expression.Constant(false);
Expression userParameter = Expression.Parameter("user", typeof(User));
Expression userUserName = Expression.MakeMemberAccess(...);
Expression userPhoneNumber = Expression.Cal(...);
Expression userEmail = Expression.Call(...);

foreach (String str in criteria)
{
    // Check if it is a phone number
    if (Regex.IsMatch(str, @"([0-9]{3})?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$"))
    {
         predicateBody = Expression.Or(predicateBody, Expression.Equals(userPhoneNumber, Expression.Constant(str)));
    }
    // check if it is an email 
    else if (criteria.Contains("@"))
    {
         predicateBody = Expression.Or(predicateBody, Expression.Equals(userEmail, Expression.Constant(str)));
    }
    else
    {
         predicateBody = Expression.Or(predicateBody, Expression.Equals(userUserName, Expression.Constant(str)));
    }
}

return query.Where(Expression.Lambda<Func<User, bool>>(predicateBody, userParameter))
     .GroupBy(u => u.Id)
     .Select(users => new User()
          {
               Id = users.Key,
               UserName = users.Select(u => u.UserName).Intersect(criteria).FirstOrDefault(),
               Email = users.Select(u => u.Email).Intersect(criteria).FirstOrDefault(),
               PhoneNumber = users.Select(u => u.PhoneNumber).Intersect(criteria).FirstOrDefault()
          });

Edit Sorry, I misunderstood the merging problem.

Edit2 If the criterias are sorted in advance, there is also a solution that does not require to manually creating the expression tree.

Edit3 I see, I forgot the part with the limited information.

var phoneNumbers = new List<string>();
var emails = new List<string>();
var userNames = new List<string>();

foreach (var str in criteria)
{
    // Check if it is a phone number
    if (Regex.IsMatch(str, @"([0-9]{3})?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$"))
    {
         phoneNumbers.Add(criteria);
    }
    // check if it is an email 
    else if (criteria.Contains("@"))
    {
         emails.Add(crietria);
    }
    else
    {
         userNames.Add(criteria);
    }
}

return query.Where(u => phoneNumbers.Contains(u.PhoneNumber)
       || emails.Contains(u.Email)
       || userNames.Contains(u.UserName))
       .GroupBy(u => u.Id)
       .Select(users => new User()
              {
                   Id = users.Key,
                   UserName = users.Select(u => u.UserName).Intersect(userNames).FirstOrDefault(),
                   Email = users.Select(u => u.Email).Intersect(emails).FirstOrDefault(),
                   PhoneNumber = users.Select(u => u.PhoneNumber).Intersect(phoneNumbers).FirstOrDefault()
              });

Answer 2

This is what I ended up going with.

public IQueryable<User> Search(String[] criteria)
        {
            var query = Database.Get<User>();
            List<User> res = new List<User>(); 
            foreach (String str in criteria)
            {
                // Check if it is a phone number

                if (Regex.IsMatch(str, @"([0-9]{3})?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$"))
                {
                    var users = query.Where(
                           u => u.PhoneNumber.ToLower() == (str))
                           .Select(i => new User
                           {
                               Id = i.Id,
                               UserName = i.Name,
                               Email = null,
                               PhoneNumber = i.PhoneNumber
                           });

                    // Multiple users can have the same phone so add all results
                    foreach (User u in users)
                    {
                        if (u != null) { res.Add(u); }
                    }

                }
                // Check if it is an email match 
                else if (str.Contains("@"))
                {
                    var user = query.Where(
                           u => u.Email.ToLower() == (str))
                           .Select(i => new User
                           {
                           Id = i.Id,
                           UserName = i.Name,
                           Email = i.Email,
                           PhoneNumber = null
                       }).SingleOrDefault<User>(); // Only one user can use an email

                if (user != null) { res.Add(user); }
            }
            // Otherwise it is a username
            // NOTE: If a username is all digits and dashes it won't be 
            // searched for because it is interpreted as a phone number!
            else
            {
                var user = query.Where(
                       u => u.UserName.ToLower() == (str))
                       .Select(i => new User
                       {
                           Id = i.Id,
                           UserName = i.Name,
                           Email = null,
                           PhoneNumber = null
                       }).SingleOrDefault<User>(); // Only one user can use an email

                if (user != null) { res.Add(user); }
            }
        }

        query = res.AsQueryable(); 

        // Group the results by username and id and return all information that was found
        query = from u in query
                group u by new
                {
                    u.Id,
                    u.UserName
                } into g
                select new User()
                {
                    Id = g.Key.Id,
                    UserName = g.Key.UserName,
                    Email = g.Select(m => m.Email).SkipWhile(string.IsNullOrEmpty).FirstOrDefault(), 
                    PhoneNumber = g.Select(m => m.PhoneNumber).SkipWhile(string.IsNullOrEmpty).FirstOrDefault()
                };

        return query;
    }