>Query keywords – orderby

>

orderby: Is used for sorting the records. In comprehension syntax it is just like we use it in SQL. i.e:

from c in Customers
orderby c.Country, c.CompanyName descending
select c

Here we are ordering by Country (default ascending) and then by CompanyName in descending order.

In method syntax there are 4 methods:

OrderBy()
OrderByDescending()
ThenBy()
ThenByDescending()

Using method syntax above sample would be written as:

Customers
  .OrderBy( c => c.Country )
  .ThenByDescending( c => c.CompanyName )
  .Select( c => c)

All 4 methods support a custom comparison method as a second parameter (for example you could create a method that instead of widely known alphabetical sort, sorts the strings based on a custom ordering – "10" comes after "2").

Here is a sample modified from LinqPad’s OrderBy sample:

string[] strings = { "Tom", "Çetin",
   "I come before i", "Can", "in",
   "Dick", "Harry", "Mary", "Jay",
   "ş is between s and t", "smith"};

strings.OrderBy (n => n, StringComparer.Create( new System.Globalization.CultureInfo("tr-TR"), true))
    .Dump ("Case insensitive ordering based on Turkish alphabet");

This second parameter is not supported in Linq to SQL.

Posted in Uncategorized | Leave a comment

>Query keywords – group and into

>

group (… by ): Is used for grouping the results based on a key. The key can be a plain or composite key. In Linq, the result of grouping is a List of Lists. You can think of it as data grouping in a report rather than a flat rows*columns representation of a table. The result is  ( A Key per group, Matching Items per key ) collection – officially IGrouping ( TKey, TElement ). Another feature of group is that it is a keyword that can be used to end a query ( a query should end either with a select or group ).

A simple grouping would be to group Northwind Customers by their country values:

from c in Customers group c by c.Country

You can "group", "select" or "join" into an intermediary result using the keyword "into" and continue the query on that ‘sub query’. It is like doing a sub query in SQL. "Key" is a property that is generated automatically and holds the group’s key. i.e.:

from c in Customers
                group c by c.Country into countries
                select new
                {
                    Country = countries.Key,
                    Customers = from cus in countries 
                               
orderby cus.City, cus.CustomerID
                                select new { cus.CustomerID, cus.City }
                }

Here what we do is first we group the Customers by their country and ‘alias’ that ‘sub query’ as "countries". From that point on identifier "c" is not available to us (it is similar to SQL in that matter too). We continue with "countries" which is now a ( Country as key, Matching Rows ) collection.  

You can also group a group but IMHO it is a hard to understand syntax:

from c in Customers.Select( c => new {c.CustomerID, c.Region, c.City, c.Country} )
   group c by c.Country[0] into g1
   from cc in ( from c in g1 group c by c.Country )
   group cc by g1.Key

Posted in Uncategorized | Leave a comment

>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()))

Posted in Uncategorized | Leave a comment

>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();

Posted in Uncategorized | Leave a comment

>Query keywords – from

>

<!–
/* Font Definitions */
@font-face
{font-family:”Cambria Math”;
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:162;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1107304683 0 0 159 0;}
@font-face
{font-family:calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:162;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-1610611985 1073750139 0 0 159 0;}
@font-face
{font-family:verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;
mso-font-charset:162;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:536871559 0 0 0 415 0;}
@font-face
{font-family:consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;
mso-font-charset:162;
mso-generic-font-family:modern;
mso-font-pitch:fixed;
mso-font-signature:-1610611985 1073750091 0 0 159 0;}
/* Style Definitions */
p.msonormal, li.msonormal, div.msonormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:””;
margin-top:0cm;
margin-right:0cm;
margin-bottom:10.0pt;
margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:en-us;}
.msochpdefault
{mso-style-type:export-only;
mso-default-props:yes;
mso-ascii-font-family:calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:calibri;
mso-fareast-theme-font:minor-latin;
mso-hansi-font-family:calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:en-us;}
.msopapdefault
{mso-style-type:export-only;
margin-bottom:10.0pt;
line-height:115%;}
@page section1
{size:595.3pt 841.9pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;
mso-header-margin:35.4pt;
mso-footer-margin:35.4pt;
mso-paper-source:0;}
div.section1
{page:section1;}
–>

Let’s start to check each query keyword with samples.

from: Is like the “from” in SQL, refers to the source.

