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

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.