Linq to SQL: Aggregating over ||

Question

I'm trying to write a Linq query which fetches all users whose first or last name begins with at least one string in a list of strings. This is used for auto-completion of recipients in a messaging system.

This was my first naive attempt:

var users = UserRepository.ALL()
foreach (var part in new [] { 'Ha', 'Ho', 'He' })
{
    string part1 = part; // "Copy" since we're coding lazily
    users = users.Where(x => x.LastName.StartsWith(part1) ||
                             x.FirstName.StartsWith(part1));
}

This doesn't work though, as the result becomes:

users.Where(a || b).Where(c || d).Where(e || f)...

...whereas I want:

users.Where(a || b || c || d || e || f || ...)

How would I go about doing this?

Answer 1

you need to use 2 collections - in your code you are filtering down a list ... really you need the collection of filtered lists - not a collection of a list that has been filtered multiple times.

one as a repository for matches and the other witin your loop

var userCollection = new Collection<string>();
var users = UserRepository.ALL() 
foreach (var part in new [] { 'Ha', 'Ho', 'He' }) 
{ 
    string part1 = part; // "Copy" since we're coding lazily 
    var matches = users.Where(x => x.LastName.StartsWith(part1) || 
                             x.FirstName.StartsWith(part1)); 
    foreach (var i in matches)
    {
        userCollection.Add(i);
    }
} 

I am not claiming this is the most elegant solution - just simply trying to point at why your logic is failing.

you can probably do something with Contains

var results= from i in collection 
            where idList.Contains(i.Id) 
            select i; 

just cant see how off the top of my head

Answer 2

You can construct an expression tree:

var parts = new[] { "Ha", "Ho", "He" };

var x = Expression.Parameter(typeof(User), "x");

var body = 
    parts.Aggregate<string, Expression>(
        Expression.Constant(false), 
        (e, p) => 
            Expression.Or(e,
                Expression.Or(
                    Expression.Call(
                        Expression.Property(x, "LastName"), 
                        "StartsWith", 
                        null,
                        Expression.Constant(p)),
                    Expression.Call(
                        Expression.Property(x, "FirstName"), 
                        "StartsWith", 
                        null, 
                        Expression.Constant(p)))));

var predicate = Expression.Lambda<Func<User, bool>>(body, x);

var result = users.Where(predicate);

The result is the same as:

var result =
    users.Where(x => 
        false ||
        x.LastName.StartsWith("Ha") || x.FirstName.StartsWith("Ha") ||
        x.LastName.StartsWith("Ho") || x.FirstName.StartsWith("Ho") ||
        x.LastName.StartsWith("He") || x.FirstName.StartsWith("He") );

Answer 3

Of course, I should use Union...

IQueryable<User> result = null;
foreach (var part in terms)
{
    string part1 = part;
    var q = users.Where(x => x.FirstName.StartsWith(part1) ||
                             x.LastName.StartsWith(part1));
    result = result == null ? q : result.Union(q);
}

Using ReSharper this could be turned into a Linq expression:

IQueryable<User> result = terms.Select(part1 =>
    users.Where(x => x.FirstName.StartsWith(part1) ||
                     x.LastName.StartsWith(part1)))
         .Aggregate<IQueryable<User>, IQueryable<User>>(
                null, (current, q) => current == null ? q : current.Union(q));

...but I'm probably going to go for the foreach loop this time. :)

Answer 4

Here is a one-liner (formatted for readability) that I believe returns the result you seek:

 var users = UserRepository.ALL()
    .ToList() //ToList called only to materialize the list
    .Where(x => new[] { 'Ha', 'Ho', 'He' }
        .Any(y => x.LastName.StartsWith(y))
    );  //Don't need it here anymore!

It is probably not the efficient solution you were seeking, but I hope it helps you in some way!

EDIT: As gaearon pointed out, if the 'ALL()' command returns a lot of records, my first solution is probably really bad. Try this:

var users = UserRepository.ALL()
        .Where(x => new[] { 'Ha', 'Ho', 'He' }
            .Any(y => SqlMethods.Like(x.LastName, y + "%"))
        );

Answer 5

This code does it for strings.

var users = new [] {"John", "Richard", "Jack", "Roy", "Robert", "Susan" };
var prefixes = new [] { "J", "Ro" };

var filtered = prefixes.Aggregate(Enumerable.Empty<string>(),
    (acc, pref) => acc.Union(users.Where(u => u.StartsWith(pref)).ToList()));

For your User class it would look like

var filtered = prefixes.Aggregate(
    Enumerable.Empty<User>(),
    (acc, pref) => acc.Union(
        users.Where(
            u => u.FistName.StartsWith(pref) || u.LastName.StartsWith(pref)
            ).ToList()));