Linq to SQL: aggregation by ||

Question

I am trying to write a Linq query that retrieves all users whose first or last name starts with at least one line in the list of lines. This is used to automatically populate recipients in the 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 does not work, as the result is:

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

... while I want:

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

How should I do it?

Answer 1

Of course I have to 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 can 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 this time I'm probably going to go through a loop foreach. :)

Answer 2

2 - ... , , .

, -

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

, - , .

, , - Contains

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

, .

Answer 3

( ), , , , :

 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!

, , , , , !

EDIT: , "ALL()" , , , . :

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

Answer 4

.

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

User

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

Answer 5

:

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

, :

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