In the previous part, we generated LLBLGen projects using the Adapter template and included them in the test project. Now that everything is set up let’s learn how to write queries using the LLBLGen QuerySpec.

Introduction

There are three main types of queries in LLBLGen: Low-Level, QuerySpec, and LinqToLLBLGen. This post will cover the QuerySpec type (to learn more about query types in LLBLGen, read QuerySpec and LinqToLLBLGen documentation).

If we look at the documentation we can see that the QuerySpec is built on top of the Low-Level API and defines three types of queries:

• EntityQuery - a query which will result in the fetch of an entity (T is the entity class type),
• DynamicQuery - a query which will result in the fetch of a projection, and
• DynamicQuery - effectively a DynamicQuery (it derives from DynamicQuery), but has a projector defined which will return the results as elements of type T.

All of the query classes derive from the shared base class called QuerySpec. Let’s write the first query and take a closer look at the classes we’re using.

First query

A great thing about the QuerySpec is that, if you know how to write an SQL query, then it shouldn’t be a problem to reproduce that query using LLBLGen. Let’s start with a simple example - select everything from the Employee table and write down the JobTitle of the first 5 employees:

var employees = new EntityCollection<EmployeeEntity>();
using (var adapter = new DataAccessAdapter(@"Server=(localdb)\MSSQLLocalDb;Integrated Security=true;Initial Catalog=AdventureWorks2016"))
{
    var qf = new QueryFactory();
    var q = qf.Employee;

    adapter.FetchQuery(q,employees);
}

for (int i = 0; i < 5; i++)
{
    var employee = employees[i];
    Console.WriteLine($"The employee number {employee.BusinessEntityId} is working as a {employee.JobTitle}");
}

This query is used only as an example on how to fetch all the records from a single database table - you should never fetch everything only to show the first 5 records. We are going to refactor the query later on, but now let’s look at this EntityQuery piece by piece.

First, we have the EntityCollection<T> class which is used to work on or to retrieve one or more entities at the same time.

When using the Adapter template we described in the previous part, the only class we need to interact with the database is DataAccessAdapter. That single class is responsible for fetching the data from the database, changing or deleting the existing data, calling stored procedures, etc. By default, the connection string is read automatically from the .config file, but you can also pass a connection string to a constructor overload of that class as I did in the example. Because the DataAdapterClass is not thread-safe, it’s recommended to create a new instance for each query which means each query can target a different database. I added the connection string to the app.config in my ConsoleTester project, so I’m not going to pass the connection string each time I create a new instance.

To create a query, we are using the generated QueryFactory class which is a starting point of all queries in the QuerySpec. Please note that, per LLBLGen documentation, it is not recommended to cache a QueryFactory instance in a static variable. The QueryFactory is a lightweight object so you should use one instance per complete query definition.

Finally, we are passing the query to the adapter which is responsible for fetching all the data from our database.

To run the query above, you need to add the following using statements:

using AdventureWorksLLBLGen.DatabaseSpecific;
using AdventureWorksLLBLGen.HelperClasses;
using AdventureWorksLLBLGen.FactoryClasses;
using AdventureWorksLLBLGen.HelperClasses;

You also need to add a reference to SD.LLBLGen.Pro.ORMSupportClasses assembly. The easiest way is to click on the code that is still underlined in VisualStudio, press and hold Ctrl, press dot(.) and select Add Reference To ‘SD.LLBLGen.Pro.ORMSupportClasses’…

After that you only need to add one more using statement:

using SD.LLBLGen.Pro.QuerySpec.Adapter;

Now you should be able to run the query in the ConsoleTester application and get the following output:

The employee number 1 is working as a Chief Executive Officer
The employee number 2 is working as a Vice President of Engineering
The employee number 3 is working as an Engineering Manager
The employee number 4 is working as a Senior Tool Designer
The employee number 5 is working as a Design Engineer

Fetching only needed data

Most of the time you don’t need all the records from a single table, or at least you need a paged resultset. To get the same output as in the first query, we can use the Limit() method to limit the number of returned rows to a specified number. In the following examples only the query (var q = ...) line changes, everything else remains the same.

