LINQ Collection

This topic applies to .NET version only

This chapter provides a collection of LINQ examples with db4o, which you can use to find different ways to construct your queries. The information below covers most of the database querying operations and should be enough for the majority of cases. However, it is still advisable to study MSDN resources, which explain in detail how LINQ and Extension methods work. For the introduction to LINQ queries for db4o please refer to LINQ chapter.

Simple Select

Let's fill up the database with some data:

LinqCollection.cs: StoreObjects
01private static void StoreObjects()
02        {
03            File.Delete(Db4oFileName);
04            IObjectContainer container = Database();
05            if (container != null)
06            {
07                try
08                {
09                    Pilot pilot;
10                    Car car;
11                    for (int i = 0; i < ObjectCount; i++)
12                    {
13                        pilot = new Pilot("Test Pilot #" + i, i + 10);
14                        car = new Car("Test model #" + i, pilot);
15                        container.Store(car);
16                    }
17                    container.Commit();
18                }
19                catch (Db4oException ex)
20                {
21                    System.Console.WriteLine("Db4o Exception: " + ex.Message);
22                }
23                catch (Exception ex)
24                {
25                    System.Console.WriteLine("System Exception: " + ex.Message);
26                }
27                finally
28                {
29                    CloseDatabase();
30                }
31            }
32        }
LinqCollection.vb: StoreObjects
01Private Shared Sub StoreObjects()
02            File.Delete(Db4oFileName)
03            Dim container As IObjectContainer = Database()
04            If container IsNot Nothing Then
05                Try
06                    Dim pilot As Pilot
07                    Dim car As Car
08                    For i As Integer = 0 To ObjectCount - 1
09                        pilot = New Pilot("Test Pilot #" + i.ToString(), i + 10)
10                        car = New Car("Test model #" + i.ToString(), pilot)
11                        container.Store(car)
12                    Next
13                    container.Commit()
14                Catch ex As Db4oException
15                    System.Console.WriteLine("Db4o Exception: " + ex.Message)
16                Catch ex As Exception
17                    System.Console.WriteLine("System Exception: " + ex.Message)
18                Finally
19                    CloseDatabase()
20                End Try
21            End If
22        End Sub

In order to select objects of only Pilot class we can issue the following query:

LinqCollection.cs: SelectPilotByNameAndPoints
01private static void SelectPilotByNameAndPoints()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    IEnumerable<Pilot> result = from Pilot p in container
09                                                where p.Name.StartsWith("Test") && p.Points > 12
10                                                select p;
11
12                    ListResult(result);
13                }
14                catch (Exception ex)
15                {
16                    System.Console.WriteLine("System Exception: " + ex.Message);
17                }
18                finally
19                {
20                    CloseDatabase();
21                }
22            }
23        }
LinqCollection.vb: SelectPilotByNameAndPoints
01Private Shared Sub SelectPilotByNameAndPoints()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result As IEnumerable(Of Pilot) = From p As Pilot In container _
06                                                Where p.Name.StartsWith("Test") And p.Points > 12 _
07                                                Select p
08
09                    ListResult(result)
10                catch ex As Exception
11                    System.Console.WriteLine("System Exception: " + ex.Message)
12                finally
13                    CloseDatabase()
14                End Try
15            End If
16        End Sub

Note, that where clause is optional: if it is not specified all objects of Pilot class will be returned.

We can make querying even easier by using anonymous types:

LinqCollection.cs: SelectByNameAndPoints
01private static void SelectByNameAndPoints()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from Pilot p in container
09                                 where p.Name.StartsWith("Test") && p.Points > 12
10                                 select p;
11
12                    ListResult(result);
13                }
14                catch (Exception ex)
15                {
16                    System.Console.WriteLine("System Exception: " + ex.Message);
17                }
18                finally
19                {
20                    CloseDatabase();
21                }
22            }
23        }
LinqCollection.vb: SelectByNameAndPoints
01Private Shared Sub SelectByNameAndPoints()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                                 Where p.Name.StartsWith("Test") And p.Points > 12 _
07                                 Select p
08                    ListResult(result)
09                catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

