>LINQ Query Operators – Part II

>

Projection Operators:

Select: Selects items from a source, projecting the results as necessary.

When the input sequence would be selected as is without any projection ( aka * in T-SQL ) using Select() is not necessary and looks a little ugly. i.e.:

Customers.Select( c => c )
 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Instead we would simply write just "Customers" which is the enumerable itself. In query syntax it is the same as writing:

from c in Customers select c
 
Here are some Select projections and its Query expression, T-SQL counterparts:
 
T-SQL
Select CompanyName from customers
Query Syntax
from c in Customers
     select c.CompanyName
Method Syntax
Customers.Select( c => c.CompanyName )
 
T-SQL
Select 
  firstName, lastName,
  firstName +  ‘ ‘ + lastName As fullName
from employees
Query Syntax
from e in Employees
select 
  new {
        e.FirstName,
        e.LastName,
        fullName = e.FirstName + " " + e.LastName }
Method Syntax
Employees
  .Select( e =>
  new {
     e.FirstName,
     e.LastName,
     fullName = e.FirstName + " " + e.LastName } )
 
There is another overload of Select() which accepts an index parameter and is not supported by Linq To SQL and EF. Here is a simple example that lists the DBF files from VFP samples data folder enumerating them with their position (reminder: in C# indexing, arrays start from 0):
 
Directory.GetFiles(
   @"c:\Program Files\Microsoft Visual Foxpro 9\Samples\Data""*.dbf")
   .Select( (filename,position) =>
        new {fileNo = position+1,filename} )
 
In the samples above, with the "new" keyword the type created is an anonymous type. Sometimes we would want to create a type of our own (similar to "into cursor cursorName" – cursorName might a different structure than the source). To do that we could create a new class and qualify the "new" type with that class. i.e.:
 
void Main()
{
  var q = from c in Customers
          select new
          myCustomer {
            ID = c.CustomerID,
            company = c.CompanyName,
            orderCount = c.Orders.Count
          };

  q.Dump();
}

public class myCustomer
{
 public string ID {get;set;}
 public string company {get;set;}
 public int orderCount {get;set;}
}

 
This produces the SQL:
 

SELECT
  [t0].[CustomerID] AS [ID],
  [t0].[CompanyName] AS [company],
  (
    SELECT COUNT(*)
    FROM [Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
  ) AS [orderCount]
FROM [Customers] AS [t0]

SelectMany: The Select() method works as Select( input =>output ) where it produces one output per input. Consider a query getting customers and orders of customers (maybe customerID and CompanyName from Customers and OrderID, OrderDate from orders to show simpler output):
 
Select 
   [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID] As [OrderID], [t1].[OrderDate]
FROM [Customers] As [t0], [Orders] As [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
 
This trivial SQL generates a 2D flat table.  If we attempt to create this with a Select() we could do that using an expression like below:
 

Orders.Select( o =>
   new 
   {
     o.Customer.CustomerID,
     o.Customer.CompanyName,
     o.OrderID,
     o.OrderDate
   } )

Which produce this SQL:
 
Select 
[t1].[CustomerID], [t1].[CompanyName], 
[t0].[OrderID], [t0].[OrderDate]
FROM [Orders] As [t0]
LEFT OUTER JOIN [Customers] As [t1] 
On [t1].[CustomerID] = [t0].[CustomerID]
 
and the flat 2D result that we are accustomed to. If you look at the SQL closely you would notice that it is sort of cheat creating an inner join (Orders driving the results and already each Order has a "parent" Customer, this in effect is an inner join). We could also look at it as:
 
use Customers order tag CustomerID
select 0
use Orders
set relation to CustomerID into Customers
 
The old Indian trick that we used in reports in old days to get all orders of customers using relations. If we did the relation from Customers into Orders and then browse each table side by side remember per Customer in left browse we would see "Many" Orders on right. Or better yet we all wanted to have Grid in Grid or hierarchical grids showing one Customer in a row and her Orders in a grid in another grid cell. Doing that is not trivial except using relations and that would be a single "thing". Now that we are in objects world a single object would be representing a data structure where per customer "row" there are multiple order "rows" (think of a report where data is grouped on CustomerID). If we have started from Customers (and remember we get one output per input) we would end up with such an "object" which is an ordered enumerable of Customers along with its Orders. Here is our new query:
 
Customers.Select( c =>
   new  
   {
     c.CustomerID,
     c.CompanyName,
     OrderList = c.Orders.Select( o =>  new {o.OrderID, o.OrderDate})
   } )
 
Producing SQL:
 
Select 
  [t0].[CustomerID], 
  [t0].[CompanyName], 
  [t1].[OrderID], [t1].[OrderDate], 
  (
    Select COUNT(*)
    FROM [Orders] As [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
  ) As [value]
FROM [Customers] As [t0]
  LEFT OUTER JOIN [Orders] As [t1]
  On [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
 
If we run this SQL we would get a flat list as always. However the Linq query returns an object result which is more like "parent-child" packed data (like a 3D table). Here is part of output from LinqPad to understand better (and I hope you are following these testing in LinqPad yourself):
 
Select
 
This is more like grouping that we may like. We may want this as a flat 2D as we are accustomed to and SelectMany() comes into play there.  With query syntax it is as easy as:
 
from c in Customers
from o in c.Orders
select new
   {
      c.CustomerID,
      c.CompanyName,
      o.OrderID,
      o.OrderDate
   }
 
this is converted to SelectMany() as shown here:
 
Customers
   .SelectMany (
      c => c.Orders, 
      (c, o) => 
         new  
         {
            CustomerID = c.CustomerID, 
            CompanyName = c.CompanyName, 
            OrderID = o.OrderID, 
            OrderDate = o.OrderDate
         }
   )
 
This concludes projection operators subject.
 
Note: I intentionally didn’t mention some details about operators shown and left out some operator(s) that may be part of categories we talked about in order to prevent confusion. Also note that these are standard operators. Linq is evolving and already there are many more operators than you would see in standard operators set. Trying to keep it simple and summarized I might be skipping details more than I should, I hope not.
Posted in Uncategorized | Leave a comment

>LINQ Query Operators – Part I

>

Orders.OrderBy(o => o.OrderDate).Skip(5)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Before checking the operators lets remind some points.

  • Query operators are extension methods
  • Some Linq operations are only available with operators (or to say it otherwise "method syntax")
  • Syntax includes the source itself which need not be specified (because implemented as static extension method on IEnumerable, IOW source). Typical syntax looks like:

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

      Looks like Chinese? It is very easy once you understand indeed. You can think of it as:

     IEnumerable<TSource> : It returns something that can be enumerated ( like an array, list, records …) – and its type is symbolically TSource.

     Where<TSource>( this IEnumerable<TSource> source :  First parameter is also something that can be enumerated and since it is an extension method of IEnumerable itself we don’t need to feed source parameter.

     Up to this point it is simple, gets something like a list and returns back another list. Just like an SQL select does.

     Func<TSource, Boolean> predicate) : Here comes the interesting part. It accepts a "function" that has the TSource type input parameter (you can think of TSource as a row of customer when the source is customers table) and returns back true or false. If true is returned "record" is selected.

     Now lets check this "Where" using in a Linq query and compare to its counterpart SQL select and Linq query syntax:

SQL select:

select * from Customers where Customers.Country = "USA" into cursor usaCustomers

LINQ Query Syntax:

IEnumerable<Customers> usaCustomers = from c in Customers where c.Country == "USA" select c;

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }LINQ Method Syntax:

IEnumerable<Customers> usaCustomers = Customers.Where( c => c.Country == "USA" );

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Lets dissect the method syntax and see different ways of writing the same thing:

Customers.Where( c => c.Country == "USA" );

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

TSource is Customers. In method Where(), c is the input parameter to function (remember it represents something like a Customers row, so it has columns as properties). it returns the result of check: c.Country == "USA" which is a true or false. We could write the same thing like this:

Customers.Where( ( c ) => c.Country == "USA" );

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

When parameter count is one, we wouldn’t want to do that however and simply use the previous syntax. When parameter count is multiple we put them within parentheses.

For readability we are allowed to write the exact same thing like this (the newline and extra whitespace between Customers and .Where is insignificant):

IEnumerable<Customers> usaCustomers = Customers
                                      .Where( c => c.Country == "USA" );

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Now that I think we are armed with basic syntax we can move on. LINQ operators are divided into categories. Members of categories may be different  from source to source but in general all sources have same categories (and some operators are listed in more than one category).

Filtering and Partitioning Operators:

Where:  This is the filtering operator which any VFP developer would be most familiar with. It has two forms

  1. Typical function getting a source and returning a boolean (filters out for the ones returning false)

    var usaCustomers = Customers.Where( c => c.Country == "USA" );

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }

  2. A function getting an index parameter besides source (this one is not supported with Linq To SQL or Linq to Entity Framework because there isn’t a supporting SQL for this. Practically speaking methods that do not a SQL counterpart do not work with L2S).

    int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
     
    var some = numbers
        .Where((c, index) => ( 
                             (c%2 == 0) &&  
                             (index >= 8) && (index <= 15)));
     
    some.Dump("Even numbers between positions 8-15");

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }

1st syntax simply select customers from USA. 2nd syntax limits the "records" to position (sort of recno()) to 8-15 and within that range select numbers that are even.

Skip: Skips N elements.

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
var some = numbers.Skip(5);
some.Dump("Skipped first 5 numbers");

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

In T-SQL it looks like:

select number from 
(select number,Row_Number() over (order by number) as row from numbers) numberList
where row > 5 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Actually if you run this against Northwind:

Orders.OrderBy(o => o.OrderDate).Skip(5)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This is the generated T-SQL code:

-- Region Parameters
DECLARE @p0 Int SET @p0 = 5
-- EndRegion
SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID],
   [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], 
   [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress],
   [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[OrderDate]) AS [ROW_NUMBER], 
        [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], 
        [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], 
        [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], 
        [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [Orders] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
ORDER BY [t1].[ROW_NUMBER]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You can also achieve the same result with a query like:

SELECT * from Orders
WHERE OrderId not in 
   (select top 5 OrderID from orders ORDER BY OrderDate)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Take: Takes top N – IOW typical TOP N query operator.

Orders.OrderBy(o => o.OrderDate).Take(5)

 
T-SQL counterpart:

SELECT TOP 5 * FROM [Orders] order by OrderDate

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 
Both Skip and Take have another version which is not supported by L2S:
 
SkipWhile: Skips over elements as long as a condition is true. It has 2 overloads:

  1. First overload skips while a condition is true.

    int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
    var some = numbers.SkipWhile( n => n < 5 );
    some.Dump("Skipped while number is less than 5");

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }

  2. Second overload skips while a condition is true and also gets an index parameter as input.

    int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
    var some = numbers.SkipWhile( (n, index) => index < 5);
    some.Dump("Skipped while index position is less than 5");

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }

TakeWhile: Takes elements as long as the condition is true. Similar to SkipWhile it has same 2 overloads.
 

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
var some = numbers.TakeWhile( n => n < 5);
some.Dump("Take while number is less than 5");

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
var some = numbers.TakeWhile( (n, index) => index < 5);
some.Dump("Take while index position is less than 5");

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You can combine or in other words chain these operators. Here is a sample (and already we did one before using OrderBy() above):

Orders
 .OrderBy( o => o.CustomerID)
 .Where( o => o.Customer.Country == "USA" )
 .Skip(5)
 .Take(3)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Note that using Skip() and Take() combination you can easily create a "paging query" for a web application. For example this gets the records 21-30 (Page 3 where pages are 10 records high) in a particular order of a paged query:

Customers.OrderBy(c => c.CustomerID).Skip( 2*10 ).Take(10)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Posted in Uncategorized | Leave a comment

>A few Linq resources

>

It has been a long time since I posted my last entry. Sorry about that. I still don’t have time for long posts but I thought I could at least provide some pointers to resources, samples, tools and such.

Here is a new book that is published recently:

Essential LINQ
by Charlie Calvert and Dinesh Kulkarni
Publisher: Addison-Wesley Professional
Pub Date: March 12, 2009
Print ISBN-10: 0-321-56416-2
Print ISBN-13: 978-0-321-56416-0
Web ISBN-10: 0-321-60475-X
Web ISBN-13: 978-0-321-60475-0
Pages: 600