var q = qf.Employee.Limit(5);

To use the Limit(n) and other QuerySpec methods, we need to add another using statement:

using SD.LLBLGen.Pro.QuerySpec;

When you rerun the query, you should get the same output as in the first query, but with one big difference - you’re only fetching 5 rows instead of fetching all of them and just displaying the first 5.

Now let’s say you want to limit the number of fetched rows to 5, but you don’t want the first 5 employees, but the ones listed from 11-15. If that’s the case, you can combine Offset() with the Limit() method:

var q = qf.Employee.OrderBy(EmployeeFields.BusinessEntityId.Ascending()).Offset(10).Limit(5);

The output should then look like this:

The employee number 11 is working as a Senior Tool Designer
The employee number 12 is working as a Tool Designer
The employee number 13 is working as a Tool Designer
The employee number 14 is working as a Senior Design Engineer
The employee number 15 is working as a Design Engineer

Basically, we are creating a query where we specify that we want to skip the first 10 rows and then fetch the next 5. When combining the two methods, it is recommended to use the OrderBy() method to order the resultset and get the right rows. You can pass one or more sort clauses to the method separated by commas. The EmployeeFields class used in the example is a generated class that was created when we generated the source code in the first part and contains all the fields from the Employee table. Each generated table has its own class with the Fields suffix (e.g., PersonFields, EmployeeFields, etc.) which we can use to specify ordering, create joins between tables, etc.

The combination of those two methods can also be used for paginating a resultset, but that can be done easier by using the Page() method. To reproduce the previous query, but now using the page method, let’s assume we are displaying 5 items per page and we want to display the resultset for the third page. We would again use the OrderBy() method to ensure the right rows are fetched and just pass the pageNumber and pageSize parameters. The query would then look like this:

var q = qf.Employee.OrderBy(EmployeeFields.BusinessEntityId.Ascending()).Page(3,5);

Looking at the examples so far and the Employee table, you can notice that we only have some basic information about the employee, such as order number, job title, and gender, but we don’t have the information about the actual person. To get more information we can use the Person table and filter all the employees by their first name, so let’s do that next.

Filtering, joining, and prefetching

To specify a filter on the resultset we need to use the Where() method, which has sibling methods called AndWhere() and OrWhere() that you can use to append additional predicates. Now that we know this let’s write a query that’s going to fetch the first 5 male employees whose first name starts with the letter “A.”

Because we want to filter the resultset using the property from another table, we have to join those two tables together. All joins in LLBLGen have to be specified inside a From() method, which is used to specify joins between tables and in dynamic queries where the source is not clear. In many queries, that method is not necessary, which is why we haven’t used it so far.

To join the tables together, we have to use the QueryTarget construct. This construct has to be used for EntityQuery<T> instances, but not for dynamic queries. We are going to see how to create joins in dynamic queries in the final part of this blog post series.

There are two ways we can get the same result:

var q = qf.Employee.
        From(QueryTarget.
        InnerJoin(EmployeeEntity.Relations.PersonEntityUsingBusinessEntityId)).
        Where(PersonFields.FirstName.StartsWith("A")).
        AndWhere(EmployeeFields.Gender == "M").
        OrderBy(EmployeeFields.BusinessEntityId.Ascending()).
        Limit(5);

In this example, we are using the EntityRelation object to join our two tables. This way you don’t have to know all the details about primary and foreign key constraints to create a join. The generated entity class already knows about all the relations.

We can rewrite the query and get the same result by specifying the primary and foreign key constraints:

var q = qf.Employee.
        From(QueryTarget.
        InnerJoin(qf.Person).On(PersonFields.BusinessEntityId == EmployeeFields.BusinessEntityId)).
        Where(PersonFields.FirstName.StartsWith("A")).
        AndWhere(EmployeeFields.Gender == "M").
        OrderBy(EmployeeFields.BusinessEntityId.Ascending()).
        Limit(5);

As you can see, this time we had to specify the name of the table we want to join, in our case the Person table, and then we had to specify the predicate expression as the ON clause. To join the tables this way you need to know the primary and foreign key constraints. If you specify the wrong fields in a join, you’ll get a runtime exception.

