SelectMany or multiple from

SelectMany method doesn't exist as query keyword and it's used to flatten resulting sequence into single sequence. For example let's select titles of all employees younger as 50 years.
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);
emp.Log = Console.Out;

var results = from e in emp.Employees
            where e.BirthDate > DateTime.Now.AddYears(-50)
            select e.Titles;

foreach (var a in results)
    Console.WriteLine(a);

Listing 11.1

The result will contain arrays of Titles and we need just list of titles. An additional from select keyword paar help us to retrieve flat titles.
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);
emp.Log = Console.Out;

var results = from e in emp.Employees
            where e.BirthDate > DateTime.Now.AddYears(-50)
            from t in e.Titles
            select t.Title1;

foreach (var a in results)
    Console.WriteLine(a);

Listing 11.2

groupby clause

group by statement defined as
groupby-clause ::= group selExpr by keyExpr
That is powerful tool for grouping by single or compund property(s), also function can be applied to key property. In simplest case group statement uses itemName (from itemName) as selExp.
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var results = from e in emp.DeptEmps
                group e by e.Department.DeptName;

foreach (var a in results)
{
    Console.WriteLine(a.Key.ToString());
    foreach (var b in a.Take(10) )
        Console.WriteLine("\t"+b.Employee.LastName);
}

Listing 10.1

If you misstype variable name var results = from e in emp.DeptEmps group i by e.Department.DeptName; appears strange error message: Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer<>' because it is not a delegate type. Check carefully selExp. It's possible here to construct new anonymous type as selExp almost with the same result set.
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var results = from e in emp.DeptEmps
                group new { e.Department.DeptName, e.Employee.LastName } by e.Department.DeptName;

foreach (var a in results)
{
    Console.WriteLine(a.Key.ToString());
    foreach (var b in a.Take(10) )
        Console.WriteLine("\t"+b.LastName);
}

Listing 10.2

The output from both listings will be the same Listing10 let's find in next example all full namesake in employee set, it should much first name and last name. We should use compound key and extended group by into.
         MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
         var emp = new EmployeeDataContext(conn);

         var results = from e in emp.Employees
                       group e by new { e.LastName, e.FirstName } into g
                       where g.Count()>1
                       select g.Key;

         foreach (var a in results)
             Console.WriteLine(a);
     }

Listing 10.3

Deferred Execution and Lazy vs. Eager Evaluation in LINQ. Part 2.

Julien Roncaglia has introduced in three examples absolutely crystal explanation, I can only quote: Considering a function
int Computation(int index)
Immediate execution
IEnumerable GetComputation(int maxIndex)
{
    var result = new int[maxIndex];
    for (int i = 0; i < maxIndex; i++)
    {
        result[i] = Computation(i);
    }
    return result;
}
When the function is called Computation is executed maxIndex + 1 times GetEnumerator return a new instance of the enumerator doing nothing more. Each call to MoveNext put the the value stored in the next Array cell in the Current member of the IEnumerator and that's all. Cost: Big upfront, Small during enumeration (only a copy) Deferred but eager execution
IEnumerable GetComputation(int maxIndex)
{
    var result = new int[maxIndex];
    for (int i = 0; i < maxIndex; i++)
    {
        result[i] = Computation(i);
    }
    foreach (var value in result)
    {
        yield return value;
    }
}
When the function is called an instance of an auto generated class (called "enumerable object" in the spec) implementing IEnumerable is created and a copy of the argument (maxIndex) is stored in it. GetEnumerator return a new instance of the enumerator doing nothing more. The first call to MoveNext execute maxIndex+1 times the compute method, store the result in an array and Current will return the first value. Each subsequent call to MoveNext will put in Current a value stored in the array. Cost: nothing upfront, Big when the enumeration start, Small during enumeration (only a copy) Deferred and lazy execution
IEnumerable GetComputation(int maxIndex)
{
    for (int i = 0; i < maxIndex; i++)
    {
        yield return Computation(i);
    }
}
When the function is called the same thing as the lazy execution case happens. GetEnumerator return a new instance of the enumerator doing nothing more. Each call to MoveNext execute once the Computation code, put the value in Current and let the caller immediately act on the result. Most of linq use deferred and lazy execution but some functions can't be so like sorting. Cost: nothing upfront, Moderate during enumeration (the computation is executed there) To summarize Immediate mean that the computation/execution is done in the function and finished once the function return. (Fully eager evaluation as most C# code does) Deferred/Eager mean that most of the work will be done on the first MoveNext or when the IEnumerator instance is created (For IEnumerable it is when GetEnumerator is called) Deferred/Lazy mean that the work will be done each time MoveNext is called but nothing before. Parallel LINQ does it a little differently as the computation could be considered deferred/Lazy from the point of view of the caller but internally the computation of some number of elements begin in parallel as soon as the enumeration begin. The result is that if the next value is already there you get it immediately but otherwise you will have to wait for it. Source: stackoverflow.com/a/2515920

orderby clause

