How do I ... ?

This section enlists a set of answers on common How do I ... ? questions related to the generated code and the functionality LLBLGen Pro provides and a list of all the example code inside the various sections in this documentation. The How do I...? examples are given in C# and VB.NET and if required in both template group semantics, and if not, SelfServicing is used. All examples use the Northwind database schema, unless stated otherwise.

The documentation itself contains numerous examples as well. Just type in the subject you want to have an example on in the search box on the top left of the documentation and browse to the topics matching your query to find examples how to use that particular subject.

How do I ... ?

How do I read all entities of a given type into a collection?

// Low-level API
var allCustomers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(allCustomers, null);
}

// Linq
EntityCollection<CustomerEntity> allCustomers;
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    allCustomers = metaData.Customer.Execute<EntityCollection<CustomerEntity>>();
}

// QuerySpec
var qf = new QueryFactory();
EntityCollection<CustomerEntity> allCustomers;
using(var adapter = new DataAccessAdapter())
{
    allCustomers = qf.Customer.FetchQuery();
}

// Low-level API
var allCustomers = new CustomerCollection();
allCustomers.GetMulti(null);

// Linq
var metaData = new LinqMetaData();
CustomerCollection allCustomers = metaData.Customer.Execute<CustomerCollection>();

// QuerySpec
var qf = new QueryFactory();
var allCustomers = new CustomerCollection();
allCustomers.GetMulti(qf.Customer);

How do I insert a new entity?

var newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
using(var adapter = new DataAccessAdapter())
{
    // if the newProduct object is used after this call, specify True
    // for refetchAfterSave, a parameter of SaveEntity()
    adapter.SaveEntity(newProduct);
}

// C#
var newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
newProduct.Save();

How do I delete an existing entity?

Method 1: entity is already loaded into memory.

Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints)

// fetch the entity.
var customer = new CustomerEntity("FISSA");
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchEntity(customer);
    // ... some other code
    adapter.DeleteEntity(customer);
}

// fetch the entity.
var customer = new CustomerEntity("FISSA");
// ... some other code
customer.Delete();

Method 2: use a new entity object to directly delete an entity from the database.

Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints.

var customer = new CustomerEntity("FISSA");
using(var adapter = new DataAccessAdapter())
{
    adapter.DeleteEntity(customer);
}

var customer = new CustomerEntity();
customer.CustomerID = "FISSA";
customer.Delete();

Method 3: use a predicate expression to delete the entity directly.

var bucket = new RelationPredicateBucket(CustomerFields.CustomerID.Equal("FISSA"));
using(var adapter = new DataAccessAdapter())
{
    adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);
}

var customers = new CustomerCollection();
customers.DeleteMulti(CustomerFields.CustomerID.Equal("FISSA"));

How do I pass different connection strings at runtime using Adapter?

All generated code (SelfServicing or Adapter) by default reads the connection string from the *.config file of the application referencing the generated code. You can pass a different connection string than the one generated into the app.config file to the DataAccessAdapter's class constructor. That instance will then use the connection string passed in instead of the connection string defined in the *.config file.

using(var adapter = new DataAccessAdapter(
                "data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret"))
{
    // your code here.
}

How do I test if an entity is actually loaded from the database?

var customer = new CustomerEntity("CHOPS");
bool loadedCorrectly = false;
using(var adapter = new DataAccessAdapter())
{
    loadedCorrectly = adapter.FetchEntity(customer);
    // or
    loadedCorrectly = (customer.Fields.State == EntityState.Fetched);
}

var customer = new CustomerEntity("CHOPS");
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);

How can I determine at runtime which fields are the primary key fields of an entity?

References to these fields are stored in the IList returned by the property PrimaryKeyFields of an entity object. You can also enumerate the field objects in the collection returned by the Fields property of an entity object and check the IsPrimaryKey property.

How do I insert a NULL value for a field in a new entity?

When you create an entity object, all fields get a default value. When you save the entity (using the SelfServicing method or the Adapter method), all changed fields are saved, all non-changed fields are skipped.

This means, that if you for example save a new CustomerEntity and you don't set the ContactTitle property to a value, it will not be included in the INSERT query, which will result in a NULL value for ContactTitle in the database table. If a default constraint would have been defined for that field, the RDBMS would have inserted that value instead of NULL.

How do I prevent that new entities are added to an entity collection in a grid?

