public static void Nested_Readers( IKLink link )
{
Console.WriteLine( "\n===== Nested readers..." );
var cmd = link.From( x => x.Countries ).OrderBy( x => x.Name );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var ctry in cmd.ConvertBy( rec => {
CountryTable table = new CountryTable(); dynamic d = rec;
table.Id = d.Id;
table.Name = d.Name;
table.RegionId = d.RegionId;
_Nested_Readers_Employees( link, table );
return table;
} ) ) Console.WriteLine( "\n> Country => {0}", ctry );
cmd.Dispose();
}
static void _Nested_Readers_Employees( IKLink link, CountryTable ctry )
{
var cmd = link.From( x => x.Employees ).Where( x => x.CountryId == ctry.Id );
DEBUG.IndentLine( "\n> Command => {0}", cmd );
foreach( var emp in cmd.ConvertBy( rec => {
EmployeeTable table = new EmployeeTable(); dynamic d = rec;
table.Id = d.Id;
table.FirstName = d.FirstName;
table.LastName = d.LastName;
table.CountryId = d.CountryId;
ctry.Employees.Add( table );
return table;
} ) ) DEBUG.WriteLine( "\n> Employee => {0}", emp );
cmd.Dispose();
DEBUG.Unindent();
}
public static void Query_WithChainedFroms( IKLink link )
{
Console.WriteLine( "\n===== Query using a chained From syntax..." );
var cmd = link
.From( x => x.Regions.As( x.Super ) ).Where( x => x.Super.Name == "Europe, Middle East & Africa" )
.From( x => x.Regions.As( x.Reg ) ).Where( x => x.Reg.ParentId == x.Super.Id )
.From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.RegionId == x.Reg.Id )
.From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.CountryId == x.Ctry.Id )
.Select( x => x.Emp.All() )
.Select( x => x.Reg.All() )
.OrderBy( x => x.Reg.Id ).OrderBy( x => x.Emp.Id );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Fake_Query_Extended_Syntax( IKLink link )
{
Console.WriteLine( "\n== A fake query to show the extended syntax usage..." );
var cmd = link
.From( "Employees AS Emp" )
.GroupBy( x => x.Cube( x.C1, "C2 with Embedded Spaces" ) )
.OrderBy( x => x( x.C3, "Text without meaning", x.C4 >= 9 ) )
.Select( "C5 as C6" );
Console.WriteLine( "\n> Command => {0}", cmd );
Console.WriteLine( "\n> NOT EXECUTED AS IT HAS NO MEANING ..." );
}
public static void Query_WithSourceFroms( IKLink link )
{
Console.WriteLine( "\n===== Query using a command as the From source..." );
var cmd = link
.From(
link.From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.Id == "us" ),
x => x.Location )
.From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.CountryId == x.Location.Id )
.Select( x => x.Emp.All() );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Query_With_In_and_Equal( IKLink link )
{
Console.WriteLine( "\n===== Query using the IN and EQUAL syntax..." );
var cmd = link
.From( x => x.Employees ).Where( x => !x.CountryId.In(
link.From( y => y.Countries ).Select( y => y.Id ).Where( y => y.RegionId.In(
link.From( z => z.Regions ).Select( z => z.Id ).Where( z => z.ParentId =
link.From( p => p.Regions ).Select( p => p.Id )
.Where( p => p.Name == "Europe, Middle East & Africa" )
) ) ) ) );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Query_GettingOneValue( IKLink link )
{
Console.WriteLine( "\n===== Query getting one value instead of a record..." );
// As anonymous columns are not supported, we need to give a name to it
// The easiest way is by using an alias for the object returned by the database
var cmd = link
.From( x => x.Employees )
.Select( x => x.Count( x.Id ).As( x.SumOfEmployees ) );
Console.WriteLine( "\n> Command => {0}", cmd );
dynamic obj = cmd.First();
Console.WriteLine( "\n> Using the record => {0}", obj );
Console.WriteLine( "\n> Using the property => {0}", obj.SumOfEmployees );
cmd.Dispose();
}
public static void Query_MultipleTables_AndSkipTake( IKLink link )
{
Console.WriteLine( "\n===== Query multiples tables..." );
var cmd = link
.From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.JoinDate >= new CalendarDate( 2000, 1, 1 ) )
.From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.Id == x.Emp.CountryId )
.Select( x => x.Ctry.All() )
.Select( x => x.Emp.Id, x => x.Emp.BirthDate, x => x.Emp.LastName );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
Console.WriteLine( "\n===== Query multiples tables with skip/take..." );
foreach( var obj in cmd.SkipTake( 2, 2 ) )
Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}
public static void Query_Basic_ConversionToType( IKLink link )
{
Console.WriteLine( "\n===== Basic Query with conversion to a given type..." );
var cmd = link.From( x => x.Employees.As( x.Emp ) );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( EmployeeTable obj in cmd.ConvertBy( rec => {
// Using several ways of accessing the columns...
EmployeeTable emp = new EmployeeTable(); dynamic d = rec;
emp.Id = d.Id;
emp.FirstName = (string)rec["FirstName"];
emp.LastName = (string)rec["Emp", "LastName"];
// Using the hard way, the indexed one, we need to take care of the type conversions...
// object orig = rec["Employees", "BirthDate"];
// CalendarDate date = orig == null ? null : new CalendarDate( (DateTime)orig );
// emp.BirthDate = date;
emp.BirthDate = d.BirthDate; // If using dynamics no conversion specification is needed
emp.JoinDate = d.JoinDate;
emp.StartTime = d.StartTime;
emp.Active = d.Active;
emp.ManagerId = d.Emp.ManagerId; // Using the alias
emp.CountryId = d.Employees.CountryId; // Using the table name
emp.Photo = d.Photo;
return emp;
} ) ) Console.WriteLine( "\n> Employee => {0}", obj );
cmd.Dispose();
}
public static void Query_Basic_ConversionToAnonymous( IKLink link )
{
Console.WriteLine( "\n===== Basic Query with conversion to anonymous..." );
var cmd = link.From( x => x.Employees.As( x.Emp ) ).Where( x => x.LastName >= "C" );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd.ConvertBy( rec => {
// Using several ways of accessing the columns...
dynamic d = rec;
return new {
d.Id,
Name = string.Format( "{0}, {1}", rec["LastName"], rec["Emp", "FirstName"] )
};
} ) ) Console.WriteLine( "\n> Converted => {0}", obj );
cmd.Dispose();
}
public static void Query_Basic_WithIndexedColumns( IKLink link )
{
Console.WriteLine( "\n===== Basic Query with Indexed columns..." );
var cmd = link.From( x => x.Employees.As( x.Emp ) ).Where( x => x.LastName >= "C" );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( KRecord obj in cmd )
Console.WriteLine( "\n> Generated => {0}: {1} {2}",
obj["Id"],
obj["Emp", "FirstName"],
obj["Employees", "LastName"] );
cmd.Dispose();
}
public static void Query_Basic_WithDynamicColumns( IKLink link )
{
Console.WriteLine( "\n===== Basic Query with Dynamic Columns..." );
var cmd = link.From( x => x.Employees.As( x.Emp ) ).Where( x => x.LastName >= "C" );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( dynamic obj in cmd )
Console.WriteLine( "\n> Generated => {0}: {1} {2}",
obj.Employees.Id, // Using the table name
obj.Emp.FirstName, // Using the table alias
obj.LastName ); // We are using the Employees table implicitly as there is only one LastName column
cmd.Dispose();
}
public static void Query_Basic( IKLink link )
{
Console.WriteLine( "\n===== Basic Query..." );
var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "C" );
Console.WriteLine( "\n> Command => {0}", cmd );
foreach( var obj in cmd ) Console.WriteLine( "\n> Record => {0}", obj );
cmd.Dispose();
}