Using dynamic lists

LLBLGen Pro offers you to create lists in code, without the necessity of the designer. This sometimes can become handy if you just want to pull a small list of data from the database without having to re-generate the code again. This section is largely meant for people who have to work with code bases which contain dynamic list creating code: with Linq and QuerySpec, it's easier to create dynamic lists and projection queries than using the more verbose ResultsetFields method described below.

The example contains alternative queries using QuerySpec and Linq to show how to fetch the same resultset using these methodologies. It's recommended you use QuerySpec instead of the ResultsetFields methods in new code.

In the example below, the 'low-level API' variant is the original dynamic list code, using ResultsetFields. This methodology is still fully supported, just keep in mind that QuerySpec and Linq offer a less verbose alternative.

The following paragraph discusses briefly how to create a dynamic list in code. Dynamic lists are using the same building blocks as Typed View and Typed List classes use and can be used with normal filters and other constructs like group by.

Creating dynamic lists

Typed lists are great, however sometimes you need a small list of data, build from one or more entities and use it in a read-only way, and you don't really need the typed functionality coming with a typed list. After all, a typed list requires you to go into the designer, create the list and re-generate the code. Dynamic lists are based on entity fields, using the similar code as TypedList classes use internally. These lists are loaded into DataTable objects.

The following example shows you how to create such a dynamic list. The example uses aggregates and a GroupByCollection to read a custom resultset into a DataTable, fully build with entity fields. The example joins the same entity (EmployeeEntity) twice, hence it uses aliases to make sure the right entity is referenced by the various elements.

Low-level API, C#QuerySpec, C#Linq, C#

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]);
DataTable dynamicList = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);
}

var qf = new QueryFactory();
var q = qf.Create()
            .Select(() => new
            {
                FirstName = EmployeeFields.FirstName.Source("Manager").ToValue<string>(),
                LastName = EmployeeFields.LastName.Source("Manager").ToValue<string>(),
                NumberOfEmployees = Functions.CountRow().As("NumberOfEmployees").ToValue<int>()
            })
            .From(qf.Employee.InnerJoin(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager"))
            .GroupBy(EmployeeFields.FirstName.Source("Manager"), EmployeeFields.LastName.Source("Manager"));
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
    // process 'results' here, as it's a List<anonymousType>
}

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
            {
                FirstName = g.Key.FirstName,
                LastName = g.Key.LastName,
                NumberOfEmployees = g.Count()
            };
    var results = q.ToList();
}

This list retrieves all managers and the number of employees they manage. Let's walk through the example to make it more understandable. It first creates a list of fields which will form the list. ResultsetFields is a class defined in the HelperClasses namespace in your generated code (database generic project) and which is a class derived from EntityFields2, the container for EntityField2 objects which is also located in every entity: the Fields property.

The three lines following the declaration of the fields parameter define the three fields in detail. First, it specifies an entity field, to signal which field we want on that position of the resultset fields, then the index of the field in the ResultsetFields object, then the alias for the field in the resultset and optionally (but we join Employee twice so we have to alias) the alias for the entity this field belongs to.

The third field is actually the same as the second, Employee.LastName, however has an aggregate function applied to it. LastName is not a numeric field, but the type of the field is not important when an aggregate function is applied, as the field defines a column in the dynamic list and is used as a parameter for the aggregate function; the aggregate function itself, or better the value it produces, is the actual value of the column and the type is determined at runtime. As a DataColumn object can contain any value, this works as planned.

As we have to join Employee twice, we have to define a relation collection and add the relation required for the join. The entities in the relation are properly aliased as "Employee" and "Manager" so the generated code knows from which table the fields should be retrieved. As we're going to group by, we define the group by collection and add the fields which participate in the group by in the order in which we want to group. We don't add the third field, as it is an aggregated field which is using the grouped data.

After that, the objects are setup to retrieve the data. We use the adapter's FetchTypedList method, as that method is capable of fetching a set of data in a DataTable object. We could have specified a filter as well, additional relations for the filter, and even paging parameters.

This way of creating lists of data is very flexible and can be easily extended with expressions for complex resultsets, for example for usage in reports.