private static void ListContactsAddressesEntitySQLAggregates(PEF ctx)
{
const string eSql = "SELECT groupC.Title, " +
" (SELECT VALUE c FROM PEF.Contacts as c " +
" WHERE c.Title = groupC.Title) as Contact " +
"FROM PEF.Contacts as groupC " +
"WHERE TRIM(groupC.Title) <> '' " +
"GROUP BY groupC.Title";
ObjectQuery<DbDataRecord> result =
((IObjectContextAdapter)ctx).ObjectContext.CreateQuery<DbDataRecord>(eSql);
foreach (DbDataRecord dbRecord in result)
{
var contactTitle = dbRecord["Title"] as String;
var rows = dbRecord["Contact"] as IEnumerable<Contact>;
Console.WriteLine("{0}", contactTitle);
foreach (var ct in rows)
{
Console.WriteLine("\t{0}", ct.FirstName + " " + ct.LastName);
}
/*Console.WriteLine("{0} - {1} {2} {3}",
contactTitle,
contact.Title.Trim(),
contact.FirstName.Trim(),
contact.LastName.Trim()); */
}
}
private static void ListContactsAddresses(PEF ctx)
{
var contacts = ctx.Contacts
.Where(c => c.Addresses.Any(a => a.CountryRegion == "UK"))
.Select(c => new
{
c,
c.Addresses,
AddressesCount = c.Addresses.Count,
MaxPostCode = c.Addresses.Max(a => a.PostalCode)
});
foreach (var ct in contacts)
{
Console.WriteLine("Contact: {0} {1}", ct.c.LastName.Trim(),
ct.c.FirstName.Trim());
foreach (var addr in ct.Addresses)
{
Console.WriteLine("\tStreet: {0}", addr.Street1);
Console.WriteLine("\tCity: {0}", addr.City);
Console.WriteLine("\tCountry: {0}", addr.CountryRegion);
Console.WriteLine("\tPostCode: {0}", addr.PostalCode);
}
Console.WriteLine("Max PostCode: {0}", ct.MaxPostCode);
}
}
private static void ListContactsAddressesEntitySQL(PEF ctx)
{
const string eSql = "SELECT c, c.Addresses " +
"FROM PEF.Contacts as c " +
"WHERE c.FirstName = 'Robert'";
ObjectQuery<DbDataRecord> result =
((IObjectContextAdapter)ctx).ObjectContext.CreateQuery<DbDataRecord>(eSql);
foreach (DbDataRecord dbRecord in result)
{
var contact = dbRecord[0] as Contact;
Console.WriteLine("{0} {1} {2}",
contact.Title.Trim(),
contact.FirstName.Trim(),
contact.LastName.Trim());
foreach (var address in contact.Addresses)
{
Console.WriteLine("\t{0} {1} {2}", address.Street1.Trim(),
address.City.Trim(),
address.StateProvince.Trim());
}
}
}
private static void QueryContacts()
{
using (var context = new PEF())
{
context.Database.Log = Console.WriteLine;
String query = "SELECT c, c.Addresses " +
"FROM PEF.Contacts AS c " +
"WHERE c.FirstName='Robert'";
var adapter = (IObjectContextAdapter)context;
var objectContext = adapter.ObjectContext;
ObjectQuery<DbDataRecord> contacts = objectContext.CreateQuery<DbDataRecord>(query);
//DbQuery
foreach (DbDataRecord c in contacts)
{
var contact = c[0] as Contact;
Console.WriteLine("{0} {1} {2}",
contact.Title.Trim(),
contact.FirstName.Trim(),
contact.LastName);
//foreach (var a in contact.Addresses)
//{
// Console.WriteLine(" {0}, {1}",
// a.Street1.Trim(), a.City);
//}
}
}
}
static void Main(string[] args)
{
using (PEF ctx = new PEF())
{
/*IQueryable<String> contacts = ctx.Contacts
.Where(c => c.FirstName == "Robert")
.Select(c => c.FirstName.Trim() + " " + c.LastName);*/
/*
String query = "SELECT VALUE C " +
"from PEF.Contacts as C " +
"where c.FirstName = 'Robert'";
ObjectQuery<Contact> contacts =
((IObjectContextAdapter) ctx).ObjectContext.CreateQuery<Contact>(query);
*/
/*String entitySql = "SELECT VALUE c " +
"FROM PEF.Contacts as C " +
"WHERE c.FirstName = @firstname";
ObjectQuery<Contact> contacts2 =
new ObjectQuery<Contact>(entitySql, ((IObjectContextAdapter) ctx).ObjectContext);
contacts2.Parameters.Add(new ObjectParameter("firstname", "Robert"));
var ctx2 = ((IObjectContextAdapter) ctx).ObjectContext;
// Entity SQL
ObjectSet<Contact> resultSet = ctx2.CreateObjectSet<Contact>("Contacts");
var list = resultSet.Where("it.FirstName = 'Robert'");
var list2 = resultSet.Where(c => c.FirstName == "Robert");
*/
/*foreach (var ct in list2)
{
Console.WriteLine("{0} {1}", ct.FirstName, ct.LastName);
}*/
//ExecEntityClientQuery();
ctx.Configuration.LazyLoadingEnabled = false;
var stopWatch = new Stopwatch();
stopWatch.Start();
//ListContactsAddresses(ctx);
//ListContactsAddressesJoin(ctx);
//ListContactsAddressesGroupBy(ctx);
//ListContactsAddressesLazyLoading(ctx);
//ListContactsAddressesEntitySQL(ctx);
ListContactsAddressesEntitySQLAggregates(ctx);
stopWatch.Stop();
Console.WriteLine("Elapsed (ms): " + stopWatch.ElapsedMilliseconds);
Console.ReadLine();
}
}
private static void ListContactsAddressesLazyLoading(PEF ctx)
{
var contact = ((IObjectContextAdapter) ctx).ObjectContext.GetObjectByKey(
new System.Data.Entity.Core.EntityKey("PEF.Contacts",
"ContactID",5));
Console.WriteLine(((Contact)contact).LastName);
/*var result = ctx.Addresses;
foreach (var addr in result)
{
ctx.Entry(addr).Reference(a => a.Contact).Load();
Console.WriteLine(addr.Contact.FirstName);
}*/
}
private static void ListContactsAddressesJoin(PEF ctx)
{
var result = ctx.Contacts
.Join(ctx.Addresses,
c => c.ContactID,
a => a.ContactID,
(c, a) => new
{
Contact = c,
Addresses = c.Addresses
});
foreach (var ct in result)
{
Console.WriteLine("Contact: {0} {1}", ct.Contact.LastName.Trim(),
ct.Contact.FirstName.Trim());
foreach (var addr in ct.Addresses)
{
Console.WriteLine("\tStreet: {0}", addr.Street1);
Console.WriteLine("\tCity: {0}", addr.City);
Console.WriteLine("\tCountry: {0}", addr.CountryRegion);
Console.WriteLine("\tPostCode: {0}", addr.PostalCode);
}
}
}
private static void ListContactsAddressesGroupBy(PEF ctx)
{
var result = ctx.Addresses
.Where(a => a.CountryRegion == "Canada")
.Where(a => a.Contact.LastName == "Harding")
.Select(a => new {a, a.Contact});
foreach (var r in result)
{
Console.WriteLine("LastName: {0}", r.Contact.LastName);
Console.WriteLine(" Count: {0}", r.Contact.Addresses.Count());
foreach (var record in r.Contact.Addresses)
{
Console.WriteLine("\tAddress: {0}", record.City);
}
}
}
