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!!!
This question is related to
linq
linq-to-sql
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.
@Jon: Jon, are you saying using multiple where clauses e.g.
var query = from r in tempData.AsEnumerable()
where r.Field<string>("UserName") != "XXXX"
where r.Field<string>("UserName") != "YYYY"
select r;
is more restictive than using
var query = from r in tempData.AsEnumerable()
where r.Field<string>("UserName") != "XXXX" && r.Field<string>("UserName") != "YYYY"
select r;
I think they are equivalent as far as the result goes.
However, I haven't tested, if using multiple where in the first example cause in 2 subqueries, i.e. .Where(r=>r.UserName!="XXXX").Where(r=>r.UserName!="YYYY) or the LINQ translator is smart enought to execute .Where(r=>r.UserName!="XXXX" && r.UsernName!="YYYY")
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;
}
@Theo
The LINQ translator is smart enough to execute:
.Where(r => r.UserName !="XXXX" && r.UsernName !="YYYY")
I've test this in LinqPad ==> YES, Linq translator is smart enough :))
Source: Stackoverflow.com