SODA Query API

There are occasions when we don't want to query for exact field values, but rather for value ranges, objects not containing given member values, etc. This functionality is provided by the Constraint API.

Not

First, let's negate a query to find all pilots who are not Michael Schumacher:

QueryExample.cs: RetrieveByNegation
1public static void RetrieveByNegation(IObjectContainer db) 2 { 3 IQuery query = db.Query(); 4 query.Constrain(typeof(Pilot)); 5 query.Descend("_name").Constrain("Michael Schumacher").Not(); 6 IObjectSet result = query.Execute(); 7 ListResult(result); 8 }
QueryExample.vb: RetrieveByNegation
1Public Shared Sub RetrieveByNegation(ByVal db As IObjectContainer) 2 Dim query As IQuery = db.Query() 3 query.Constrain(GetType(Pilot)) 4 query.Descend("_name").Constrain("Michael Schumacher").[Not]() 5 Dim result As IObjectSet = query.Execute() 6 ListResult(result) 7 End Sub

And 

Where there is negation, the other boolean operators can't be too far.

QueryExample.cs: RetrieveByConjunction
01public static void RetrieveByConjunction(IObjectContainer db) 02 { 03 IQuery query = db.Query(); 04 query.Constrain(typeof(Pilot)); 05 IConstraint constr = query.Descend("_name") 06 .Constrain("Michael Schumacher"); 07 query.Descend("_points") 08 .Constrain(99).And(constr); 09 IObjectSet result = query.Execute(); 10 ListResult(result); 11 }
QueryExample.vb: RetrieveByConjunction
1Public Shared Sub RetrieveByConjunction(ByVal db As IObjectContainer) 2 Dim query As IQuery = db.Query() 3 query.Constrain(GetType(Pilot)) 4 Dim constr As Constraint = query.Descend("_name").Constrain("Michael Schumacher") 5 query.Descend("_points").Constrain(99).Or(constr) 6 Dim result As IObjectSet = query.Execute() 7 ListResult(result) 8 End Sub

Or 

QueryExample.cs: RetrieveByDisjunction
01public static void RetrieveByDisjunction(IObjectContainer db) 02 { 03 IQuery query = db.Query(); 04 query.Constrain(typeof(Pilot)); 05 IConstraint constr = query.Descend("_name") 06 .Constrain("Michael Schumacher"); 07 query.Descend("_points") 08 .Constrain(99).Or(constr); 09 IObjectSet result = query.Execute(); 10 ListResult(result); 11 }
QueryExample.vb: RetrieveByDisjunction
1Public Shared Sub RetrieveByDisjunction(ByVal db As IObjectContainer) 2 Dim query As IQuery = db.Query() 3 query.Constrain(GetType(Pilot)) 4 Dim constr As Constraint = query.Descend("_name").Constrain("Michael Schumacher") 5 query.Descend("_points").Constrain(99).Or(constr) 6 Dim result As IObjectSet = query.Execute() 7 ListResult(result) 8 End Sub

Greater, Smaller, Equal <>=

We can also constrain to a comparison with a given value.

Return pilots with more than 99 points:

QueryExample.cs: RetrieveByComparison
1public static void RetrieveByComparison(IObjectContainer db) 2 { 3 IQuery query = db.Query(); 4 query.Constrain(typeof(Pilot)); 5 query.Descend("_points") 6 .Constrain(99).Greater(); 7 IObjectSet result = query.Execute(); 8 ListResult(result); 9 }
QueryExample.vb: RetrieveByComparison
1Public Shared Sub RetrieveByComparison(ByVal db As IObjectContainer) 2 Dim query As IQuery = db.Query() 3 query.Constrain(GetType(Pilot)) 4 query.Descend("_points").Constrain(99).Greater() 5 Dim result As IObjectSet = query.Execute() 6 ListResult(result) 7 End Sub

Return pilots with 99 or more points:

QueryExample.cs: RetrieveByEqualComparison
1public static void RetrieveByEqualComparison(IObjectContainer db) 2 { 3 IQuery query = db.Query(); 4 query.Constrain(typeof(Pilot)); 5 query.Descend("_points") 6 .Constrain(99).Greater().Equal(); 7 IObjectSet result = query.Execute(); 8 ListResult(result); 9 }
QueryExample.vb: RetrieveByEqualComparison
1Public Shared Sub RetrieveByEqualComparison(ByVal db As IObjectContainer) 2 Dim query As IQuery = db.Query() 3 query.Constrain(GetType(Pilot)) 4 query.Descend("_points").Constrain(99).Greater().Equal() 5 Dim result As IObjectSet = query.Execute() 6 ListResult(result) 7 End Sub