The orderby statement sorts the sequence in ascending or descending order. The default sort order is ascending. Additional sorting is possible with several keys. orderby clause is defined as
orderby-clause ::= orderby (keyExpr (ascending | descending)?)*
Example
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var results = from e in emp.Employees
                orderby e.LastName, e.FirstName descending, e.BirthDate ascending
                select new { e.LastName, e.FirstName, e.BirthDate};

foreach (object b in results.Take(10))
    Console.WriteLine(b);

Listing 9.

The output is Listing9

Deferred Execution and Lazy vs. Eager Evaluation in LINQ. Part 1.

I would like to cite Charlie Calvert, he has clearly described deferred execution. Consider this simple LINQ to SQL query:
var query = from customer in db.Customers   
            where customer.City == "Paris" 
            select customer;               
It is easy to assume that these few lines of code execute a query against a database. In fact, they do not. This code merely captures the idea of the query in a data structure called an expression tree. The tree contains information about the table you want to query, the question you want to ask of the table, and the result you want to return. It does not, however, actually execute the query! In LINQ, execution of a query is usually deferred until the moment when you actually request the data. For instance, the following code would cause the query to execute:
foreach (var Customer in query) // Query executes here
{
  Console.WriteLine(Customer.CompanyName);
}
Query expressions often do not cause code to be executed. They only define the question you want to ask. If this were not the case, then it would be difficult or impossible for LINQ to break queries down into a relational algebra which makes composability possible, and which allows developers who care about such things to optimize their code. Deferred execution makes it possible for you to compose quite complex queries from various components without expending the clock cycles necessary to actually query the data. Or at least the data will not be queried until it is absolutely necessary. Let’s make a slight change to the query shown above:
var query = (from customer in db.Customers 
             where customer.City == "Paris"
             select customer).Count(); // Query executes here 
This time the query will be run when the execution point moves past it. The code executes when you call Count(), as it would when you call any of the other operators that must iterate over the result of a query in order to return a value that is not IEnumerable<T> or IQueryable<T>. The ToList() operator the query code to execute immediately because it returns a List<T> instead of IQueryable<T>. Consider the following code:
public void SimpleQuery01()
{
  db.Log = Console.Out;

  // Query executes here because it returns a List;
  List list = (from customer in db.Customers
                         where customer.City == "Paris"
                         select customer).ToList();

  foreach (var Customer in list)
  {
    Console.WriteLine(Customer.CompanyName);
  }
}
One of the last things the LINQ to SQL provider does before executing a query is to create the SQL it will send across the wire. This fact gives us a clue we can use when trying to determine the exact moment when a query executes. Full article: blogs.msdn.com/b/charlie/archive/2007/12/09/deferred-execution.aspx

where clause

where keyword is defined as
where-clause ::= where predExpr
and used to filter elements from the source sequence. In the following example will be find all salaries of the employee "Kristen Merro" starting from 2000-01-14.
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var lastSalaries = from e in emp.Employees
                    join s in emp.Salaries on e.EmpNo equals s.EmpNo
                    where e.FirstName == "Kristen" && e.LastName == "Merro"
                    && s.FromDate > new DateTime(2000, 01, 14)
                    select new { e.LastName, s.FromDate, s.Salary1 };

foreach (object b in lastSalaries)
    Console.WriteLine(b);

Listing 8.1

only instead of && (AND) operator the where clause can be chained as shown below
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var lastSalaries = from e in emp.Employees
                    join s in emp.Salaries on e.EmpNo equals s.EmpNo
                    where e.LastName == "Merro"
                    where s.FromDate > new DateTime(2000, 01, 14)
                    where e.FirstName == "Kristen" 
                    select new { e.LastName, s.FromDate, s.Salary1 };

foreach (object b in lastSalaries)
    Console.WriteLine(b);

Listing 8.2

Conclusion: DBLinq 2007

DBLinq is not matured product yet. At least in conjunction with MySql. Nested queries are not implemented yet. Simple grouping failed also, the following example produce run-time error.
var lastSalaries = from s in emp.Salaries
    orderby s.EmPNo ascending, s.ToDate descending
    group s by s.EmPNo into grp
    let o = grp.First()
    select new { o.EmPNo, o.Salary }; 