from c in Customers
from
n in numbersArray
from
f in Directory.GetFiles(“c:\\temp”)
from
p in Diagnostics.Process.GetProcesses()
from
di in DriveInfo.GetDrives()

In these incomplete samples c, n, f, p, di  are variables referring to an object of type it is used with. For example if Customers is a collection of Customer objects then “c” in “from c in Customers” refer to a Customer type. In SQL, you may think of it as the local alias of table like:

Select c.CustomerID, c.CompanyName, c.ContactName from Customers c

In method syntax as far as I know there isn’t something to use instead (you have it as a parameter – this Ienumerable<Tsource> source). The reason that you first write “from” is intellisense support. That makes sense if you think how we write an SQL select. Think that you have 50 columns in a table and you want to select 10 of them, visualize the ideal way that you could write it. We all have some ways to make the life easier fro ourselvees in writing those type of queries but none of them would be as good as getting a direct support from the IDE itself. Saying it like:

from Customers c Select c.CustomerID, c.CompanyName, c.ContactName

we would be getting help from intellisense when writing c.CustomerID, c.CompanyName, c.ContactName. I think it was a syntax choice (probably to match natural English) to write it as:

from c in Customers

instead of

from Customers c

Unlike SQL where there is a single “from” clause in Linq you can have multiple. Multiple “from” create a type of join and there is also “join” keyword. Here are samples for multiple “from”:

from c in Customers from o in c.Orders

is an implicit join (equijoin, inner join) – in case of Linq to SQL and Linq to Entities it infers the join as c.CustomerId = o.CustomerId). If you make a typo (might be intentional but generally a newbie typo) and write it as:

from c in Customers from o in Orders

then it is still an implicit join (non equijoin, cross join) but selects all records from both tables and match each producing cartesian result – same as if we do a typo in SQL version like:

select * from Customers, Orders 

leaving out the join (either with a join clause or where specifying the relation) ends in a cross join. It might be intentional, say to create a calendar like:

2007,January
2007,February

2009,January
2009,February

int[] years = {2007,2008,2009};
string[] months = {"January","February","March",
                  
"April","May","June",
                  
"July","August","September",
                  
"October","November","December"};
var myCalendarHeaders =
        
from y in years
        
from m in months
        
select String.Format("{0},{1}", y,m);
myCalendarHeaders.Dump();

Posted in Uncategorized | Leave a comment

>Query or method syntax

>

There are a series of “Query Keywords” which most of them should be familiar to any VFP developer. They are very similar to the SQL clauses we are familiar with and results they provide are more or less similar. Beside query keywords, there are a series of extension methods defined in Linq namespace with same or similar names to those keywords and more. As said before you can use query syntax most of the time and need to use method syntax in some cases where query syntax is not supported. That is what language supports and forces you to do. IMHO however, method syntax is easier and nicer. Once you get a hang of it, query syntax and the SQL we know sounds to be a hard language:) I will try to go with both syntaxes but might honor method syntax more.

To understand the method syntax and to use it effectively we need to learn how to read method signature. Here are the signatures of System.Linq.Enumerable.Where method as an example:

public static IEnumerable<TSource> Where<TSource>(
this IEnumerable<TSource> source,
Func<TSource, bool> predicate
)

public static IEnumerable<TSource> Where<TSource>(
this IEnumerable<TSource> source,
Func<TSource, int, bool> predicate
)

Syntax is not easy to understand but don’t be afraid. If you can rewrite it in your mind unofficially you can understand it quicker then you expect:

  • There are 2 signatures or two ‘overloads’ of Where method, meaning it could be called with 2 parameter sets.
  • <TSource> : Is all about generic typing. In case of <Customer> all those TSource become Customer type.
  • public static IEnumerable<TSource> : read this part as “it would return a series of records” – TSource is the type of Object. Think it like a table. If table is Customers then TSource is a Customers row where fields are its properties.
  • Input parameters are (for first overload):
  1. this IEnumerable<TSource> source : Parameter name is “source” and it is a collection of TSource objects (think Customer as sample). “this” tells you that it is an extension method and can be used on “this” instance (of Customer type) just like as VFP’s “this”.
  2. Func<TSource, bool> predicate : Parameter name is predicate and it is an inline method (a lambda expression) that takes an object of type TSource (say Customer) as input and returns a bool result (true or false).

    In second overload, second parameter is –

  3. Func<TSource, int, bool> predicate : Parameter name is again “predicate”, which is an inline method that takes an object type of TSource, plus an integer as input parameters and returns a boolean value.