If you execute one of the two queries above you should get the same result:

The employee number 33 is working as a Production Technician - WC60
The employee number 47 is working as a Production Supervisor - WC10
The employee number 149 is working as a Production Technician - WC30
The employee number 163 is working as a Production Technician - WC45
The employee number 164 is working as a Production Technician - WC45

This time we are no longer getting the employee numbers from 1-5. On the other hand, we are only outputting the employee number and job title so we cannot be sure that we filtered the resultset correctly. For this, we have to change the console output to something like this:

foreach (var employee in employees)
{
    Console.WriteLine($"The employee {employee.Person.FirstName}{employee.Person.LastName}({employee.Gender}) is working as a {employee.JobTitle}");
}

If you run this query, you’ll get a NullReferenceException. The query worked before this change, so why did we get this exception now? Many people that are starting to use LLBLGen make a common mistake and presume that, because they’ve created a join between tables, the joined entities are prefetched. This is just not correct. The join was used to get the filtered employees, but the Person entities were not fetched, so we cannot use its properties. This can be fixed by adding the WithPath() method that’s going to prefetch the specified entities.

var q = qf.Employee.
        From(QueryTarget.
        InnerJoin(qf.Person).On(PersonFields.BusinessEntityId == EmployeeFields.BusinessEntityId)).
        Where(PersonFields.FirstName.StartsWith("A")).
        AndWhere(EmployeeFields.Gender == "M").
        OrderBy(EmployeeFields.BusinessEntityId.Ascending()).
        Limit(5).
        WithPath(EmployeeEntity.PrefetchPathPerson);

When the prefetch is specified we can then output the result and confirm that all employees in the filtered resultset are males whose first name starts with the letter “A”:

The employee AnnikStahl(M) is working as a Production Technician - WC60
The employee AndrewHill(M) is working as a Production Supervisor - WC10
The employee AndyRuth(M) is working as a Production Technician - WC30
The employee AlexNayberg(M) is working as a Production Technician - WC45
The employee AndrewCencini(M) is working as a Production Technician - WC45

Another way of getting the same result would be to create our own class and then write a dynamic query to select only the properties we need. We are going to do that in the third part.

In our example, we created an inner join between two tables, but the process is the same if you want to use a different join or join more than two tables. To learn more about joins click here.

There is a lot more to the QuerySpec than covered in this short tutorial. You can find more information about all the topics we covered here (and more) in the QuerySpec documentation linked in the introduction.

Now that we know some basics on how to fetch the entities using EntityQuery<T>, we can move on and learn how to insert, update, and delete rows.

Creating a new entity

To insert a new PersonEntity, we first need to insert a BusinessEntity entity to obtain a BusinessEntityId. We don’t have to specify the value for the BusinessEntityId property because that’s an identity column which is incremented when the entity is saved.

var newBusinessEntity = new BusinessEntityEntity
{
    ModifiedDate = DateTime.UtcNow,
    Rowguid = Guid.NewGuid()
};

using (var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(newBusinessEntity);
}   

var newPerson = new PersonEntity
{
    BusinessEntityId = newBusinessEntity.BusinessEntityId,
    PersonType = "EM",
    NameStyle = false,
    Title = "Mr",
    FirstName = "John",
    LastName = "Doe",
    EmailPromotion = 1,
    Rowguid = Guid.NewGuid(),
    ModifiedDate = DateTime.UtcNow
};

using (var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(newPerson);
}
Console.WriteLine($"{newPerson.Title}. {newPerson.FirstName} {newPerson.LastName} has just been inserted!");

To insert both of the entities we are using the SaveEntity(entity) method which saves the data directly to the database. One thing to note here is that, if you save your entity this way, it will be marked as “Out of sync” and the query above will raise the ORMEntityOutOfSyncException when you try to output the properties of the newly inserted PersonEntity. If you want to use the entity for any further processing, you have to refetch it before reading one of the properties. The SaveEntity overload accepts a boolean for refetch where you can pass true as a second parameter, and the entity will be refetched right after saving:

adapter.SaveEntity(newPerson, true);

