Automatic Joins

After relationships are setup, joins occur automatically in LINQ.  See Mapping Relationships. 

 

Order order = new Order
{
ShippingCost = 200.11M,
Tax = 100.10M,
Buyer = new Person
{
FirstName = "John",
LastName = "Smith",
DateCreated = DateTime.Now
},
OrderDetails = new List<OrderDetail>()
};

OrderDetail detail1 = new OrderDetail
{
    Item = new Product
    {
        ProductName = "Wizard",
        Cost = 29.98M
    },
    Quantity = 2,
    Order = order
};
order.OrderDetails.Add(detail1);

OrderDetail detail2 =
    new OrderDetail
    {
        Item = new Product
        {
            ProductName = ".NET Caching Library",
            Cost = 199.95M
        },
        Quantity = 1,
        Order = order
    };
order.OrderDetails.Add(detail2);

_dataHelper.SaveWithChildren(order);

var query = _dataHelper.CreateQuery<Order>()
    .Where(o => o.OrderDetails.Any(d => d.Item.ProductId == detail2.Item.ProductId))
    .Select(o => o.OrderId);

var result = query.ToList();

foreach (var item in result)
{
    Console.WriteLine(item);
}

 

Creating Manual Joins

If desired, you can also do manual joins. 

 

Order order = new Order
{
ShippingCost = 200.11M,
Tax = 100.10M,
Buyer = new Person
{
FirstName = "John",
LastName = "Smith",
DateCreated = DateTime.Now
},
OrderDetails = new List<OrderDetail>()
};

OrderDetail detail1 = new OrderDetail
{
    Item = new Product
    {
        ProductName = "Wizard",
        Cost = 29.98M
    },
    Quantity = 2,
    Order = order
};
order.OrderDetails.Add(detail1);

OrderDetail detail2 =
    new OrderDetail
    {
        Item = new Product
        {
            ProductName = ".NET Caching Library",
            Cost = 199.95M
        },
        Quantity = 1,
        Order = order
    };
order.OrderDetails.Add(detail2);

_dataHelper.SaveWithChildren(order);

var orders = _dataHelper.CreateQuery<Order>();
var details = _dataHelper.CreateQuery<OrderDetail>();
var products = _dataHelper.CreateQuery<Product>();

var query = from o in orders
            from d in details
            from p in products
            where o.OrderId == d.Order.OrderId
                  && d.Item.ProductId == p.ProductId
                  && p.ProductId == detail2.Item.ProductId
            select o.OrderId;

var result = query.ToList();

foreach (var item in result)
{
    Console.WriteLine(item);
}

 

Traditional Joins with SQL

 

public List<OrderHistory> GetOrderHistory(string email)
{
 string sql = @"SELECT
 o.OrderNumber,
 o.OrderDate,
 d.ProductName,
 d.VariantName,
 d.SKU,
 d.UserName,
 d.License
 FROM Orders o
 INNER JOIN OrderDetails d on d.OrderId = o.OrderId
 WHERE o.Email = @Email
 AND (FinancialStatus = 'paid' or FinancialStatus = 'CAPTURED')
 ORDER BY o.OrderDate DESC, d.SKU";

 Dictionary<string, object> parms = new Dictionary<string, object>();
 parms.Add("Email", email);

 using (new OpenCloseWrapper(_db))
 {
  return _db.Load<OrderHistory>(sql, parms);
 }

}