public List <FullContact> GetFullContacts(ContactParameters contactParameters)
{
// INNER JOIN lambda
/*return this.RepositoryContext.Contacts.Join(
* this.RepositoryContext.Addresses,
* contact => contact.Id,
* address => address.ContactId,
* (contact, address) => new FullContact
* {
* ZipCode = address.ZipCode,
* City = address.City,
* FirstName = contact.FirstName,
* LastName = contact.LastName
* }
* ).ToList();*/
// LEFT JOIN lambda
/*return this.RepositoryContext.Contacts
* .GroupJoin(
* this.RepositoryContext.Addresses,
* contact => contact.Id,
* address => address.ContactId,
* (contact, address) => new { contact, address }
* )
* .SelectMany(
* x => x.address.DefaultIfEmpty(),
* (x, address) => new FullContact {
* FirstName = x.contact.FirstName,
* LastName = x.contact.LastName,
* City = address == null ? string.Empty : address.City,
* ZipCode = address == null ? 0 : address.ZipCode
* }
* ).ToList();*/
// LEFT JOIN Query syntax
/*var query = from contact in this.RepositoryContext.Contacts
* join address in this.RepositoryContext.Addresses
* on contact.Id equals address.ContactId
* into ContactAddressGroup
* from address in ContactAddressGroup.DefaultIfEmpty()
* select new FullContact
* {
* FirstName = contact.FirstName,
* LastName = contact.LastName,
* Gender = contact.Gender,
* ZipCode = address.ZipCode,
* City = address.City,
* };*/
List <FullContact> fullContacts = new List <FullContact> {
};
using (var context = this.RepositoryContext) {
var query = from contact in context.Contacts
join child in context.Children on contact.Id equals child.ContactId
into ChildGroup
from c in ChildGroup.DefaultIfEmpty()
join address in context.Addresses on contact.Id equals address.ContactId
into AddressGroup
from a in AddressGroup.DefaultIfEmpty()
select new {
Contact = contact,
Address = a,
Child = c
};
// Filters
if (!string.IsNullOrEmpty(contactParameters.Gender))
{
query = query.Where(q => q.Contact.Gender == contactParameters.Gender);
}
// Group by contact
var grouping = query.ToLookup(q => q.Contact.Id);
Console.WriteLine("Items Count: {0}", grouping.Count);
foreach (var item in grouping)
{
var fullContact = new FullContact()
{
FirstName = item.First().Contact.FirstName,
LastName = item.First().Contact.LastName,
Gender = item.First().Contact.Gender,
ZipCode = item.First().Address == null ? 0 : item.First().Address.ZipCode,
City = item.First().Address?.City,
Children = new List <Child> {
}
};
foreach (var i in item)
{
if (i.Child != null)
{
fullContact.Children.Add(
new Child()
{
Name = i.Child.Name
}
);
}
}
fullContacts.Add(fullContact);
}
}
return(fullContacts);
}
public void SetDataContextTest()
{
var db = new AccessNorthwind("C:/Northwind.mdb");
var order = db.Orders.First();
Assert.IsNotNull(order.DataContext);
var customer = db.Customers.SingleOrDefault(o => o.CustomerID == "MCSFT");
if (customer != null)
{
Assert.AreEqual(db, customer.DataContext);
db.Customers.DeleteOnSubmit(customer);
db.SubmitChanges();
//Assert.AreEqual(null, customer.DataContext);
}
var newCustomer = new Customer
{
CustomerID = "MCSFT",
CompanyName = "Microsoft",
ContactName = "John Doe",
ContactTitle = "Sales Manager",
Address = "1 Microsoft Way",
City = "Redmond",
Region = "WA",
PostalCode = "98052",
Country = "USA",
Phone = "(425) 555-1234",
Fax = string.Empty,
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();
Assert.AreEqual(db, newCustomer.DataContext);
db.Contacts.Delete(o => true);
db.Log = Console.Out;
if (db.Contacts.Count() == 0)
{
var contact = new Contact();
db.Contacts.InsertOnSubmit(contact);
db.SubmitChanges();
Assert.AreEqual(db, contact.DataContext);
}
if (db.Contacts.OfType <FullContact>().Count() == 0)
{
var contact = new FullContact();
db.Contacts.InsertOnSubmit(contact);
db.SubmitChanges();
Assert.AreEqual(db, contact.DataContext);
Assert.IsTrue(db.Contacts.OfType <FullContact>().Count() > 0);
}
db = new AccessNorthwind("C:/Northwind.mdb");
var c = db.Contacts.First();
Assert.AreEqual(db, c.DataContext);
var f = db.Contacts.OfType <FullContact>().First();
Assert.AreEqual(db, f.DataContext);
}