Lets recap it in a pseudo code and a little bit VFP like typing:

procedure Where( source as CollectionOfTSource ) as CollectionOfTSource

Loop and call predicate( TSource ) per item in source Collection

Add those that returns True to return Set

Procedure predicate( item as TSource ) as Logical

in second overload procedure named “predicate” would look like:

Procedure predicate( item as TSource, parameter2 as integer ) as Logical

Let’s give an example to understand better:

int[] numbers = {1,10,29,5,53,33,32,277,232,32,43};

IEnumerable<int> oddNumbers = from n in numbers where n % 2 != 0 select n;

oddNumbers.Dump(“Odd numbers using query syntax”);

This is vanilla ice query syntax that looks like an SQL and correspond to first overload of Where() method. Here we use “where” clause with a simple filter. Same query can be done with method syntax like this:

IEnumerable<int> odds = System.Linq.Enumerable.Where( numbers,n => n % 2 != 0 );

odds.Dump(“Odd numbers using method syntax – 1”);

We are sending “numbers” as ( IEnumerable<TSource> source ) parameter and ” n => n % 2 != 0 ” as ( Func<TSource, bool> predicate ) parameter to Where() method defined in System.Linq.Enumerable class. TSource is defined as int in this case. Since “Where” method is defined as an extension method for IEnumerable<TSource> (in other words a collection of same type of objects – their type is referred to as TSource, in our sample TSource is int to remind) we could use the Where method directly on the collection instance (our collection here is “numbers” which is a collection of int). Here is our simplified version:

IEnumerable<int> odds2 = numbers.Where( n => n % 2 != 0 );

odds2.Dump(“Odd numbers using method syntax – 2”);

// Could write those 2 lines as one – didn’t do like that just to prevent confusion:

// numbers.Where( n => n % 2 != 0 ).Dump(“Odd number using method syntax – 2”);

Method syntax is easier IMHO and moreover it lets us to use the second overload of Where(). In second overload “int” parameter is an index to item’s location (in forums you should have seen questions like: “how can I get last 3 orders per customer”, this parameter lets you go further and in your inline method you can have criteria for the position – not supported on all Linq flavors, i.e.: Linq to SQL doesn’t support it). Let’s use it in our next sample to select odd numbers from our set “where” the number’s position in array is greater than 3rd element and less than 6th element (remember in C# sequences start at 0):

numbers.Where( (n, i) => n % 2 != 0 && i > 2 && i < 5 ).Dump(“Odd numbers from a subset”);

Note that we didn’t do any sorting (yet) last query would return 5,53. Play with simple queries using Where() method to get used to it, you wouldn’t regret you learned method syntax (especially when you see chained multiple methods which we don’t see in SQL). You can mix query and method syntax. By the way, whatever syntax you use intellisense kicks in and helps you on the way.

PS: n => n % 2 != 0 is a lambda expression and is typed (actually its type name is LambdaExpression). Typed > has a type (class) > is reusable, can be saved, loaded, passed around as a parameter (not something that we see with SQL and for that reason I think hard to understand initially).

Posted in Uncategorized | Leave a comment

>Simplest table and query

>

We made a head start to Linq skipping its basics and gave a sample from Entity Framework. Both Linq and Entity Framework are huge and much more capable than that sample could show. Linq can be done against any enumerable source means that we can treat a much wider set of source as ‘data’ and query over them using the same set of methods.  Linq have two syntaxes, one called Query syntax and the other method syntax. You can use the one you like but some features are only supported through method syntax (and as you get used you it method syntax sounds to be easier).

We will use LinqPad for a series of reasons:

    A cool interface that looks similar to and can use in place of SQL server management studio. We can interactively write and run C# (or VB) expressions, statements, program or even T-SQL. We can have multiple of them active at the same time shown under different tabs. It has a nice way of showing the results in grids (most of the time grids). Those grids also show the power of .Net from another aspect. It automatically writes in additional syntaxes and IL code where appropriate (i.e.: even if you wouldn’t use .Net at all and good in object query more than you are in T-SQL, you could write your query in Linqpad, test and get the corresponding T-SQL code from results’ window SQL tab. Below is a sample)

from c in Customers
  where c.Country == "USA" && c.Orders.Any(o => o.OrderDate == new DateTime(1997,1,1))
  select c

is simplier than writing corresponding T-SQL and more natural and shows what is really sent to SQL server. Here is what it produced  on my box against SQLExpress 2005, Northwind sample database:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE ([t1].[OrderDate] = @p1) AND ([t1].[CustomerID] = [t0].[CustomerID])
    ))
