Skip to main content
BuyLog In
XPO (.NET ORM Library)
Search in…
Docs > Query and Shape Data > Select Data from Multiple Tables
All docs
V23.2
.NET 6.0+
.NET ORM Library (XPO)
.NET Support
Getting Started
Product Information
Connect to a Data Store
Create a Data Model
Create-Read-Update-Delete (CRUD)
Query and Shape Data
LINQ to XPO
Filtering
Sorting
Free Joins
Stored Procedures
Direct SQL Queries
NULL Value Handling Specifics
Always Encrypted (SQL Server only)
Delayed Loading
Optimistic Concurrency
Custom Collection Types
Select Data from Multiple Tables
Best Practices and Troubleshooting
Design-Time Features
Examples
Member Tables
API Reference
Download CHM

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:

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.

XPQuery<Boxer> boxers = new XPQuery<Boxer>(session);  
var list = from b in boxers  
            join bc in boxers  
            on bc.Weight >= Math.Floor(b.Weight / 10) * 10 &  
                bc.Weight <= Math.Floor(b.Weight / 10 + 1) * 10  
            into bcg  
            where b.WinNumber = bcg.Max(bcgi => bcgi.WinNumber)  
            select b;