Database Object Clone

You can easily use LINQ to create database objects clones:

LinqCollection.cs: SelectClone
01private static void SelectClone()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from Pilot p in container
09                                 where p.Name.StartsWith("Test") && p.Points > 12
10                                 select new Pilot(p.Name, p.Points);
11
12                    ListResult(result);
13                }
14                catch (Exception ex)
15                {
16                    System.Console.WriteLine("System Exception: " + ex.Message);
17                }
18                finally
19                {
20                    CloseDatabase();
21                }
22            }
23        }
LinqCollection.vb: SelectClone
01Private Shared Sub SelectClone()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                                 Where p.Name.StartsWith("Test") And p.Points > 12 _
07                                 Select New Pilot(p.Name, p.Points)
08                    ListResult(result)
09                Catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                Finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

Retrieved objects are not bound to the object container, but they duplicate the values from the database. You can use them for an "object-readonly" mode.

Select Any Type

With the selection we are not bound to only one type of objects - actually we can select everything that is currently in the database:

LinqCollection.cs: SelectEverythingByName
01private static void SelectEverythingByName()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from object o in container
09                                 where o.ToString().StartsWith("Test")
10                                 select o;
11                    ListResult(result);
12                }
13                catch (Exception ex)
14                {
15                    System.Console.WriteLine("System Exception: " + ex.Message);
16                }
17                finally
18                {
19                    CloseDatabase();
20                }
21            }
22        }
LinqCollection.vb: SelectEverythingByName
01Private Shared Sub SelectEverythingByName()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From o As Object In container _
06                                 Select res = o _
07                                 Where res.ToString().StartsWith("Test")
08                    ListResult(result)
09                Catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

We can further use this broad selection too. As the query result implements IQueryable interface, we can re-use it to retrieve more specific objects:

LinqCollection.cs: SelectFromSelection
01private static void SelectFromSelection()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var allObjects = from object o in container select o;
09                    var listObjects = allObjects.ToList();
10                    var pilots = from object p in listObjects 
11                                 where p.GetType().FullName.Equals("Db4objects.Db4odoc.LinqCollection.Pilot") 
12                                 && ((Pilot)p).Points > 25
13                                 select (Pilot)p;
14                    ListResult(pilots);
15                    var cars = from object car in allObjects
16                               where car.GetType().FullName.Equals("Db4objects.Db4odoc.LinqCollection.Car")
17                               && pilots.Contains(((Car)car).Pilot) 
18                               select (Car)car;
19                    ListResult(cars);
20                }
21                catch (Exception ex)
22                {
23                    System.Console.WriteLine("System Exception: " + ex.Message);
24                }
25                finally
26                {
27                    CloseDatabase();
28                }
29            }
30        }
LinqCollection.vb: SelectFromSelection
01Private Shared Sub SelectFromSelection()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim allObjects = From o As Object In container Select o
06                    Dim listOfObjects = allObjects.ToList()
07                    Dim pilots = From p As Object In allObjects _
08                    Where (p.GetType().FullName.Equals("Linq.Db4objects.Db4odoc.Linq.Pilot")) _
09                    Select pilot1 = CType(p, Pilot) Where pilot1.Points > 25
10                    ListResult(pilots)
11                    Dim cars = From car As Object In allObjects _
12                               Where car.GetType().FullName.Equals("Linq.Db4objects.Db4odoc.Linq.Car") _
13                               Select car1 = CType(car, Car) _
14                               Where pilots.Contains(CType(car1, Car).Pilot)
15                    ListResult(cars)
16                Catch ex As Exception
17                    System.Console.WriteLine("System Exception: " + ex.Message)
18                finally
19                    CloseDatabase()
20                End Try
21            End If
22        End Sub

