Select Data from Multiple Tables
- 6 minutes to read
Select Data from Joined Tables (Master-Detail)
XPO selects data from joined tables out-of-the-box based on the structure of a data model.
using DevExpress.Xpo;
using DevExpress.Data.Filtering;
public class ProductLine : XPLiteObject {
// ...
public ProductLine(Session session) : base(session) { }
// Gets a collection of products that correspond to the current product line.
// XPO loads this collection on demand.
[Association("ProductLine-Products")]
public XPCollection<Product> Products { get { return GetCollection<Product>(nameof(Products)); } }
string fName;
public string Name {
get { return fName; }
set { SetPropertyValue(nameof(Name), ref fName, value); }
}
}
public class Product: XPObject {
// ...
ProductLine fLine;
[Association("ProductLine-Products")]
public ProductLine Line {
get { return fLine; }
set { SetPropertyValue(nameof(ProductLine), ref fLine, value); }
}
}
XPO loads collection properties (of the XPCollection or XPCollection<T> type) on demand. XPO populates collections when their persistent objects are accessed for the first time.
// Searches for the 'My Brand' product line.
// XPO does not populate the 'line.Products' collection.
ProductLine line = (ProductLine)session.FindObject(typeof(ProductLine),
CriteriaOperator.Parse("Name='My Brand'"));
// Reads the total number of objects in the collection.
// XPO populates the 'Products' collection.
int count = line.Products.Count;
When a property that references a persistent object is loaded, XPO performs multiple queries. The first query retrieves a reference to the persistent object itself. Other queries retrieve references for each property of the persistent object. This might be a recursive process because properties can reference other persistent objects.
In SQL, for example, you would have to execute the following query to get data from related tables:
exec sp_executesql N'select N0."Oid",N1."Address",N2."Street",N2."City",N2."StateProvince",N2."ZipPostal",N2."Country",N2."OptimisticLockField",N2."GCRecord",N1."OptimisticLockField",N1."GCRecord",N1."ObjectType",N0."FirstName",N0."LastName",N0."MiddleName",N0."Birthday",N0."Email" from ((("dbo"."Person" N0
inner join "dbo"."Party" N1 on (N0."Oid" = N1."Oid"))
left join "dbo"."Address" N2 on (N1."Address" = N2."Oid"))
where N0."Oid" in (@p0,@p1)',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='85ECDEFA-1BE3-4AEF-A3CE-598F3C072599',@p1='F5EA5632-1BB0-408B-9E66-8441B4C216A8'
The following code describes the data model above in terms of XPO. BaseObject is a base persistent class with an auto-generated GUID key.
using DevExpress.Persistent.Base;
using DevExpress.Persistent.BaseImpl;
using DevExpress.Xpo;
[MapInheritance(MapInheritanceType.OwnTable)]
public abstract class Party : XPBaseObject {
protected Party(Session session) : base(session) { }
private Address fAddress;
[Aggregated]
public Address Address {
get { return fAaddress; }
set { SetPropertyValue(nameof(Address), ref fAddress, value); }
}
/* ... */
}
public class Person : Party { /* ... */ }
public class Address: XPBaseObject { /* ... */ }
See the following topics for more information on how XPO loads reference and collection properties:
- Relationships Between Objects
- Inheritance Mapping
- Delayed Loading
- Reload Objects and Collections
- ExplicitLoadingAttribute
Select Data from Unrelated Tables
Use the XPView component or LINQ to XPO to select data from multiple tables. The main advantage of XPView over LINQ to XPO is that it is easier to create complex dynamic queries.
These examples demonstrate how to use XPView, a LINQ query projected to an anonymous type, and a LINQ query projected to POCO to fetch data with a query similar to the following SQL query.
SELECT
(customer.FirstName + ' ' + customer.LastName) AS "Contact Name",
address.City,
(SELECT SUM(order.Amount) AS "Total Amount" FROM Orders order WHERE customer.ID = order.CustomerID) as "Total Amount"
FROM Customers customer
LEFT JOIN Addresses address ON customer.AddressID = address.ID
XPView
using DevExpress.Xpo;
// ...
XPView view = new XPView(unitOfWork, classInfo);
view.AddProperty("Contact Name", "Concat([FirstName], ' ', [LastName])");
view.AddProperty("City", "[Address.City]");
view.AddProperty("Total Amount", "[Orders].Sum([Amount])");
LINQ & Projection
using System.Linq;
// ...
var q1 = unitOfWork.Query<Customer>()
.Select(c => new {
ContactName = string.Concat(c.FirstName, " ", c.LastName),
c.Address.City,
TotalAmount = c.Orders.Sum(o => o.Amount)
});
LINQ & POCO
using System.Linq;
// ...
var q2 = unitOfWork.Query<Customer>()
.Select(c => new CustomerDetails() {
ContactName = string.Concat(c.FirstName, " ", c.LastName),
City = c.Address.City,
TotalAmount = c.Orders.Sum(o => o.Amount)
});
How to Join Data Based on a Custom Condition
Use Free Joins to join persistent objects based on a custom condition. The following example demonstrates how to get the list of boxers with the most wins in their weight class.
XPView view = new XPView(session, typeof(Boxer));
view.AddProperty("Name");
view.AddProperty("WinNumber");
view.CriteriaString = "[<Boxer>][[^.Weight] >= floor(Weight / 10) * 10 and [^.Weight] <= floor(Weight / 10 + 1) * 10].max(WinNumber) = WinNumber";
LINQ to XPO uses Free Joins to implement the Queryable.GroupJoin and Queryable.Join methods. The following example demonstrates how to use LINQ to XPO to fetch the list of boxers.