The Adapter's EntityCollection<T> class and the SelfServicing entity collection classes are bound to a grid using an EntityView2 or EntityView object, by default the view returned from the entity collection's DefaultView property.

These EntityView(2) objects implement the IBindingList.AllowNew property. Setting this property to false/False will assure that there will be no 'new' line in the grid bound to the collection object and the grid is not able to add new entities via the IBindingList interface.

How do I set a time-out setting of 1 minute using SelfServicing?

You can set a timeout (in seconds) for all commands executed using a global setting in the generated CommonDaoBase class. (HelperClasses namespace). It doesn't hurt to use a timeout larger than what you actually need, so setting it to 1 minute to make sure a long running query will succeed is not degrading performance in your application.

If a connection to the database is not available, it will however then take longer to timeout. ADO.NET's default is 15 seconds, the default in the templates is set to 30 seconds.

/// set it to 60 seconds
CommonDaoBase.CommandTimeOut = 60;

How do I update a series of entities directly in the database?

The example below will update all EmployeeID fields in all orders with the value of 5 where the current EmployeeID field is 2.

var newValues = new OrderEntity();
newValues.EmployeeID = 5;
var filter = new RelationPredicateBucket(OrderFields.EmployeeID.Equal(2));
using(var adapter = new DataAccessAdapter())
{
    adapter.UpdateEntitiesDirectly(newValues, filter);
}

var newValues = new OrderEntity();
newValues.EmployeeID = 5;
var updater = new OrderCollection();
updater.UpdateMulti(newValues, (OrderFields.EmployeeID == 2));

How do I filter on fields in another entity?

Below is an example which retrieves all Customer entities which have bought a given product, in this case the product with ProductID 2

// Low-level API
var filter = new RelationPredicateBucket(ProductFields.ProductID.Equal(2));
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID); 
filter.PredicateExpression.Add(ProductFields.ProductID == 2);
var customers = new EntityCollection<CustomerEntity>();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, filter);
}

// Linq
EntityCollection<CustomerEntity> customers;
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
            join o in metaData.Order on c.CustomerId equals o.CustomerId
            join od in metaData.OrderDetails on o.OrderId equals od.OrderId
            join p in metaData.Product on od.ProductId equals p.ProductId
            where p.ProductId == 2
            select c;
    customers = q.Execute<EntityCollection<CustomerEntity>>();
}

// QuerySpec
EntityCollection<CustomerEntity> customers;
var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget
                    .InnerJoin(qf.Order).On(CustomerFields.CustomerId.Equal(OrderFields.CustomerId))
                    .InnerJoin(qf.OrderDetails).On(OrderFields.OrderId.Equal(OrderDetailsFields.OrderId))
                    .InnerJoin(qf.Product).On(OrderDetailsFields.ProductId.Equal(ProductFields.ProductId)))
            .Where(ProductFields.ProductId.Equal(2));
using(var adapter = new DataAccessAdapter())
{
    customers = q.FetchQuery<EntityCollection<CustomerEntity>>();
}

// Low-level API
var relations = new RelationCollection();
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID); 
var customers = new CustomerCollection();
customers.GetMulti(, relations);

// Linq
var metaData = new LinqMetaData();
var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId
        join od in metaData.OrderDetails on o.OrderId equals od.OrderId
        join p in metaData.Product on od.ProductId equals p.ProductId
        where p.ProductId == 2
        select c;
var customers = q.Execute<CustomerCollection>();

// QuerySpec
var qf = new QueryFactory();
var q = qf.Customer
            .From(QueryTarget
                    .InnerJoin(qf.Order).On(CustomerFields.CustomerId.Equal(OrderFields.CustomerId))
                    .InnerJoin(qf.OrderDetails).On(OrderFields.OrderId.Equal(OrderDetailsFields.OrderId))
                    .InnerJoin(qf.Product).On(OrderDetailsFields.ProductId.Equal(ProductFields.ProductId)))
            .Where(ProductFields.ProductId.Equal(2));
var customers = new CustomerCollection();
customers.GetMulti(q);

How do I handle exceptions which occur during save/delete/fetch actions?

Each exception caught during the execution of the SQL the Dynamic Query Engines generate is wrapped in a ORMQueryExecutionException exception object. You can thus create an extra catch clause which solely catches this exception.

The ORMQueryExecutionException exception class embeds the caught exception as the inner exception, the complete SQL executed in text and the parameters collection passed to the query. This ensures you have all the information needed to fully debug the situation.

