static void Main()
{
try
{
///<remarks>
/// If you're using Visual Studio 2010 SP1 the sample database
/// included with this solution should also be available at:
/// %PROGRAMFILES%\Microsoft SQL Server Compact Edition\v4.0\Samples
///</remarks>
var res = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
var con = new SqlCeConnectionStringBuilder();
con.DataSource = Path.Combine(res, "Northwind.sdf");
var msg = "Retrieving the amount of customers and orders per city in the USA:";
using (var data = new NorthwindDataContext(con.ConnectionString))
{
// TODO: Create a complex LINQ expression with data from all tables.
var query = from customer in data.Customers
join order in data.Orders on customer.CustomerID equals order.CustomerID
where customer.Country == "USA"
group customer by customer.City into customerPerCity
select new
{
City = customerPerCity.Key,
CustomerCount = customerPerCity.Count(),
OrderCount = customerPerCity.Sum(c => c.Orders.Count)
};
if (query != null)
{
var txt = new StringBuilder();
txt.AppendLine(msg);
txt.Append(Environment.NewLine);
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "City", "Customers", "Orders"));
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
foreach (var item in query)
{
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", item.City, item.CustomerCount, item.OrderCount));
}
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
Console.Write(txt.ToString());
}
}
}
catch (Exception err)
{
Console.Write(Environment.NewLine);
Console.WriteLine(String.Format("Exception: {0}", err.Message));
}
finally
{
Console.Write(Environment.NewLine);
Console.Write("Press any key to continue . . .");
Console.ReadKey(true);
}
}
public static void Main()
{
try
{
var directory = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
var connection = new SqlCeConnectionStringBuilder();
connection.DataSource = Path.Combine(directory, "Northwind.sdf");
using (var data = new NorthwindDataContext(connection.ConnectionString))
{
var query = from customer in data.Customers
join order in data.Orders
on customer.CustomerID equals order.CustomerID
where customer.Country == "USA"
group customer by customer.City into customerPerCity
orderby customerPerCity.Count() descending
select new
{
City = customerPerCity.Key,
CustomerCount = customerPerCity.Count(),
OrdersCount = customerPerCity.Sum(c => c.Orders.Count)
};
if (query != null)
{
var builder = new StringBuilder();
builder.AppendConsoleFormat();
foreach (var item in query)
{
builder.AppendLine(string.Format("{0,-54} {1,11} {2,11}", item.City, item.CustomerCount, item.OrdersCount));
}
Console.Write(builder.ToString());
}
}
}
catch (Exception error)
{
Console.Write(Environment.NewLine);
Console.WriteLine(string.Format("Exception: {0}", error.Message));
}
finally
{
Console.Write(Environment.NewLine);
Console.Write("Press any key to continue . . .");
Console.ReadKey(true);
}
}