Using Default Values 

The query API also allows to query for field default values.

QueryExample.cs: RetrieveByDefaultFieldValue
01public static void RetrieveByDefaultFieldValue(IObjectContainer db) 02 { 03 Pilot somebody = new Pilot("Somebody else", 0); 04 db.Set(somebody); 05 IQuery query = db.Query(); 06 query.Constrain(typeof(Pilot)); 07 query.Descend("_points").Constrain(0); 08 IObjectSet result = query.Execute(); 09 ListResult(result); 10 db.Delete(somebody); 11 }
QueryExample.vb: RetrieveByDefaultFieldValue
01Public Shared Sub RetrieveByDefaultFieldValue(ByVal db As IObjectContainer) 02 Dim somebody As Pilot = New Pilot("Somebody else", 0) 03 db.Set(somebody) 04 Dim query As IQuery = db.Query() 05 query.Constrain(GetType(Pilot)) 06 query.Descend("_points").Constrain(0) 07 Dim result As IObjectSet = query.Execute() 08 ListResult(result) 09 db.Delete(somebody) 10 End Sub

String Comparisons

Like 

This is an equivalent to SQL "like" operator:

SodaExample.cs: TestLike
01public static void TestLike() 02 { 03 File.Delete(Db4oFileName); 04 IObjectContainer container = Database(); 05 if (container != null) 06 { 07 try 08 { 09 Pilot pilot = new Pilot("Test Pilot1", 100); 10 container.Set(pilot); 11 pilot = new Pilot("Test Pilot2", 102); 12 container.Set(pilot); 13 14 // Simple.IQuery 15 IQuery query1 = container.Query(); 16 query1.Constrain(typeof(Pilot)); 17 query1.Descend("_name").Constrain("est"); 18 IObjectSet result = query1.Execute(); 19 ListResult(result); 20 21 // Like.IQuery 22 IQuery query2 = container.Query(); 23 query2.Constrain(typeof(Pilot)); 24 // All pilots with the name containing "est" will be retrieved 25 query2.Descend("_name").Constrain("est").Like(); 26 result = query2.Execute(); 27 ListResult(result); 28 } 29 catch (Db4oException ex) 30 { 31 Console.WriteLine("Db4o Exception: " + ex.Message); 32 } 33 catch (Exception ex) 34 { 35 Console.WriteLine("System Exception: " + ex.Message); 36 } 37 finally 38 { 39 CloseDatabase(); 40 } 41 } 42 }
SodaExample.vb: TestLike
01Public Shared Sub TestLike() 02 File.Delete(Db4oFileName) 03 Dim container As IObjectContainer = Database() 04 If container IsNot Nothing Then 05 Try 06 Dim pilot As New Pilot("Test Pilot1", 100) 07 container.[Set](pilot) 08 pilot = New Pilot("Test Pilot2", 102) 09 container.[Set](pilot) 10 11 ' Simple.IQuery 12 Dim query1 As IQuery = container.Query() 13 query1.Constrain(GetType(Pilot)) 14 query1.Descend("_name").Constrain("est") 15 Dim result As IObjectSet = query1.Execute() 16 ListResult(result) 17 18 ' Like.IQuery 19 Dim query2 As IQuery = container.Query() 20 query2.Constrain(GetType(Pilot)) 21 ' All pilots with the name containing "est" will be retrieved 22 query2.Descend("_name").Constrain("est").[Like]() 23 result = query2.Execute() 24 ListResult(result) 25 Catch ex As Db4oException 26 Console.WriteLine("Db4o Exception: " + ex.Message) 27 Catch ex As Exception 28 Console.WriteLine("System Exception: " + ex.Message) 29 Finally 30 CloseDatabase() 31 End Try 32 End If 33 End Sub

startsWith, endsWith 

Compares a beginning or ending of a string:

SodaExample.cs: TestStartsEnds
01public static void TestStartsEnds() 02 { 03 File.Delete(Db4oFileName); 04 IObjectContainer container = Database(); 05 if (container != null) 06 { 07 try 08 { 09 Pilot pilot = new Pilot("Test Pilot0", 100); 10 container.Set(pilot); 11 pilot = new Pilot("Test Pilot1", 101); 12 container.Set(pilot); 13 pilot = new Pilot("Test Pilot2", 102); 14 container.Set(pilot); 15 16 IQuery query = container.Query(); 17 query.Constrain(typeof(Pilot)); 18 query.Descend("_name").Constrain("T0").EndsWith(false).Not(); 19 //query.Descend("_name").Constrain("Pil").StartsWith(true); 20 IObjectSet result = query.Execute(); 21 ListResult(result); 22 } 23 catch (Db4oException ex) 24 { 25 Console.WriteLine("Db4o Exception: " + ex.Message); 26 } 27 catch (Exception ex) 28 { 29 Console.WriteLine("System Exception: " + ex.Message); 30 } 31 finally 32 { 33 CloseDatabase(); 34 } 35 } 36 }
SodaExample.vb: TestStartsEnds
01Public Shared Sub TestStartsEnds() 02 File.Delete(Db4oFileName) 03 Dim container As IObjectContainer = Database() 04 If container IsNot Nothing Then 05 Try 06 Dim pilot As New Pilot("Test Pilot0", 100) 07 container.[Set](pilot) 08 pilot = New Pilot("Test Pilot1", 101) 09 container.[Set](pilot) 10 pilot = New Pilot("Test Pilot2", 102) 11 container.[Set](pilot) 12 13 Dim query As IQuery = container.Query() 14 query.Constrain(GetType(Pilot)) 15 query.Descend("_name").Constrain("T0").EndsWith(False).[Not]() 16 'query.Descend("_name").Constrain("Pil").StartsWith(true); 17 Dim result As IObjectSet = query.Execute() 18 ListResult(result) 19 Catch ex As Db4oException 20 Console.WriteLine("Db4o Exception: " + ex.Message) 21 Catch ex As Exception 22 Console.WriteLine("System Exception: " + ex.Message) 23 Finally 24 CloseDatabase() 25 End Try 26 End If 27 End Sub

Case Insensitive Queries

By default all string querying functions use case-sensitive comparison. startsWith and endsWith allow to switch between comparison modes using a parameter. However, if you need a case-insensitive comparison for like , equals or contains queries, it is recommended to use Native Queries as SODA does not provide such an option.

Contains 

Allows to retrieve objects constraining by included value (collection). If applied to a string will behave as "Like".

SodaExample.cs: TestContains
01public static void TestContains() 02 { 03 File.Delete(Db4oFileName); 04 IObjectContainer container = Database(); 05 if (container != null) 06 { 07 try 08 { 09 ArrayList list = new ArrayList(); 10 Pilot pilot1 = new Pilot("Test 1", 1); 11 list.Add(pilot1); 12 Pilot pilot2 = new Pilot("Test 2", 2); 13 list.Add(pilot2); 14 Team team = new Team("Ferrari", list); 15 container.Set(team); 16 17 IQuery query = container.Query(); 18 query.Constrain(typeof(Team)); 19 query.Descend("_pilots").Constrain(pilot2).Contains(); 20 IObjectSet result = query.Execute(); 21 ListResult(result); 22 } 23 catch (Db4oException ex) 24 { 25 Console.WriteLine("Db4o Exception: " + ex.Message); 26 } 27 catch (Exception ex) 28 { 29 Console.WriteLine("System Exception: " + ex.Message); 30 } 31 finally 32 { 33 CloseDatabase(); 34 } 35 } 36 }
SodaExample.vb: TestContains
01Public Shared Sub TestContains() 02 File.Delete(Db4oFileName) 03 Dim container As IObjectContainer = Database() 04 If container IsNot Nothing Then 05 Try 06 Dim list As New ArrayList() 07 Dim pilot1 As New Pilot("Test 1", 1) 08 list.Add(pilot1) 09 Dim pilot2 As New Pilot("Test 2", 2) 10 list.Add(pilot2) 11 Dim team As New Team("Ferrari", list) 12 container.[Set](team) 13 14 Dim query As IQuery = container.Query() 15 query.Constrain(GetType(Team)) 16 query.Descend("_pilots").Constrain(pilot2).Contains() 17 Dim result As IObjectSet = query.Execute() 18 ListResult(result) 19 Catch ex As Db4oException 20 Console.WriteLine("Db4o Exception: " + ex.Message) 21 Catch ex As Exception 22 Console.WriteLine("System Exception: " + ex.Message) 23 Finally 24 CloseDatabase() 25 End Try 26 End If 27 End Sub

Identity Comparison 

db4o database identity can also be used as a constraint. In this case only objects with the same database instance will be retrieved:

SodaExample.cs: TestIdentity
01public static void TestIdentity() 02 { 03 File.Delete(Db4oFileName); 04 IObjectContainer container = Database(); 05 if (container != null) 06 { 07 try 08 { 09 Pilot pilot = new Pilot("Test Pilot1", 100); 10 Car car = new Car("BMW", pilot); 11 container.Set(car); 12 // Change the name, the pilot instance stays the same 13 pilot.Name = "Test Pilot2"; 14 // create a new car 15 car = new Car("Ferrari", pilot); 16 container.Set(car); 17 18 // Simple query: 19 IQuery query1 = container.Query(); 20 query1.Constrain(typeof(Car)); 21 query1.Descend("_pilot").Constrain(pilot); 22 IObjectSet result = query1.Execute(); 23 ListResult(result); 24 25 // identity.IQuery: 26 IQuery query2 = container.Query(); 27 query2.Constrain(typeof(Car)); 28 // All cars having pilot with the same Database identity 29 // will be retrieved. As we only created Pilot object once 30 // it should mean all car objects 31 query2.Descend("_pilot").Constrain(pilot).Identity(); 32 result = query2.Execute(); 33 ListResult(result); 34 } 35 catch (Db4oException ex) 36 { 37 Console.WriteLine("Db4o Exception: " + ex.Message); 38 } 39 catch (Exception ex) 40 { 41 Console.WriteLine("System Exception: " + ex.Message); 42 } 43 finally 44 { 45 CloseDatabase(); 46 } 47 } 48 }
SodaExample.vb: TestIdentity
01Public Shared Sub TestIdentity() 02 File.Delete(Db4oFileName) 03 Dim container As IObjectContainer = Database() 04 If container IsNot Nothing Then 05 Try 06 Dim pilot As New Pilot("Test Pilot1", 100) 07 Dim car As New Car("BMW", pilot) 08 container.[Set](car) 09 ' Change the name, the pilot instance stays the same 10 pilot.Name = "Test Pilot2" 11 ' create a new car 12 car = New Car("Ferrari", pilot) 13 container.[Set](car) 14 15 ' Simple query: 16 Dim query1 As IQuery = container.Query() 17 query1.Constrain(GetType(Car)) 18 query1.Descend("_pilot").Constrain(pilot) 19 Dim result As IObjectSet = query1.Execute() 20 ListResult(result) 21 22 ' identity.IQuery: 23 Dim query2 As IQuery = container.Query() 24 query2.Constrain(GetType(Car)) 25 ' All cars having pilot with the same Database identity 26 ' will be retrieved. As we only created Pilot object once 27 ' it should mean all car objects 28 query2.Descend("_pilot").Constrain(pilot).Identity() 29 result = query2.Execute() 30 ListResult(result) 31 Catch ex As Db4oException 32 Console.WriteLine("Db4o Exception: " + ex.Message) 33 Catch ex As Exception 34 Console.WriteLine("System Exception: " + ex.Message) 35 Finally 36 CloseDatabase() 37 End Try 38 End If 39 End Sub

Sorting Results 

It is also possible to have db4o sort the results.

QueryExample.cs: RetrieveSorted
01public static void RetrieveSorted(IObjectContainer db) 02 { 03 IQuery query = db.Query(); 04 query.Constrain(typeof(Pilot)); 05 query.Descend("_name").OrderAscending(); 06 IObjectSet result = query.Execute(); 07 ListResult(result); 08 query.Descend("_name").OrderDescending(); 09 result = query.Execute(); 10 ListResult(result); 11 }
QueryExample.vb: RetrieveSorted
01Public Shared Sub RetrieveSorted(ByVal db As IObjectContainer) 02 Dim query As IQuery = db.Query() 03 query.Constrain(GetType(Pilot)) 04 query.Descend("_name").OrderAscending() 05 Dim result As IObjectSet = query.Execute() 06 ListResult(result) 07 query.Descend("_name").OrderDescending() 08 result = query.Execute() 09 ListResult(result) 10 End Sub

All these techniques can be combined arbitrarily, of course. Please try it out. There still may be cases left where the predefined query API constraints may not be sufficient - don't worry, you can always let db4o run any arbitrary code that you provide in an Evaluation. Evaluations will be discussed in a Evaluations chapter.