private AliasedCustomer[] AddAliasedCustomers(out Country[] countries)
{
db.DropAndCreateTable<AliasedCustomer>();
db.DropAndCreateTable<AliasedCustomerAddress>();
db.DropAndCreateTable<Country>();
var customers = new[]
{
new AliasedCustomer
{
Name = "Customer 1",
PrimaryAddress = new AliasedCustomerAddress
{
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
},
new AliasedCustomer
{
Name = "Customer 2",
PrimaryAddress = new AliasedCustomerAddress
{
AddressLine1 = "2 America Street",
Country = "USA"
},
},
new AliasedCustomer
{
Name = "Customer 3",
PrimaryAddress = new AliasedCustomerAddress
{
AddressLine1 = "3 Canada Street",
Country = "Canada"
},
},
};
customers.Each(c =>
db.Save(c, references: true));
countries = new[]
{
new Country {CountryName = "Australia", CountryCode = "AU"},
new Country {CountryName = "USA", CountryCode = "US"},
new Country {CountryName = "Italy", CountryCode = "IT"},
new Country {CountryName = "Spain", CountryCode = "ED"}
};
db.Save(countries);
return customers;
}
private void AddCustomers()
{
var customers = new[]
{
new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
},
new Customer
{
Name = "Customer 2",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "2 America Street",
Country = "USA"
},
},
new Customer
{
Name = "Customer 3",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "3 Canada Street",
Country = "Canada"
},
},
};
customers.Each(c =>
db.Save(c, references: true));
}
public void Can_do_LeftJoins_using_SqlExpression()
{
var customers = new[]
{
new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress {
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
},
new Customer
{
Name = "Customer 2",
PrimaryAddress = new CustomerAddress {
AddressLine1 = "2 America Street",
Country = "USA"
},
},
new Customer
{
Name = "Customer 3",
PrimaryAddress = new CustomerAddress {
AddressLine1 = "3 Canada Street",
Country = "Canada"
},
},
};
customers.Each(c =>
db.Save(c, references: true));
db.Insert(
new Country { CountryName = "Australia", CountryCode = "AU" },
new Country { CountryName = "USA", CountryCode = "US" },
new Country { CountryName = "Italy", CountryCode = "IT" },
new Country { CountryName = "Spain", CountryCode = "ED" });
//Normal Join
var dbCustomers = db.Select<Customer>(q => q
.Join<CustomerAddress>()
.Join<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));
Assert.That(dbCustomers.Count, Is.EqualTo(2));
//Left Join
dbCustomers = db.Select<Customer>(q => q
.Join<CustomerAddress>()
.LeftJoin<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName));
Assert.That(dbCustomers.Count, Is.EqualTo(3));
//Warning: Right and Full Joins are not implemented by Sqlite3. Avoid if possible.
var dbCountries = db.Select<Country>(q => q
.LeftJoin<CustomerAddress>((c, ca) => ca.Country == c.CountryName)
.LeftJoin<CustomerAddress, Customer>());
Assert.That(dbCountries.Count, Is.EqualTo(4));
var dbAddresses = db.Select<CustomerAddress>(q => q
.LeftJoin<Country>((ca, c) => ca.Country == c.CountryName)
.LeftJoin<CustomerAddress, Customer>());
Assert.That(dbAddresses.Count, Is.EqualTo(3));
}
private Customer[] AddCustomersWithOrders()
{
var customers = new[]
{
new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
Orders = new[]
{
new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m},
new Order {LineItem = "Line 1", Qty = 2, Cost = 3.98m},
new Order {LineItem = "Line 2", Qty = 1, Cost = 1.49m},
new Order {LineItem = "Line 2", Qty = 2, Cost = 2.98m},
new Order {LineItem = "Australia Flag", Qty = 1, Cost = 9.99m},
}.ToList(),
},
new Customer
{
Name = "Customer 2",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "2 Prospect Park",
Country = "USA"
},
Orders = new[]
{
new Order {LineItem = "USA", Qty = 1, Cost = 20m},
}.ToList(),
},
};
customers.Each(c =>
db.Save(c, references: true));
return customers;
}
public void Can_do_joins_with_complex_wheres_using_SqlExpression()
{
var customers = new[]
{
new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress {
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
Orders = new[] {
new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
new Order { LineItem = "Line 1", Qty = 2, Cost = 3.98m },
new Order { LineItem = "Line 2", Qty = 1, Cost = 1.49m },
new Order { LineItem = "Line 2", Qty = 2, Cost = 2.98m },
new Order { LineItem = "Australia Flag", Qty = 1, Cost = 9.99m },
}.ToList(),
},
new Customer
{
Name = "Customer 2",
PrimaryAddress = new CustomerAddress {
AddressLine1 = "2 Prospect Park",
Country = "USA"
},
Orders = new[] {
new Order { LineItem = "USA", Qty = 1, Cost = 20m },
}.ToList(),
},
};
customers.Each(c =>
db.Save(c, references: true));
db.Insert(
new Country { CountryName = "Australia", CountryCode = "AU" },
new Country { CountryName = "USA", CountryCode = "US" });
var results = db.Select<FullCustomerInfo, Customer>(q => q
.Join<CustomerAddress>() //implicit
.Join<Customer, Order>() //explicit
.Where(c => c.Name == "Customer 1")
.And<Order>(o => o.Cost < 2)
.Or<Order>(o => o.LineItem == "Australia Flag"));
var costs = results.ConvertAll(x => x.Cost);
Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m }));
var orderIds = results.ConvertAll(x => x.OrderId);
var expectedOrderIds = new []{customers[0].Orders[0].Id, customers[0].Orders[2].Id, customers[0].Orders[4].Id};
Assert.That(orderIds, Is.EquivalentTo(expectedOrderIds));
//Same as above using using db.From<Customer>()
results = db.Select<FullCustomerInfo>(db.From<Customer>()
.Join<CustomerAddress>() //implicit
.Join<Customer, Order>() //explicit
.Where(c => c.Name == "Customer 1")
.And<Order>(o => o.Cost < 2)
.Or<Order>(o => o.LineItem == "Australia Flag"));
costs = results.ConvertAll(x => x.Cost);
Assert.That(costs, Is.EquivalentTo(new[] { 1.99m, 1.49m, 9.99m }));
results = db.Select<FullCustomerInfo, Customer>(q => q
.Join<Customer, CustomerAddress>()
.Join<Customer, Order>()
.Where(c => c.Name == "Customer 2")
.And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem));
costs = results.ConvertAll(x => x.Cost);
Assert.That(costs, Is.EquivalentTo(new[] { 20m }));
var countryResults = db.Select<FullCustomerInfo>(db.From<Customer>()
.Join<CustomerAddress>() //implicit join with Customer
.Join<Order>((c, o) => c.Id == o.CustomerId) //explicit join condition
.Join<CustomerAddress, Country>((ca, c) => ca.Country == c.CountryName)
.Where(c => c.Name == "Customer 2") //implicit condition with Customer
.And<CustomerAddress, Order>((a, o) => a.Country == o.LineItem));
costs = countryResults.ConvertAll(x => x.Cost);
Assert.That(costs, Is.EquivalentTo(new[] { 20m }));
Assert.That(countryResults.ConvertAll(x => x.CountryCode), Is.EquivalentTo(new[] { "US" }));
}
