Joins

Automatic Joins

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

Dim order As Order = New Order With {.ShippingCost = 200.11D, .Tax = 100.10D, .Buyer = New Person With {.FirstName = "John", .LastName = "Smith", .DateCreated = Date.Now}, .OrderDetails = New List(Of OrderDetail)()}

Dim detail1 As OrderDetail = New OrderDetail With {.Item = New Product With {.ProductName = "Wizard", .Cost = 29.98D}, .Quantity = 2, .Order = order}

order.OrderDetails.Add(detail1)

Dim detail2 As OrderDetail = New OrderDetail With {.Item = New Product With {.ProductName = ".NET Caching Library", .Cost = 199.95D}, .Quantity = 1, .Order = order}

order.OrderDetails.Add(detail2)

_dataHelper.SaveWithChildren(order)

Dim query = _dataHelper.CreateQuery(Of Order)().Where(Function(o) o.OrderDetails.Any(Function(d) d.Item.ProductId = detail2.Item.ProductId)).Select(Function(o) o.OrderId)

Dim result = query.ToList()

For Each item In result

Console.WriteLine(item)

Next item

Creating Manual Joins

If desired, you can also do manual joins.

Dim detail1 As OrderDetail = New OrderDetail With {.Item = New Product With {.ProductName = "Wizard", .Cost = 29.98D}, .Quantity = 2, .Order = order}

order.OrderDetails.Add(detail1)

Dim detail2 As OrderDetail = New OrderDetail With {.Item = New Product With {.ProductName = ".NET Caching Library", .Cost = 199.95D}, .Quantity = 1, .Order = order}

order.OrderDetails.Add(detail2)

_dataHelper.SaveWithChildren(order)

Dim orders = _dataHelper.CreateQuery(Of Order)()

Dim details = _dataHelper.CreateQuery(Of OrderDetail)()

Dim products = _dataHelper.CreateQuery(Of Product)()

Dim query = From o In orders , d In details , p In products _

Where o.OrderId = d.Order.OrderId AndAlso d.Item.ProductId = p.ProductId AndAlso p.ProductId = detail2.Item.ProductId _

Select o.OrderId

Dim result = query.ToList()

For Each item In result

Console.WriteLine(item)

Next item

Traditional Joins with SQL

Public Function GetOrderHistory(ByVal email As String) As List(Of OrderHistory)

Dim sql As String = "SELECT" & ControlChars.CrLf & " o.OrderNumber," & ControlChars.CrLf & " o.OrderDate," & ControlChars.CrLf & " d.ProductName," & ControlChars.CrLf & " d.VariantName," & ControlChars.CrLf & " d.SKU," & ControlChars.CrLf & " d.UserName," & ControlChars.CrLf & " d.License" & ControlChars.CrLf & " FROM Orders o" & ControlChars.CrLf & " INNER JOIN OrderDetails d on d.OrderId = o.OrderId" & ControlChars.CrLf & " WHERE o.Email = @Email" & ControlChars.CrLf & " AND (FinancialStatus = 'paid' or FinancialStatus = 'CAPTURED')" & ControlChars.CrLf & " ORDER BY o.OrderDate DESC, d.SKU"

Dim parms As New Dictionary(Of String, Object)()

parms.Add("Email", email)

Using TempOpenCloseWrapper As OpenCloseWrapper = New OpenCloseWrapper(_db)

Return _db.Load(Of OrderHistory)(sql, parms)

End Using

End Function