I haven’t read this book myself and learned about it today just by accident while testing a Linq sample delivered by Microsoft:)

Be sure to check samples in LinqPad and if you have Visual Studio 2008 check the samples delivered with VS located at:

C:\Program Files\Microsoft Visual Studio 9.0\Samples\1033\CSharpSamples\LinqSamples

Samples do not exist in Express version as far as I know but you could get the online version which is updated and maybe more recent then the one you have from:

http://code.msdn.microsoft.com/csharpsamples

Some more Linq flavors (not released yet – alpha,beta):

  • Parallel Linq
  • Linq to SharePoint
  • Linq to Active Directory

And finally I want to include a short reply to a question asked by a friend:

Question: Is Linq about querying data or can we use it to update the data too?

Answer: As I remember I told this before, Linq might be a misnomer and it is also used for updating the data where applicable. How we do it depends on which flavor we are using, but the key point is that while doing that we use an "object oriented" approach.

Posted in Uncategorized | Leave a comment

>Query keywords – join, let

>

join: Similar to SQL join, the join keyword is used to join sets. One thing you should note that, if there is a direct relationship in the object model a join is not needed. This behavior differs from SQL. Consider the infamous Northwind database. In SQL server (and in VFP northwind.dbc sample database) there are relations between the tables defined.  You can’t simply create joins without specifying a relation in your SQL:

select * from Customers, Orders

would be a newbie’s error (most of the time) in an attempt to join two tables and expect them to be joined using CustomerId. End result would be a Cartesian join matching each Customer with every Order in Orders set. With Linq you can make the same mistake but it is harder to do:

from c in Customers
from o in Orders
select new { c.CustomerID, o.OrderID }

With the above SQL our intention would likely be an equijoin:

select Customers.CustomerID, Orders.OrderID from Customers, Orders where Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers left join Orders on Customers.CustomerID = Orders.CustomerID

First two are typical inner join queries and 3rd left outer join. In Linq writing such queries is easy. You could write the inner join like this one:

from c in Customers
from o in c.Orders
    select new { c.CustomerID, o.OrderID }

and left outer join like this one:

from c in Customers 
    select new { c.CustomerID, OrderID = c.Orders.Select( o => o.OrderID ) }

While you could use "join" in these, join in Linq is for creating joins on properties that doesn’t already define a relationship in object model (it is easy to forget we are doing a query against objects and object models). Within a database it is unlikely to make join queries that are not already defined with relationships but you may never know what you need. And you wouldn’t always use Linq against collections that come from database based or some other type of well defined object model. We will however see "join" here with already well defined relationships just for the sake of your familiarity with those table and key field names. It makes it easier to guess the result without actually seeing it then.

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
select new {c.CustomerID, o.OrderID }

I should also note that using join on primary – foreign key properties causes an "inner join" to be generated. There is also another type of join called "Group Join". You can create group join using "join" and "into" clauses (keywords) together:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into orderGroup
select new {c.CustomerID, orderGroup }

Or if we remember that we are working with objects could write it like this as well:

from c in Customers
join o in Orders on c equals o.Customer into orderGroup
select new {c.CustomerID, orderGroup }

But also there is one another and maybe easier to write and understand variation of the same query (and both generate the same left outer join) – provided there is a relationship that we can use in object model (you may call it "relationship" or "navigational property"):

from c in Customers select new {c.CustomerID, c.Orders}