In this example we use an all objects selection to find a range of pilots and then cars with pilots within the range. Remember, however, that the objects in allObjects variable are not actually retrieved from the database until they are browsed or used. If you want to get them all into the memory for future use, utilize ToList or ToArray methods.

Join Query

Above we've already discussed how to select objects of one type based on the selection of the other type. However, that way is rather cumbersome and most probably you won't need it: you can use join operator instead:

LinqCollection.cs: SelectJoin
01private static void SelectJoin()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from Pilot p in container
09                                  from Car c in container
10                                  where p.Points > 25
11                                  && c.Pilot.Equals(p) 
12                                  select c;
13
14                    ListResult(result);
15                }
16                catch (Exception ex)
17                {
18                    System.Console.WriteLine("System Exception: " + ex.Message);
19                }
20                finally
21                {
22                    CloseDatabase();
23                }
24            }
25        }
LinqCollection.vb: SelectJoin
01Private Shared Sub SelectJoin()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                                  From c As Car In container _
07                    Where (p.Points > 25) _
08                                  And c.Pilot.Equals(p) _
09                                  Select c
10                    ListResult(result)
11                Catch ex As Exception
12                    System.Console.WriteLine("System Exception: " + ex.Message)
13                Finally
14                    CloseDatabase()
15                End Try
16            End If
17        End Sub

This simple syntax allows you to join any amount of classes using any possible relationship between them. You can also make use of Join extension method syntax to get the same result.

Sorting

Sorting is added with orderby operator and can be ascending(default) or descending.

LinqCollection.cs: SelectOrdered
01private static void SelectOrdered()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from Pilot p in container
09                                 where p.Points < 15
10                                 orderby p.Name descending
11                                 select p;
12
13                    ListResult(result);
14                }
15                catch (Exception ex)
16                {
17                    System.Console.WriteLine("System Exception: " + ex.Message);
18                }
19                finally
20                {
21                    CloseDatabase();
22                }
23            }
24        }
LinqCollection.vb: SelectOrdered
01Private Shared Sub SelectOrdered()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                    Where (p.Points < 15) Order By p.Name Descending Select p
07                    ListResult(result)
08                Catch ex As Exception
09                    System.Console.WriteLine("System Exception: " + ex.Message)
10                Finally
11                    CloseDatabase()
12                End Try
13            End If
14        End Sub

We can also use subsequent sorts. For example sort by name descending and by points ascending:

LinqCollection.cs: StoreForSorting
01private static void StoreForSorting()
02        {
03            File.Delete(Db4oFileName);
04            IObjectContainer container = Database();
05            if (container != null)
06            {
07                try
08                {
09                    Pilot pilot;
10                    for (int i = 0; i < ObjectCount; i++)
11                    {
12                        pilot = new Pilot("Test Pilot #" + i, i);
13                        container.Store(pilot);
14                    }
15                    for (int i = 0; i < ObjectCount; i++)
16                    {
17                        pilot = new Pilot("Test Pilot #" + i, (i + 10));
18                        container.Store(pilot);
19                    }
20                    container.Commit();
21                }
22                catch (Db4oException ex)
23                {
24                    System.Console.WriteLine("Db4o Exception: " + ex.Message);
25                }
26                catch (Exception ex)
27                {
28                    System.Console.WriteLine("System Exception: " + ex.Message);
29                }
30                finally
31                {
32                    CloseDatabase();
33                }
34            }
35        }
LinqCollection.cs: SelectComplexOrdered
01private static void SelectComplexOrdered()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = from Pilot p in container
09                                 orderby p.Name descending,
10                                 p.Points ascending
11                                 select p;
12
13                    ListResult(result);
14                }
15                catch (Exception ex)
16                {
17                    System.Console.WriteLine("System Exception: " + ex.Message);
18                }
19                finally
20                {
21                    CloseDatabase();
22                }
23            }
24        }
LinqCollection.vb: StoreForSorting
01Private Shared Sub StoreForSorting()
02
03            File.Delete(Db4oFileName)
04            Dim container As IObjectContainer = Database()
05            If container IsNot Nothing Then
06                Try
07                    Dim p As Pilot
08                    For i As Integer = 0 To ObjectCount - 1
09                        p = New Pilot("Test Pilot #" + i.ToString(), i)
10                        container.Store(p)
11                    Next
12                    For i As Integer = 0 To ObjectCount - 1
13                        p = New Pilot("Test Pilot #" + i.ToString(), (i + 10))
14                        container.Store(p)
15                    Next
16                    container.Commit()
17                Catch ex As Db4oException
18                    System.Console.WriteLine("Db4o Exception: " + ex.Message)
19                Catch ex As Exception
20                    System.Console.WriteLine("System Exception: " + ex.Message)
21                Finally
22                    CloseDatabase()
23                End Try
24            End If
25        End Sub
LinqCollection.vb: SelectComplexOrdered
01Private Shared Sub SelectComplexOrdered()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                                 Order By p.Name Descending Order By p.Points Ascending _
07                                 Select p
08                    ListResult(result)
09                catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