How do I implement concurrency control?

Concurrency control is something that can be implemented in various ways. Because there are numerous ways to implement concurrency control (abstract concurrency control using functionality locking, low level concurrency schemes with optimistic locking or pessimistic locking, all fields filters, timestamp filters etc.), the generated code offers you the tools to produce the concurrency scheme you want.

To implement low level concurrency control like optimistic locking, predicates are used to limit the scope of a query executed. These predicates are added to the query being executed. To produce these predicates automatically, it's wise to implement IConcurrencyPredicateFactory for the class(es) you want concurrency control for. See the sections about concurrency control (Selfservicing or Adapter) for more details about this.

How do I create a m:n relation between two entity objects?

The generated code will not save intermediate table entries in an m:n relation. Consider the two entities Department (DepartmentID (PK, int, identity), Description) and Employee (EmployeeID (PK, int, identity), Name). These two entities have an m:n relation, and this results in the third entity DepartmentEmployees (DepartmentID (PK), EmployeeID (PK)).

To relate a new employee with an existing department follow the example below. It will add a new employee to the existing department with ID 1. You can of course also set the DepartmentID of the DepartmentEmployeesEntity by hand, avoiding the necessity to fetch the DepartmentEntity first.

using(var adapter = new DataAccessAdapter())
{
    var department = new DepartmentEntity(1);
    adapter.FetchEntity(department);
    var newEmployee = new EmployeeEntity();
    newEmployee.Name = "John Doe";
    var departmentEmployees = new DepartmentEmployeesEntity();
    departmentEmployees.Department = department;
    departmentEmployees.Employee = employee;
    // save recursively
    adapter.SaveEntity(departmentEmployees);
}

var department = new DepartmentEntity(1);
var newEmployee = new EmployeeEntity();
newEmployee.Name = "John Doe";
var departmentEmployees = new DepartmentEmployeesEntity();
departmentEmployees.Department = department;
departmentEmployees.Employee = employee;
// save recursively
departmentEmployees.Save(true);

How do I fetch a typed view or typed list in a transaction using SelfServicing?

It can be necessary to fetch a typed view or typed list during a transaction. Typed lists and typed views are not addable to a Transaction object, like entities and entity collection classes are. Typed lists and typed views have a Fill() method overload which accepts a Transaction object.

Use that overload to pass an existing transaction object and the Fill() method will use the connection of the passed in transaction to retrieve the data requested instead of using a new connection. This avoids deadlocks because the writers used in the transaction block a read (fetch) by the Fill() method.

How do I write a filter which does WHERE field = 3 ?

For Linq, use 

...
where x.Fieldname==3

For QuerySpec and the low-level API, use: 

IPredicate filter = YourEntityFields.Field.Equal(3);

How do I write a filter which does WHERE fielda = fieldb ?

For Linq, use 

...
where x.Fieldname==y.FieldName

For QuerySpec and the low-level API, use: 

IPredicate filter = YourEntityFields.Field.Equal(OtherEntityFields.Field);

How do I write a filter which does WHERE field LIKE '%foo%' ?

For Linq, use 

...
where x.Fieldname.Contains("foo")

For QuerySpec and the low-level API, use: 

IPredicate filter = YourEntityFields.Field.Contains("foo");

How do I write a filter which does WHERE field BETWEEN 1 AND 10 ?

For Linq, use:

...
where x.Field >= 1 && x.Field<=10

For QuerySpec and the low-level API, use:

IPredicate filter = YourEntityFields.Field.Between(1, 10);

How do I write a filter which does WHERE field IN (1, 2, 5) ?

For Linq, use

var values = new List<int>() {1, 2, 5};
...
where values.Contains(x.Field)

For QuerySpec and the low-level API, use:

var values = new List<int>() {1, 2, 5};
...
IPredicate filter = YourEntityFields.Field.In(values);

// or, without defining 'values' first. 
IPredicate filter = YourEntityFields.Field.In(1, 2, 5);

How do I write a filter which does WHERE field IN (SELECT fieldb FROM foo) ?

For Linq, use

...
where metaData.Foo.Select(f=>f.Fieldb).Contains(x.Field)

For QuerySpec, use

var qf = new QueryFactory();
...
IPredicate filter = YourEntityFields.In(qf.Foo.Select(FooFields.Fieldb));

For the low-level API, use

IPredicate filter = new FieldCompareSetPredicate(
                YourEntityFields.Field, null, FooFields.Fieldb, null, 
                SetOperator.In, null));

