LINQ to XPO

• Nov 30, 2018
• 4 minutes to read

XPO includes the XPQuery<T> class tailored for constructing LINQ expressions against persistent objects.

You have the following options to create XPQuery<T> instances.

• Create an instance from a session’s scope, via the session’s XPQueryExtensions.Query<T> extension method.
• Pass a session 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

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")))

Note

You can find a sample project in the LINQ to XPO Knowledge Base article. You should have Visual Studio 2008 and SQL Server 2005 or 2000 with the Northwind demo database.

To learn 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.

LINQ to XPO Limitations

• The maximum number of total or group summaries allowed is 14.
• The FirstOrDefault method is not supported for property initialization in the Select statement.
• Cross Join queries are not supported.