LINQ to XPO

• Oct 09, 2023
• 4 minutes to read

XPO includes the XPQuery<T> class designed to build LINQ expressions and execute them against the underlying data store. XPQuery<T> can chain together multiple queries and supports deferred execution.

To run a query and load data, call the ToList() or ToArray() extension method, or use the foreach operator to enumerate XPQuery<T>.

You can create XPQuery<T> instances in the following ways:

• Create an instance from a session’s scope, via the session’s XPQueryExtensions.Query<T> extension method.
• Pass a session or Unit of Work to an XPQuery<T> constructor.

C#

using System.Linq;
using DevExpress.Xpo;

XPQuery<Customer> customers = Session.DefaultSession.Query<Customer>();
// Equivalent definition.
// XPQuery<Customer> customers = new XPQuery<Customer>(Session.DefaultSession);

foreach (Customer cust in customers)
    Console.WriteLine(string.Format("{0}", cust.ContactName));

VB.NET

Imports System.Linq
Imports DevExpress.Xpo

Dim customers As XPQuery(Of Customer) = Session.DefaultSession.Query(Of Customer)()
' Equivalent definition.
' XPQuery<Customer> customers = new XPQuery<Customer>(Session.DefaultSession);

For Each cust As Customer In customers
    Console.WriteLine(String.Format("{0}", cust.ContactName))
Next cust

In addition to calling the XPQueryExtensions.Query<T> extension method, you can call the XPQueryExtensions.QueryInTransaction<T> method to include all in-memory object changes to query results (as if Session.InTransactionMode is enabled). To create an InTransaction XPQuery<T> instance based on an existing XPQuery<T> instance, call its XPQuery<T>.InTransaction method.

Sample Expressions

View Example: LINQ to XPO

C#

using System.Linq;
using System.Linq.Expressions;
using DevExpress.Xpo;

XPQuery<Customer> customers = Session.DefaultSession.Query<Customer>();
XPQuery<Order> orders = Session.DefaultSession.Query<Order>();
XPQuery<Employee> employees = Session.DefaultSession.Query<Employee>();

// Simple Select with Where and OrderBy clauses
var list = from c in customers
           where (c.Country == "Germany" && c.ContactTitle == "Sales Representative")
           orderby c.ContactName
           select c;
foreach (Customer cust in list)
    Console.WriteLine(string.Format("{0}\t{1}\t{2}", cust.ContactName,
        cust.Country, cust.ContactTitle));

// Select Top 5 objects
var list = (from o in orders
            orderby o.ShippedDate descending
            select o).Take(5);
foreach (Order order in list)
    Console.WriteLine(string.Format("{0}\t{1}\t{2}", order.OrderID, order.ShippedDate,
        order.Customer.CompanyName));

// Group Join customers with an aggregation on their Orders
var list = from c in customers
           join o in orders on c equals o.Customer into oo
           where oo.Count() >= 1
           select new { c.CompanyName, OrderCount = oo.Count() };
foreach (var item in list)
    Console.WriteLine(string.Format("{0}\t{1}", item.CompanyName, item.OrderCount));

// An example of aggregated functions (Count and Average)
var list = from o in orders
           select o;
int count = list.Count();
Console.WriteLine(string.Format("Orders Row Count: {0}", count));

decimal avg = list.Average(x => x.Freight);
Console.WriteLine(string.Format("Orders Average Freight: {0:c2}", avg));

// Select with Group By
var list = from c in customers
           group c by c.ContactTitle into cc
           where cc.Count() >= 1
           select new { Title = cc.Key, Count = cc.Count() };
foreach (var item in list)
    Console.WriteLine(string.Format("{0}\t{1}", item.Title, item.Count));

// Any method 
bool result = customers.Any(c => c.Country == "Spain");
Console.WriteLine(string.Format("Is there any customer from Spain? {0}", result ? "Yes" : "No"));

result = customers.Any(c => c.Country == "Monaco");
Console.WriteLine(string.Format("Is there any customer from Monaco? {0}", result ? "Yes" : "No"));

VB.NET

Imports System.Linq
Imports System.Linq.Expressions
Imports DevExpress.Xpo

Dim customers As XPQuery(Of Customer) = Session.DefaultSession.Query(Of Customer)()
Dim orders As XPQuery(Of Order) = Session.DefaultSession.Query(Of Order)()
Dim employees As XPQuery(Of Employee) = Session.DefaultSession.Query(Of Employee)()

' simple Select with Where and OrderBy clauses
Dim list = From c In customers _
        Where c.Country = "Germany" And c.ContactTitle = "Sales Representative" _
        Order By c.ContactName _
        Select c

For Each cust As Customer In list
    Console.WriteLine(String.Format("{0}" & Constants.vbTab & "{1}" & Constants.vbTab & "{2}", _
    cust.ContactName, cust.Country, cust.ContactTitle))
Next

' Select Top 5 objects
Dim list = (From o In orders Order By o.ShippedDate Descending Select o).Take(5)
For Each order As Order In list
    Console.WriteLine(String.Format("{0}" & Constants.vbTab & "{1}" & Constants.vbTab & "{2}", _
    order.OrderID, order.ShippedDate, order.Customer.CompanyName))
Next

' Group Join customers with an aggregation on their Orders
Dim list = From c In customers _
           Group Join o In orders On c Equals o.Customer _
           Into oo = Group _
           Where oo.Count >= 1 _
           Select New With {c.CompanyName, .OrderCount = oo.Count()}
For Each item In list
    Console.WriteLine(String.Format("{0}" & Constants.vbTab & "{1}", _
    item.CompanyName, item.OrderCount))
Next

' an example of aggregated functions (Count and Average)
Dim list = From o In orders Select o
Dim count As Integer = list.Count()
Console.WriteLine(String.Format("Orders Row Count: {0}", count))

Dim avg As Decimal = list.Average(Function(x) x.Freight)
Console.WriteLine(String.Format("Orders Average Freight: {0:c2}", avg))

' Any method 
Dim result As Boolean = customers.Any(Function(c) c.Country = "Spain")
Console.WriteLine(String.Format("Is there any customer from Spain? {0}", If(result, "Yes", "No")))
result = customers.Any(Function(c) c.Country = "Monaco")
Console.WriteLine(String.Format("Is there any customer from Monaco? {0}", If(result, "Yes", "No")))

For more information on how to implement custom functions and criteria, and use them in LINQ to XPO expressions, see How to: Implement Custom Functions and Criteria in LINQ to XPO.