IPredicate filter = new FieldCompareSetPredicate(
                YourEntityFields.Field, FooFields.Fieldb, SetOperator.In, null);

How do I write a filter which does WHERE field IS NULL ?

For Linq, use

...
where x.Field == null

For QuerySpec and the low-level API, use:

IPredicate filter = YourEntityFields.Field.IsNull();

How do I use a group by clause in a dynamic list ?

The following query formulates a dynamic list with all managers and the number of employees which are reporting to them. The query is ran on Northwind, which contains a self-referencing Employees table.

// Low-level API
var fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
var bucket = new RelationPredicateBucket();
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
var groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

var tlist = new DataTable();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, tlist, bucket, 0, null, true, groupByClause);
}

// Linq
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = from e in metaData.Employee
            join m in metaData.Employee on e.ReportsTo equals m.EmployeeId
            group m by new {m.FirstName, m.LastName} into g
            select new { g.Key, NumberOfEmployees = g.Count() };
    var results = q.ToList();
}

// QuerySpec
var qf= new QueryFactory();
var q = qf.Create()
            .Select(EmployeeFields.FirstName.Source("M"),
                    EmployeeFields.LastName.Source("M"),
                    Functions.CountRow().As("NumberOfEmployees"))
            .From(qf.Employee.As("E")
                    .InnerJoin(qf.Employee.As("M"))
                        .On(EmployeeFields.ReportsTo.Source("E")
                                    .Equal(EmployeeFields.EmployeeId.Source("M"))))
            .GroupBy(EmployeeFields.FirstName.Source("M"),
                EmployeeFields.LastName.Source("M"));
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
}

var fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
var relations = new RelationCollection();
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
var groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

var dao = new TypedListDAO();
var tlist = new DataTable();
dao.GetMultiAsDataTable(fields, tlist, 0, null, null, relations, true, groupByClause, null, 0, 0);

// Linq
var metaData = new LinqMetaData();
var q = from e in metaData.Employee
        join m in metaData.Employee on e.ReportsTo equals m.EmployeeId
        group m by new {m.FirstName, m.LastName} into g
        select new { g.Key, NumberOfEmployees = g.Count() };
var results = q.ToList();

// QuerySpec
var qf= new QueryFactory();
var q = qf.Create()
            .Select(EmployeeFields.FirstName.Source("M"),
                    EmployeeFields.LastName.Source("M"),
                    Functions.CountRow().As("NumberOfEmployees"))
            .From(qf.Employee.As("E")
                    .InnerJoin(qf.Employee.As("M"))
                        .On(EmployeeFields.ReportsTo.Source("E")
                                    .Equal(EmployeeFields.EmployeeId.Source("M"))))
            .GroupBy(EmployeeFields.FirstName.Source("M"),
                EmployeeFields.LastName.Source("M"));
var results = new TypedListDAO().FetchQuery(q);

How do I construct a multi-node prefetch path and specify sorting for one node using the low-level API ?

var path = new PrefetchPath2(EntityType.EmployeeEntity);
var sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate.Descending());
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders);

var path = new PrefetchPath(EntityType.EmployeeEntity);
var sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate.Descending());
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter)
            .SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers)
            .SubPath.Add(CustomerEntity.PrefetchPathOrders);

How do I add an entity A to an entity B's collection of A's if A and B have an m:n relation ?

Say you want to add an Employee to a Department and Employee and Department have an m:n relation. This means that there is an intermediate entity DepartmentEmployees which defines the m:n relation with a foreign key to both Employee and Department.

Employee has a collection Departments which results in all Department entities the Employee works for and Department has a collection Employees which results in all Employees working for the Department. In LLBLGen Pro, m:n relations are read-only, therefore, to add an Employee to a Department, you've to use the following steps.

The example uses department and employee which are filled in DepartmentEntity and EmployeeEntity instances. You can also use new entities, this doesn't matter.

DepartmentEmployees newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
// to save the relation in the database.
using(var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(employee);
}

var newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) 
// to save the relation in the database.
employee.Save(true);

Keep in mind that the m:n relation is then saved in the database, but the situation in memory isn't updated, i.e.: employee.Departments doesn't contain 'department'. This is by design, as the m:n relation is based on a 3rd entity, which can be altered by any thread in the system, making the m:n relation, which is a result of the values of the intermediate entity, a 'view' of a situation at a given point in time.