:( Looking forward for new releases.

Drawback of Last() method in LINQ to SQL

Note that Last() method can be extremely slow on big data set. For example Listing 7.1 takes about 10 seconds. The reason is the Last() method can't be translated into SQL query and the whole table was loaded into the memory.
var result =
    (from s in emp.Salaries
        select s.Salary).Last();

Listing 7.1

If you have unique id in the table the Last() can be replaced with orderby and First() method, that will be much more efficient
var result =
    (from s in emp.Salaries
        orderby s.EmPNo descending
        select s.Salary).First();

Listing 7.2

let clause

MSDN says
In a query expression, it is sometimes useful to store the result of a sub-expression in order to use it in subsequent clauses. You can do this with the let keyword, which creates a new range variable and initializes it with the result of the expression you supply. Once initialized with a value, the range variable cannot be used to store another value. However, if the range variable holds a queryable type, it can be queried.
let clause defined as
let-clause ::= let itemName = selExpr
let us calculate once average salary and then compare it to last salary of each employee using let keyword
   
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new EmployeeDataContext(conn);

var lastSalaries = from s in emp.Salaries
                    orderby s.EmpNo ascending, s.ToDate descending
                    group s by s.EmpNo into grp
                    let o = grp.First()
                    let averageSalary = (from s in emp.Salaries select s.Salary1).Average()
                    select new { o.EmpNo, o.Salary1, MoreOrLess = (averageSalary > o.Salary1) };

foreach (object b in lastSalaries)
   Console.WriteLine(b);

Listing 6.

LINQ to Objects

LINQ allows to query any set that implements IEnumerable, IEnumerable<T> or IQueryable<T>. Array class has extension method Cast<T> which returns IEnumerable<T>, so any Array can be also querable srcExpr. Let's make DNS query with LINQ and find all google.com addresses. GetHostEntry() method returns array of IPAddress.
      
IEnumerable list = Dns.GetHostEntry("www.google.com").AddressList;

var result = 
    from s in list
    let addr = s.ToString()
    select new { addr } ;

foreach (var a in result)
    Console.WriteLine(a);

Listing 5.

join clause

join keyword defined as
join-clause ::= join itemName in srcExpr on keyExpr equals keyExpr
(into itemName)?
Let's select all department's names and managers of department. join clause is used to combine 3 tables with many-to-many relationship.
var result =
    from dep in emp.Departments
    join dm in emp.DEPtManager on dep.DEPtNo equals dm.DEPtNo
    join chef in emp.Employees on dm.EmPNo equals chef.EmPNo
    select new { dep.DEPtName, chef.LastName };

foreach (var a in result)
    Console.WriteLine(a);

Listing 3.

In our case relationships are defined using parent/child lists and query can be simplified. The DataContext hides details of implementation.
var result =
    from dep in emp.Departments
    from dm in dep.DEPtManager
    select new { dep.DEPtName, dm.Employees.LastName };

foreach (var a in result)
    Console.WriteLine(a);

Listing 4.

Result will be the same: Listing4_Output

Linq to MySql using DataContext

To use all facilities like IntelliSense or strong type binding we have to create native DataContext for our Employees mysql database. DbLink helps us to do that. DBLink has utility DBMetal.exe (aka SQLMetal) which analyses database structure and generates DBML file and corresponding classes also. After that I can rewrite "Hello, World" as
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
var emp = new DB_Employees(conn, new DbLinq.MySql.MySqlVendor());

var result = 
    from s in emp.Employees.Skip(20).Take(10)
    select s.FirstName;

Listing 2.

Look it's better?

from clause

from keyword is defined as
from-clause ::= from itemName in srcExpr
MSDN says:
A query expression must begin with a from clause. Additionally, a query expression can contain sub-queries, which also begin with a from clause. The from clause specifies the following:
  • The data source on which the query or sub-query will be run.
  • A local range variable that represents each element in the source sequence.
Both the range variable and the data source are strongly typed. The data source referenced in the from clause must have a type of IEnumerable, IEnumerable<T>, or a derived type such as IQueryable.
Or it can be replaced with iteration statement foreach, where func is delegate:
foreach(var itemName in srcExpr){
   func(itemName);
}

Hello, world!

It's time for Hello, world! programm. Let's select first N records using tools, schema and LINQ.  
MySqlConnection conn = new MySqlConnection("SERVER=localhost; DATABASE=employees;UID=root;PASSWORD=");
conn.Open();
DataSet ds = new DataSet("employees");
MySqlDataAdapter sda = new MySqlDataAdapter("select * from salaries", conn);
sda.Fill(ds, "salaries");

var result = 
    from s in ds.Tables["salaries"].AsEnumerable().Take(10)
    select s;

Listing. 1

Warning! Don't use the example in real application! Please leave a comment why it's bad designed.

Employee Demo Database Description

Demo database is provided by launchpad with Creative Commons license for test and learning purpose.

It contains departments (9), deptartment employees (331603), deptartment managers(24), employees(300024) with titles(443308) and salaries(2844047). The structure is provided below. Schema

Fig. 1 - Schema

   

LINQ Syntax

LINQ Syntax has quite simple BNF (Backus-Naur Form) notation  
query-expression ::= from-clause query-body

query-body ::=

query-body-clause* final-query-clause query-continuation?

query-body-clause ::=
(from-clause
| join-clause
| let-clause
| where-clause
| orderby-clause)

from-clause ::= from itemName in srcExpr

join-clause ::= join itemName in srcExpr on keyExpr equals keyExpr
(into itemName)?

let-clause ::= let itemName = selExpr

where-clause ::= where predExpr

orderby-clause ::= orderby (keyExpr (ascending | descending)?)*

final-query-clause ::=
(select-clause | groupby-clause)

select-clause ::= select selExpr

groupby-clause ::= group selExpr by keyExpr

query-continuation ::= into itemName query-body

Programming LINQ with free tools

I would like to use free and open source tools to learn LINQ programming. You can also take Visual Studio Express, MySQL Server with .NET Connector and some demo database suite. Tools:

HOWTO: Repair Logitech M325 Mouse

FixIt says that you will find single screw under CE label. It isn't always true.