The same effect can be achieved by using OrderBy/OrderByDescending and ThenBy/ThenByDescending extension methods.

Grouping Results

Grouping results can be often useful in different reports. For example, we can group the data from the previous example by pilot's name:

LinqCollection.cs: SelectGroupByName
01private static void SelectGroupByName()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    var result = (from Pilot p in container
09                                  orderby p.Points descending
10                                  select p).GroupBy(value => value.Points, value => new {value.Name, value.Points});
11                    foreach (var group in result)
12                    {
13                        Console.WriteLine("Pilots with {0} points:", group.Key);
14
15                        foreach (var tuple in group)
16                            Console.WriteLine("  {0}", tuple);
17                    }
18                }
19                catch (Exception ex)
20                {
21                    System.Console.WriteLine("System Exception: " + ex.Message);
22                }
23                finally
24                {
25                    CloseDatabase();
26                }
27            }
28        }
LinqCollection.vb: SelectGroupByName
01Private Shared Sub SelectGroupByName()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    Dim result = From p As Pilot In container _
06                                  Order By p.Points Descending _
07                                  Select p Group By p.Points, p.Name Into Group Select Name, Points
08                    For Each value In result
09                        Console.WriteLine("  {0}", value)
10                    Next
11                Catch ex As Exception
12                    System.Console.WriteLine("System Exception: " + ex.Message)
13                Finally
14                    CloseDatabase()
15                End Try
16            End If
17        End Sub

Here we use GroupBy extension method. The parameters specify the grouping property (Points) and the display property (Name).

Modified Result

Sometimes we need to apply some calculation on each result - with LINQ we can do that directly in the query:

LinqCollection.cs: SelectWithModifiedResult
01private static void SelectWithModifiedResult()
02        {
03            IObjectContainer container = Database();
04            int maxPoints = 100;
05            if (container != null)
06            {
07                try
08                {
09                    /*Select percentage*/
10                    var result = from Pilot p in container
11                                 where p.Name.StartsWith("Test")
12                                 select String.Format("{0}: {1}%", p.Name, (p.Points * 100 / maxPoints));
13
14                    ListResult(result);
15                }
16                catch (Exception ex)
17                {
18                    System.Console.WriteLine("System Exception: " + ex.Message);
19                }
20                finally
21                {
22                    CloseDatabase();
23                }
24            }
25        }
LinqCollection.vb: SelectWithModifiedResult
01Private Shared Sub SelectWithModifiedResult()
02            Dim container As IObjectContainer = Database()
03            Dim maxPoints As Integer = 100
04            If container IsNot Nothing Then
05                Try
06                    ' Select percentage
07                    Dim result = From p As Pilot In container _
08                    Where (p.Name.StartsWith("Test")) _
09                                 Select String.Format("{0}: {1}%", p.Name, (p.Points * 100 / maxPoints))
10                    ListResult(result)
11                catch ex As Exception
12                    System.Console.WriteLine("System Exception: " + ex.Message)
13                finally
14                    CloseDatabase()
15                End Try
16            End If
17        End Sub