Maybe you noticed that we didn’t have to pass the parameter for refetch when we first saved the BusinessEntity and used its BusinessEntityId for the PersonEntity insert. That’s because the LLBLGen code is aware of the identity columns and the value is available after saving without specifying the boolean for refetch. However, if you try to use any other property instead of the BusinessEntityId, you will get the out of sync exception.

Updating an existing entity

There are two ways we can update an existing entity; by fetching the entity into memory first and saving the entity after changes or by updating the entity directly in the persistent storage. Let’s try both ways:

var person = new PersonEntity(20785);
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntity(person);
    person.FirstName = "Jane";
    person.Title = "Ms";
    person.ModifiedDate = DateTime.UtcNow;
    adapter.SaveEntity(person);
}

In this query, we are first fetching the entity by passing the primary key value to our generated PersonEntity class. There are many ways how you can instantiate an existing entity instance and some of them you should already know if you mastered the fetching queries. If needed, you can find more information here.

After changing the fields, we are calling the SaveEntity method again. That method is looking if the passed entity is new or not, and if the entity already exists, it generates an update query to alter the data. Otherwise, it will generate an insert query for inserting a new instance into the database.

Now let’s do the same thing - but without fetching the entity first:

RelationPredicateBucket filterBucket = new RelationPredicateBucket(PersonFields.BusinessEntityId == 20785);
PersonEntity updatePerson = new PersonEntity()
{
    FirstName = "Jane",
    Title = "Ms",
    ModifiedDate = DateTime.UtcNow
};

using (var adapter = new DataAccessAdapter())
{
    adapter.UpdateEntitiesDirectly(updatePerson, filterBucket);
}

To use the RelationPredicateBucket class, we need to add the following using statement:

using SD.LLBLGen.Pro.ORMSupportClasses;

Using the UpdateEntitiesDirectly method, we are updating the entity directly in the persistent storage. To achieve that, we’re creating a filter bucket where we specify that we want to update the entity with the specific primary key. After that, we are changing the field values, calling the UpdateEntitiesDirectly method, and passing the updated entity and filter bucket. It’s possible to update one or more entities this way, and the method returns the number of updated entities.

Deleting an entity

Now that we know how to create and update an entity, it’s time to learn how to delete one. Just like with updating, we can delete an entity by first fetching the one we want to delete into memory, or we can delete that entity directly in the persistent storage.

To delete the entity we created and updated in the previous steps by first fetching it into memory, we would write the following query:

using (var adapter = new DataAccessAdapter())
{
    var personToDelete = new PersonEntity(20785);
    adapter.FetchEntity(personToDelete);
    adapter.DeleteEntity(personToDelete);
}

If you want to delete the entity without fetching it first, you have two other options.

The first option is the one we used for updating, only this time we have to use the DeleteEntitiesDirectly method (instead of UpdateEntitiesDirectly). Again, we have to create a filter bucket to specify which entity we want to delete, pass the entity type (or a string name) along with the predicate bucket we previously created:

using (var adapter = new DataAccessAdapter())
{
    RelationPredicateBucket filterBucket = new RelationPredicateBucket(PersonFields.BusinessEntityId == 20785);
    adapter.DeleteEntitiesDirectly(typeof(PersonEntity), filterBucket);
}

The other option is to create a new entity object and set the primary key value before calling the DeleteEntity method:

using (var adapter = new DataAccessAdapter())
{
    var personToDelete = new PersonEntity(20785);
    adapter.DeleteEntity(personToDelete);
}

As you can see, the only difference between this and our first delete query is that we are missing the FetchQuery method to avoid fetching the entity into memory. Please note that deletes are never recursive. If you want to delete an entity that has a foreign key, make sure that the foreign key constraint has been set to CASCADE DELETE in the database, or an exception will be thrown.

We have now covered some of the basic CRUD queries using the LLBLGen Adapter and QuerySpec. Once you’re done with the sample queries, you can use the test database and practice creating different joins, adding more filter predicates, or test the prefetch paths with different options. You can also check the result after insert, update, and delete queries to confirm that everything was executed correctly. If you get stuck, you can always take a look at the official documentation.

The source code with the added CRUD queries is available here.