Multiple WHERE clause in Linq

Question

I'm new to LINQ and want to know how to execute multiple where clause. This is what I want to achieve: return records by filtering out certain user names. I tried the code below but not working as expected.

DataTable tempData = (DataTable)grdUsageRecords.DataSource;
var query = from r in tempData.AsEnumerable()
            where ((r.Field<string>("UserName") != "XXXX") || (r.Field<string>("UserName") != "XXXX"))                            
            select r;    

            DataTable newDT = query.CopyToDataTable();

Thanks for the help in advance!!!

Jon Skeet · Accepted Answer · 2009-03-24T23:33:19.563

Well, you can just put multiple "where" clauses in directly, but I don't think you want to. Multiple "where" clauses ends up with a more restrictive filter - I think you want a less restrictive one. I think you really want:

DataTable tempData = (DataTable)grdUsageRecords.DataSource;
var query = from r in tempData.AsEnumerable()
            where r.Field<string>("UserName") != "XXXX" &&
                  r.Field<string>("UserName") != "YYYY"
            select r;

DataTable newDT = query.CopyToDataTable();

Note the && instead of ||. You want to select the row if the username isn't XXXX and the username isn't YYYY.

EDIT: If you have a whole collection, it's even easier. Suppose the collection is called ignoredUserNames:

DataTable tempData = (DataTable)grdUsageRecords.DataSource;
var query = from r in tempData.AsEnumerable()
            where !ignoredUserNames.Contains(r.Field<string>("UserName"))
            select r;

DataTable newDT = query.CopyToDataTable();

Ideally you'd want to make this a HashSet<string> to avoid the Contains call taking a long time, but if the collection is small enough it won't make much odds.

I have a UserName collection. How do I pass it to the where clause dynamically. — Ganesha, Mar 24 '09 at 23:30
I know this was answered a long time ago but my quick suggestion would be to try and use join rather than contains. Is much more efficient (especially if your contains dataset is any reasonable size). — ArtificialGold, Oct 20 '10 at 14:47
so if i have a use case where i have a predicate only on non null values, would it be better (readable) to have where(x => x.value!=null).where(y => y.value.Contains("someValue")).ToList() ? instead of .where(y => y.value!=null && y.value.Contains("someValue")).ToList() — kuldeep, Jan 27 '23 at 13:23

score 52 · Answer 2 · edited Mar 04 '21 at 20:35

52

The LINQ translator is smart enough to execute:

.Where(r => r.UserName !="XXXX" && r.UsernName !="YYYY")

I've tested this in LinqPad ==> YES, Linq translator is smart enough :))

edited Mar 04 '21 at 20:35

Jean-François Fabre

137,073
23
153
219

answered Sep 21 '12 at 06:48

alex

529
4
2

1

Smart enough means that Linq has extra filters which is a hit to performance. – Andres Toro Feb 24 '19 at 02:21

Tolga Okur · Answer 3 · 2011-11-08T09:41:57.753

Also, you can use bool method(s)

Query :

DataTable tempData = (DataTable)grdUsageRecords.DataSource;
var query = from r in tempData.AsEnumerable()
            where isValid(Field<string>("UserName"))// && otherMethod() && otherMethod2()                           
            select r;   

        DataTable newDT = query.CopyToDataTable();

Method:

bool isValid(string userName)
{
    if(userName == "XXXX" || userName == "YYYY")
        return false;
    else return true;
}

Multiple WHERE clause in Linq

3 Answers3

Linked

Related