In the example above the query will bring as the calculated percentage of maximum for each pilot's points.

Selecting A Result

Though a query usually returns all the candidates matching the selected criteria, we can specify which of the results we want: First, ElementAt, Any, All.

LinqCollection.cs: SelectAny
01private static void SelectAny()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    /*Checks if query returns any results*/
09                    var result = (from Pilot p in container
10                                  where p.Name.EndsWith("Test")
11                                  select p).Any();
12
13                    System.Console.WriteLine("The query returns any results: " + result);
14                }
15                catch (Exception ex)
16                {
17                    System.Console.WriteLine("System Exception: " + ex.Message);
18                }
19                finally
20                {
21                    CloseDatabase();
22                }
23            }
24        }
LinqCollection.vb: SelectAny
01Private Shared Sub SelectAny()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    'Checks if query returns any results
06                    Dim result = Aggregate p As Pilot In container _
07                    Where p.Name.EndsWith("Test") Into Any()
08                    System.Console.WriteLine("The query returns any results: " + result.ToString())
09                Catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                Finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

The query above will return true if there are any objects in the result set and false otherwise.

Aggregate Functions

Often we are not interested in each and every result, but need some statistics about it: sum, aggregate, average, max etc. This can be achieved with extension methods.

LinqCollection.cs: SelectAverage
01private static void SelectAverage()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    /*Find the average of pilot points*/
09                    var result = (from Pilot p in container
10                                  where p.Name.StartsWith("Test")
11                                  select p.Points).Average();
12
13                    System.Console.WriteLine(result);
14                }
15                catch (Exception ex)
16                {
17                    System.Console.WriteLine("System Exception: " + ex.Message);
18                }
19                finally
20                {
21                    CloseDatabase();
22                }
23            }
24        }
LinqCollection.vb: SelectAverage
01Private Shared Sub SelectAverage()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    'Find the average of pilot points
06                    Dim result = Aggregate p As Pilot In container _
07                    Where p.Name.StartsWith("Test") Into Average(p.Points)
08                    System.Console.WriteLine(result)
09                Catch ex As Exception
10                    System.Console.WriteLine("System Exception: " + ex.Message)
11                Finally
12                    CloseDatabase()
13                End Try
14            End If
15        End Sub

This method returns only the average value of pilot points.

LinqCollection.cs: SelectAggregate
01private static void SelectAggregate()
02        {
03            IObjectContainer container = Database();
04            if (container != null)
05            {
06                try
07                {
08                    /*Select pilot names separated by semicolon*/
09                    var result = (from Pilot p in container
10                                  where p.Name.StartsWith("Test")
11                                  select p.Name).Aggregate(new StringBuilder(), 
12                                  (acc, value) => acc.AppendFormat("{0}; ", value));
13
14                    System.Console.WriteLine(result);
15                }
16                catch (Exception ex)
17                {
18                    System.Console.WriteLine("System Exception: " + ex.Message);
19                }
20                finally
21                {
22                    CloseDatabase();
23                }
24            }
25        }
LinqCollection.vb: SelectAggregate
01Private Shared Sub SelectAggregate()
02            Dim container As IObjectContainer = Database()
03            If container IsNot Nothing Then
04                Try
05                    ' Select pilot names separated by semicolon
06                    Dim result = From p As Pilot In container _
07                    Where p.Name.StartsWith("Test") Select p.Name
08                    Dim sumString As String = result.Aggregate("", Function(acc, value) acc + ";" + value)
09
10                    System.Console.WriteLine(sumString)
11
12                Catch ex As Exception
13                    System.Console.WriteLine("System Exception: " + ex.Message)
14                Finally
15                    CloseDatabase()
16                End Try
17            End If
18        End Sub

In this case we use Aggregate extension method to return all the names and a semicolon-separated string. In Aggregate function first parameter specify the initial return value, second parameter is a function that appends each new value to the initial value.