>Query keywords – where

>

where: Just like in SQL “where” is used to filter the query results. Though not supported by all flavors (like Linq to SQL doesn’t support) if method syntax is used, then you can also filter on based on position in collection (i.e: if you are filtering an array you can say to choose from each Nth element where N is even – see the sample below).

Samples assume Northwind sample database is used for the ones against SQL data – to do that with LinqPad:

Click “Add Connection”, point to your SQL server instance that have the “Northwind” database. If database is not already selected in query window, click “Use Northwind” link displayed in window at right as seen in the following screen shot:

With this setup you can simply type the codes below and press F5 to see results.

from c in Customers
where c.Country == “USA”
select c

VB version:

from c in Customers _
where c.Country = “USA” _
select c

Method syntax (C#):

from c in Customers.Where( c => c.Country == “USA” )
select c

Method syntax (C#) – same thing typed another way:

from c in Customers
.Where( c => c.Country ==
“USA” )
select c

Method syntax (C#) – same thing typed another way:

from c in Customers
.Where( c =>
c.Country ==
“USA” )
select c

Method syntax (VB):

from c in Customers _
.Where(
Function(c) c.Country = “USA” ) _
select c

Simple where filtering on Customers whose Country is USA

Note that in all syntaxes that is a single statement (single command). You can type it in different ways using continuation (in C# you don’t need any continuation line marker – where in VB you use underscore and VFP semicolon).

VB method syntax is a little weird and hard to understand for me, I wouldn’t show VB method syntax in other samples. It should however be easy to understand for VB developers.

Within method call the variable “c” is local to Where() method and could be something else. i.e:

from c in Customers
.Where( cus =>
cus.Country ==
“USA” )
select c

Using method syntax we would see it is easier (it is not clear here why it is) but keeping that to later. Here it is just another way of writing the same thing.

from c in Customers
where c.Country == “USA” && c.Orders.Count < 5
select c

VB version:

from c in Customers _
where c.Country = “USA” and c.Orders.Count < 5 _
select c

Method syntax (C#):

from c in Customers
.Where( c => c.Country ==
“USA” &&
c.Orders.Count <
5 )
select c

Adding additional criteria. Note the navigational access to Orders of Customer simply using c.Orders and then Count property on customer’s Orders

from c in Customers
where c.Country == “USA” &&
c.Orders.First().OrderDate <
new DateTime(1997,1,1)
select c

VB version:

from c in Customers _
where c.Country = “USA” and _
c.Orders.First().OrderDate <
new DateTime(1997,1,1) _
select c

Method syntax (C#):

from c in Customers
.Where( c => c.Country ==
“USA” &&
c.Orders.First().OrderDate <
new DateTime(1997,1,1) )
select c

Country equals “USA” and additional criteria on Customer’s “first” Order using “First()” extension method. c.Orders.First() return the first order of that Customer as a Customer object where you can check any property (field, column). Another thing to note here is that we use strongly typed .Net DateTime() without doing any tricky conversion or anything like that to check OrderDate which is a DateTime in backend. If you check the SQL sent to backend “each” criteria are sent as parameters which is the way to do this in VFP too.

To remind Where() method, it has 2 overloads ( syntax shown here is not official and correct .Net syntax but kind of syntax that I converted to “VFP like” to make it easier VFP developers to understand input, output parameters. You can find the actual official .Net syntax on MSDN help ).

Where( lFilterExpression )

Where( nRowIndex, lFilterExpression )

We have already seen how Where( lFilterExpression ) version is used in the samples above. Note that, c => c.Country == “USA” is an expression as a whole that could be assigned to a variable (I am telling it here because I know VFP developer’s start to think how they could create dynamic expressions, do macro substitution etc. It is not exactly that but you should feel that you don’t need & operator. In VFP a similar one might be : lcWhere = “c.Country = ‘USA’” followed by select … where &lcWhere).

Second overload is not supported in Linq to SQL. I will instead give a sample using an array:

string[] months = {“January”,“February”,“March”,
“April”,“May”,“June”,
“July”,“August”,“September”,
“October”,“November”,“December”};
var myMonths = from m in months
.Where( (month, index) => index %
3 == 0 && month.Contains(‘r’) )
select m;
myMonths.Dump(
“Quarter starts”);

Here we use a criteria telling “Month’s array index should be a multiple of 3 and Month name should contain the letter ‘r’. We could chain multiple Where() and write a query like this:

var inSeattle = Customers
.Where(c => c.Country==
“USA”)
.Where(c => c.Region==
“WA”)
.Where(c => c.City==
“Seattle”);

inSeattle.Dump();

Which exactly generates the same SQL as this one:

var inSeattle = Customers
.Where(c =>
c.Country==
“USA” &&
c.Region==
“WA” &&
c.City==
“Seattle”);

inSeattle.Dump();

About cetinbasoz

A developer working primarily with C#,F#,VFP,SQL server, Silverlight,Linq ...
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s