Not logged in

LINQ to XPO

Article Details

ID:	K18051
Version(s):	8.x, 9.x, 10.x
Updated:	10/25/2011

Product Group:	.NET
Product:	eXpress Persistent Objects

Description:

LINQ is .NET Language-Integrated Query. It's included in .NET Framework 3.5 and you can use it in Visual Studio 2008 projects. You can use it to query persistent objects (XPO).

XPO officially supports LINQ since v7.3. This article provides several sample queries to help you get started with LINQ to XPO.

Solution

XPO 2007 vol 3 includes a new assembly - DevExpress.Xpo.v7.3.Linq. This assembly implements the XPQuery class for constructing LINQ expressions against persistent objects. To run the attached sample project, you should have Visual Studio 2008 and SQL Server 2005 or 2000 with the Northwind demo database. Below are the sample expressions from our project.

[C#]

using System.Linq;

using System.Linq.Expressions;

using DevExpress.Xpo;



//...

XPQuery<Customer> customers = new XPQuery<Customer>(Session.DefaultSession);

XPQuery<Order> orders = new XPQuery<Order>(Session.DefaultSession);

XPQuery<Employee> employees = new XPQuery<Employee>(Session.DefaultSession);



// 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



'...

Private customers As XPQuery(Of Customer)

Private orders As XPQuery(Of Order)

Private employees As XPQuery(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")))

XPO LINQ expressions are turned into pure database queries. That is, an expression is processed on the database server's side and only the requested objects or a scalar value are loaded onto the client. To see all of this in action, take a close look at the database queries executed by XPO - they are displayed in a log window in our sample project.

Example: LINQ to XPO

ID:

E642

Supported IDEs:

Microsoft Visual Studio 2008, Microsoft Visual Studio 2010

Supported Version(s):

v2008 vol 2.6 - v2011 vol 2.8

Programming Language:

Product:	eXpress Persistent Objects
Control:	n/a
Updated:	5/27/2011

Description:

LINQ is .NET Language-Integrated Query. It's included in .NET Framework 3.5 and you can use it in Visual Studio 2008 projects.
XPO officially supports LINQ since v7.3. This example provides several sample queries to help you get started with LINQ to XPO.

See Also:
LINQ to XPO
The LINQ project

Do you have any comments? We are eager to hear them!

How would you rate the quality of this content?

Average rating: 8.5 out of 9
12 people have rated this page

Post Your Vote (Login Required)

If you need any clarification with regards to the contents of this article, please don't hesitate to contact our Support Team.

Product Group:

Product:

Keywords:

Searching Tips

Suggestions
Active: 1,163
Processed: 31,202
Questions & Issues
Active: 931
Processed: 256,392 + 101,114
2,126 Knowledge Base Articles
3,463 Code Central Examples