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:

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.

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:

• Visual Studio Express
• MySql Server
• .NET Connector
• Demo Database