>Query keywords – select

>

Before starting "select" I would like to list where to find LinqPad, SQL express, sample Northwind database, VC# express. To follow you need LinqPad (VS or VC# express IDE would do too, but I repeat LinqPad is a cool utility that worth the 3Mb download).

Linqpad download: http://www.LinqPad.Net

SQL express version download: http://www.microsoft.com/express/sql/default.aspx (you may also download cool SQL CE for some samples in the future – or to use from VFP)

VC# (or any one of the express): http://www.microsoft.com/express/product/ (If you check specific product pages there are tons of extras but don’t get lost in them:)

Northwind database: http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

after downloading run SQL2000SampleDb.msi. It creates a new folder named "SQL Server 2000 Sample Databases", in it you would find both the sql scripts and database files (Northwind and Pubs). Either attach the database or run the sql script. i.e: In command prompt you can go to that folder and execute:

sqlcmd -S .\sqlexpress -i instnwnd.sql –E

( –S stands for SQL server instance which by default is .\SQLExpress for express edition, –i is input script file, and –E parameter tells that we are using windows authentication).

(you can also try this: http://msdn.microsoft.com/en-us/library/8b6y4c7s(VS.80).aspx )

OK now let’s continue.

select: is the keyword for selecting of course:) Linq however can do a select that can do more than flat retrieval. It can transform the data it retrieves (projection, shaping data) in a variety of ways.  With SQL select the result may be transformed. i.e:

select employeeID, rtrim(LastName) + ‘, ‘ + rtrim(FirstName) as Fullname from employees  

Fullname is a transformation of data but still we have a flat result made up of rows and columns. However with Linq we can do much more complex shaping on the data retrieved.

select clause too also  have method counterpart Select(). Select() method just like Where() have 2 overloads. Second overload, like Where, has an index parameter and not supported by Linq to entities (you can find a list of supported and non supported methods for Linq to entities here.

Do you always need to use select clause or Select() method? No, you don’t have to. Using query syntax you need to end the expression with a ‘select’ or ‘group’ clause.  You can have do these:

var customers = Customers; // in LinqPad Northwind set as the database you may try just typing: Customers [F5] 
var usaCustomers =  Customers.Where( c => c.Country == "USA" );

You may think of these variations like:

use customers

and

use customers
scan for Country = "USA"

We have done these before like:

from c in Customers select c

from c in Customers where c.Country == "USA" select c 

These are "expressions" you can directly type in LinqPad and hit F5 to get results.

You can shape the result in a variety ways and to create new shapes you use "new { … }" as we saw earlier in creating anonymous types:

from c in Customers select new { c.CompanyName, c.ContactName }

from c in Customers select new { MyCompany = c.CompanyName, c.ContactName }

are simple transformations and correspond to:

select c.CompanyName, c.ContactName from Customers

select c.CompanyName as MyCompany, c.ContactName from Customers

in SQL. Now more interesting selects – try these in LinqPad:

from c in Customers
    where c.Country == "USA" && c.Region == "WA"
    select c.Orders

from c in Customers
from o in c.Orders
    where c.Country == "USA" && c.Region == "WA"
    select new {CustomerInfo = c, OrderInfo = o}

you can have many variations, you can embed little lambda expression to transform further or utilize Linq to XML to generate XML results. i.e:

from c in Customers
        select new XElement ("customer",
            new XAttribute ("id", c.CustomerID),
            new XAttribute ("company", c.CompanyName),
            new XElement ("contact", c.ContactName),
            new XElement ("country", c.Country),
                  new XComment ("Total Orders:"+c.Orders.Count().ToString()))

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