LINQ LEFT JOIN, Combine multiple WHERE clauses with OR

Question

LINQ,

var clause = PredicateBuilder.False<User>();
clause = clause.Or(u => u.uid.Equals(1));
clause = clause.Or(u => u.uid.Equals(2));

var usersInGroup = (from u in db.Users
                    join g in db.GroupUsers
                        on u.uid equals g.uid
                        into ug
                    from g in ug.DefaultIfEmpty()
                    where g.gid.Equals(0)
                    select u).Where(clause);

These two where clauses are chained together as;

WHERE ([t0].[gid] = 0) AND (([t1].[uid] = 1) OR ([t1].[uid] = 2))

How do I add the two where conditions as

WHERE ([t0].[gid] = 0) OR (([t1].[uid] = 1) OR ([t1].[uid] = 2))

I don't think you can combine multiple where clauses with or. You need to combine your conditions into a single where clause. — Ben Robinson, Jun 23 '11 at 15:43
This is linq to sql. How do I combine the two clauses? For example I can't do this; .Where(clause.Or(g => g.gid.Equals(0))); — Red Taz, Jun 23 '11 at 15:44
Do you want a solution using PredicateBuilder, or can you just manually add each condition to the `where` clause as esastincy suggests? — Justin Morgan - On strike, Jun 23 '11 at 18:50

score 2 · Accepted Answer · edited May 23 '17 at 12:19

Thanks to,

Can PredicateBuilder generate predicates that span multiple tables?

I now have a solution that works but my result set is based on a new hybrid class. As a result I have had to mirror all of the relevant fields. See below.

public class HybridGroupUser {
    private User _user;
    public User User {
        get { return _user; }
        set {
            _user = value;
            if (value != null) {
                uid = value.uid;
                fname = value.fname;
                lname = value.lname;
                email = value.email;
            }
        }
    }
    private GroupUser _GroupUser;
    public GroupUser GroupUser {
        get { return _GroupUser; }
        set {
            _GroupUser = value;
            if (value != null) {
                uid = value.uid;
                fname = value.fname;
                lname = value.lname;
                email = value.email;
            }
        }
    }

    public int? uid { get; set; }
    public string fname { get; set; }
    public string lname { get; set; }
    public string email { get; set; }
}

With this class I can now do the following;

var clause = PredicateBuilder.False<HybridGroupUser>();

clause = clause.Or(u => u.GroupUser.gid.Equals(0);
foreach (int i in AddedUsers) {
    int tmp = i;
    clause = clause.Or(u => u.User.uid.Equals(tmp));
}

var usersInGroup = (from u in db.Users
                    join gusr in db.GroupUser
                        on u.uid equals gusr.uid
                        into ug
                    from gusr in ug.DefaultIfEmpty()
                    select new HybridGroupUser {
                       User = u, 
                       GroupUser = gusr
                    }).Where(clause);

esastincy · Answer 2 · 2011-06-23T16:09:34.163

0

var usersInGroup = (from u in db.Users
join g in db.Groups
on u.uid equals g.uid
where g.gid.Equals(0) || (u.uid.Equals(1) || u.uid.Equals(2))
select u)

edited Jun 23 '11 at 16:09

answered Jun 23 '11 at 15:50

esastincy

1,607
8
28
38

Not really. Do you mean two separate linq queries joined together? – Red Taz Jun 23 '11 at 16:03
I'm using PredicateBuilder to build up the uids because I don't know how many there will be at runtime, so I can't hard code them as you suggest. – Red Taz Jun 24 '11 at 08:08

score 0 · Answer 3 · answered Sep 01 '11 at 12:24

Instead of doing multiple Or clauses, why not just do Contains. That way your list of Ids can be complete dynamic (as long as it doesn't exceed 2000):

var ids = new int[] {1, 2, 3}; 

var usersInGroup = (from u in db.Users 
                    join g in db.GroupUsers 
                        on u.uid equals g.uid 
                        into ug 
                    from g in ug.DefaultIfEmpty() 
                    where g.gid.Equals(0) 
                      && ids.Contains(u.uid)
                    select u);

LINQ LEFT JOIN, Combine multiple WHERE clauses with OR

3 Answers3