eXpress Persistent Objects - K18051 - LINQ to XPO

Home

Support Center Home

Categories (Beta)

Report Issue

Security

Issue Reports
Total: 43356
Active: 393
Processed: 42963

Suggestions
Total: 17800
Active: 505
Processed: 17295

Questions
Total: 98455
Active: 82
Processed: 98373

1851 Knowledge Base Articles

632 Code Central Examples

This website is powered by Developer Express ASP.NET technologies including the ASPxMenu, ASPxNavBar, ASPxTabControl, ASPxSiteMapControl, ASPxPopupControl and the ASPxGridView and Editors Suite.
Database connectivity is via eXpressPersistent Objects.


	Knowledge Base Article

LINQ to XPO

Article ID:	K18051
Product Group:	.NET
Product:	eXpress Persistent Objects

Version(s):	7.3.X - 8.X
Updated:	6 Jan 2009
Categories:	n/a

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.

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

How would you rate the quality of this content?

Average rating: 9 out of 9
8 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.