>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.

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