let: Let is a cool keyword  that allows you to store inline expressions. Those who use SQL server 2005 and later know that how much CTE (Common Table Expression – with {…} as ) is welcomed easing the writing of queries and increasing the performance as well. Let keyword is not CTE of course but looks very similar. Using it you assign a variable a complete expression inline creating a "sub-expression". That sub-expression is evaluated only once and can be used in operations within the outer expression. Now let’s see how let lets you < g > write more succinct expressions. I will start with an exact copy from LinqPad’s 5 minutes samples:

from p in Products
let spanishOrders = p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")
where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),   
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

Here spanishOrders is an inline variable (well not very true to call it variable, purists should forgive me) whose value is assigned from 

p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")  

expression. This expression is the same as this one written in comprehension syntax:

from o in p.OrderDetails where o.Order.ShipCountry == "Spain" select o

(and you could replace it with this one enclosing it in parentheses). It is later used in 3 places:

where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),   
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

I think you can see it without any further explanation. It is acting like an extraordinary SQL subquery (but it doesn’t need to be something like a subquery, any expression would do and if that expression is returning something queryable you can query it). Here is another sample (this sample is from .Net documentation, you can see the original here):

string[] strings =
        {
            "A penny saved is a penny earned.",
            "The early bird catches the worm.",
            "The pen is mightier than the sword."
        };

// Split the sentence into an array of words
// and select those whose first letter is a vowel.
var earlyBirdQuery =
    from sentence in strings
    let words = sentence.Split(‘ ‘)
    from word in words
    let w = word.ToLower()
    where w[0] == ‘a’ || w[0] == ‘e’
        || w[0] == ‘i’ || w[0] == ‘o’
        || w[0] == ‘u’
    select word;

earlyBirdQuery.Dump();

There are two let keywords here. First one gets each sentence and stores the result of Split(‘ ‘) function to words. "words" is another enumerable ( words of sentences in string ) and it is followed by another query ( from word in words ). For each word second "let" stores the result of word.Lower() to "w". "where" clause use that w multiple times. Without "let" word.Lower() itself would be called multiple times ( where w[0] == ‘a’ || w[0] == ‘e’ … w[0] == ‘u’ ).

Here is another sample which I wrote as a reply on MSDN C# forum. The question was (as I remember it):

There is tickdata like this one:

create table tickdata (timed datetime, price money, quantity int)
insert into tickData values (’06:01′,100,20)
insert into tickData values (’06:02′,90,30)
insert into tickData values (’06:03′,129,10)
insert into tickData values (’06:11′,112,8)
insert into tickData values (’06:12′,150,60)
insert into tickData values (’06:20′,110,10)
insert into tickData values (’06:23′,120,5)
insert into tickData values (’06:24:59′,130,15)
insert into tickData values (’06:25′,140,35)

From this data generate stock trade like data that looks like:

period open low high close volume
06:00 – 06:05 100.0000 90.0000 129.0000 129.0000 60
06:10 – 06:15 112.0000 112.0000 150.0000 150.0000 68
06:20 – 06:25 110.0000 110.0000 130.0000 130.0000 30
06:25 – 06:30 140.0000 140.0000 140.0000 140.0000 35

where period is defined as 5 minute intervals starting from the first available tickdata time and shows for each period with data. This was the Linq solution I came up with (besides "let" keyword there is grouping and query methods):

from myData in Tickdata.OrderBy( t => t.Timed ).AsEnumerable()
  group myData by ((int)myData.Timed.Value.TimeOfDay.TotalMinutes) / 5 into barData
  let bStart = TimeSpan.FromMinutes( barData.Key * 5 ).ToString()
  let bEnd   = TimeSpan.FromMinutes( (barData.Key+1) * 5 ).ToString()
  select new
  { period = bStart.Substring(0,5) + " – " + bEnd.Substring(0,5),
    open   = barData.FirstOrDefault().Price,  
    low    = barData.Min( bd => bd.Price ),
    high   = barData.Max( bd => bd.Price ),
    close  = barData.LastOrDefault().Price,
    volume = barData.Sum( bd => bd.Quantity ) }

This concludes our query keywords series. Other keywords are conceptual ones used to complete syntax (in, on, equals, by, ascending, descending).

Posted in Uncategorized | Leave a comment

>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