Querying an Entity Data Model - Entity Framework 6 Recipes - page 104

public DbSet<Order> Orders { get; set; }
public DbSet<Account> Accounts { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Account>().ToTable("Chapter3.Account");
modelBuilder.Entity<Order>().ToTable("Chapter3.Order");
base.OnModelCreating(modelBuilder);
}
}
To find the orders, follow the pattern in Listing 3-37.
Listing 3-37. Using a Join on Multiple Properties to Find All of the Orders Being Shipped to the Account's City
and State
using (var context = new EFRecipesEntities())
{
var a1 = new Account { City = "Raytown", State = "MO" };
a1.CustomerOrders.Add(new CustomerOrder { Amount = 223.09M, ShipCity = "Raytown",
ShipState = "MO" });
a1. CustomerOrders.Add(new CustomerOrder { Amount = 189.32M, ShipCity = "Olathe",
ShipState = "KS" });
var a2 = new Account { City = "Kansas City", State = "MO" };
a2. CustomerOrders.Add(new CustomerOrder { Amount = 99.29M, ShipCity = "Kansas City",
ShipState = "MO" });
var a3 = new Account { City = "North Kansas City", State = "MO"};
a3. CustomerOrders.Add(new CustomerOrder { Amount = 102.29M, ShipCity = "Overland Park",
ShipState = "KS" });
context.Accounts.Add(a1);
context.Accounts.Add(a2);
context.Accounts.Add(a3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var orders = from o in context.CustomerOrders
join a in context.Accounts on
new {Id = o.AccountID, City = o.ShipCity, State = o.ShipState }
equals
new {Id = a.AccountID, City = a.City, State = a.State }
select o;
Console.WriteLine("Orders shipped to the account's city, state...");
foreach (var order in orders)
{
Console.WriteLine("\tOrder {0} for {1}", order.AccountID.ToString(),
order.Amount.ToString());
}
}