— @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
— @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [01.01.1997 00:00:00]
— Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

You could write it in multiple ways and interestingly enough the code generated is almost the exact query that I would suggest if I saw someone asking something like:

“Using Northwind data, how could I get the customers who are from USA and had an order on 1/1/1997?”

    Saving, loading of your own codes is a snap. With its samples tab it is not simply a tool but a tutorial in itself. Assuming you picked the autocomplete activation for a few bucks, it simplifies the writing of code as if you are in VS.

In summary, LinqPad is a cool utility deserving more than this promotional words. Let’s put up the LinqPad and start to investigate. LinqPad’s samples are nice, do not forget to check them out.

The simplest enumerable thing would be an array. Starting with an integer array lets check Linq syntax, extension methods and others:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var query = from d in digits select d;

foreach(var digit in query)
{
  Console.WriteLine(digit);
}

It is a lot of code, yes., but of course it is because I started with a long and verbose code. Now I will make analogies to SQL but Linq is not SQL, it might be strange to compare them (you better try to think in terms of objects and gets accustomed to it ASAP). You can think of “digits” as a single column table. The simplest select query would be:

select * from digits 

In Linq query syntax we start with “from” and:

from d in digits

is a way of saying – OK I want ‘rows’ from ‘digits’ table and I will refer to each ‘row instance’ as ‘d’. In that sense, ‘select d’ means ‘all columns of’ or ‘select *’. Assigning it to ‘query’ is like ‘into cursor query’ (with the exception ‘select … into cursor query’ wouldn’t really execute until foreach(…) that demands its result – foreach() is not the only way to demand results).

foreach(var digit in query) { // do whatever }

is a simple loop which you can think of as ‘for each … endfor’ or ‘scan…endscan’. In VFP that code would be something like:

select * from digits into cursor query && assuming we have a single column named d
scan
   ? d && or query.d
endscan

Hmm, you say, but in VFP I have the luxury of using xbase and could simply do it like this:

use digits
scan
   ? d
endscan 

In C# you didn’t really need that ‘from … ‘ and could simply use ‘digits’ itself in the same manner:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
foreach(var digit in digits)
{
  Console.WriteLine(digit);
}

This is plain old C#, no Linq in sight. However, if it were a table like thing we would expect to be able to do filtered selections, aggregations like average, sum… etc. If you didn’t pay for LinqPad’s autocomplete activation do this in VS:    

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var myCursor = digits.

As soon as you put the dot intellisense would kick in (in case of VS it would kick in earlier) and list PEM of digits.

Linq1_Intellisense

Methods are shown with a pink icon and those methods having blue arrow next to pink icon are Extension Methods defined in Linq namespace. If you disable Linq extensions by commenting using System.Linq you see those methods are gone from the list:

Linq2_NoIntellisense

In LinqPad some namespaces you need frequently are implicitly imported and you don’t need those using lines – and you could add other namespaces you may need using F4. In LinqPad there is a cool method named "Dump()" that "eats" anything you throw at it! Use it to easily check your results and play with ‘digits table’ in LinqPad, try things like:

digits.First().Dump();
digits.Sum().Dump("Sum of Digits");
digits.Average().Dump();

or have more fun with queries like:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var query1 = from d in digits where d > 5 select d;
query1.Dump();
var query2 = from d in digits where d % 2 == 0 orderby d descending select d;
query2.Dump();
var query3 =
  from d in digits
  select new {
             d,
             even = (d % 2 == 0),
             sqr = d * d,
             cube = d * d * d,
             bitset = 1 << d
             };
query3.Dump();
var query4 =
  from dColumns in query3
  group dColumns by dColumns.even into groupedCursor
  select groupedCursor;
query4.Dump();

    query1 adds a simple ‘where’ clause. query2 demonstrates an ‘order by’. query3 demonstrates projection (like SQL’s as clause – select d, (d%2 = 0) as even, d*d as sqr … ) query4 demonstrates querying from another query (query3 – like making a select from a previous cursor) plus ‘group by’. Note that group by result is a little different than SQL group by result and is a Key, Collection list ( like a table of grouping keys that contain their related items ):

Linq3_Grouping

Are we having fun yet? Just wet our toes:)

Posted in Uncategorized | Leave a comment