Using the TypedView classes

LLBLGen Pro supports read-only lists based on a database view/table, in the form of TypedViews. This section discusses how to generate typed views as either a Typed Datatable or a POCO class and how to fetch the typed views at runtime.

Choosing the Typed View class type

Which class is generated for a Typed View is determined by its OutputType setting. This setting is found on the Typed View's Code gen. Info tab in the designer and its default value is obtained from the TypedViewOutputTypeDefault setting located in the LLBLGen Pro framework section of the Project Settings. There are three values to choose from:

  • PocoWithQuerySpecQuery (default)
  • PocoWithLinqQuery
  • TypedDataTable

The default is PocoWithQuerySpecQuery, which will result in a POCO class called typedViewNameRow and a QuerySpec query to fetch the typed view. The value TypedDataTable results in a class like in versions v3.x and earlier: a DataTable-derived class which is retrievable using the low-level api. PocoWithLinqQuery results in a POCO class called typedViewNameRow, and a Linq query to fetch the typed view. 

It's recommended to use the TypedViewOutputTypeDefault setting to set the output type of your typed views in one go. You can also set the OutputType setting on individual typed views in bulk using the Bulk Output Setting Values Manipulator in the designer.

Differences between POCO classes and DataTable derived classes

The main difference is that a POCO class is a plain class with only properties, while a typed datatable class derives from DataTable and thus can use the DataView class to create custom views on the DataTable. Additionally, POCO typed views are not fetchable through the low-level API, only through Linq or QuerySpec.

Instantiating and using a Typed View

For each of the three OutputType values, examples are given for a typed view mapped onto a table/view, stored procedure resultset and table-valued function.

Instantiating a Typed View mapped onto a table / view

For the examples below, we'll use the view 'Invoices' from the Northwind database, and use the TypedView 'Invoices' mapped onto that view.

OutputType: TypedDataTable

To create an instance of the 'Invoices' typed view and fill it with all the data in the view, the following code is sufficient:

var invoices = new InvoicesTypedView();
invoices.Fill();

In the above example, the rows will be added as they are received from the database provider; no sorting nor filtering will be applied. Furthermore, all rows in the view are read, which is probably not what you want. Let's filter on the rows, so the TypedView will only contain those rows with an OrderID larger than 11000. We also specify not to filter out duplicate rows. 

var invoices = new InvoicesTypedView();
var invoicesFilter = new PredicateExpression(InvoicesFields.OrderID.GreaterThan(11000));
invoices.Fill(0, null, true, invoicesFilter);

The overloaded Fill() version that accepts a filter, also accepts other parameters:

  • maxNumberOfItemsToReturn, which is used to limit the number of rows returned. When this parameter is set to 0, it is ignored (all rows are returned)
  • sortClauses, which is a collection of SortClause objects and used to sort the rows in the table before they're added to the Typed View object. When this parameter is set to null, no sorting is performed.
  • allowDuplicates. Most views don't contain duplicate rows, but if they do, you can filter them out using this setting.

Specifying a filter will narrow down the number of rows to the ones matching the filter. The filter can be as complex as you want. See for filtering information and how to set up sorting clauses Getting started with filtering and Sorting.

OutputType: PocoWithLinqQuery

For Linq, similar to entities mapped onto tables/views, a property is generated for each typed view which returns a DataSource Object, named after the typed view. The DataSource object is an IQueryable<TypedViewNameRow>. Usage is the same as with entities in Linq: Below the Invoices typed view is fetched and filtered on OrderId

var q = from x in metaData.Invoices
        where x.OrderId == 10254
        select x;

OutputType: PocoWithQuerySpecQuery

For QuerySpec, a property is created in the QueryFactory class which creates a DynamicQuery<T> with a typed Select which fills the Typed View row with values, without a From clause. The property is named after the typed view, similar to the entity query properties are named after the entity name: TypedViewName.

It is possible to join with the typed view query, using normal constructs:

var qf = new QueryFactory();
var q = qf.Invoices
            .From(QueryTarget.InnerJoin(qf.Order)
                    .On(InvoicesFields.OrderId.Equal(OrderFields.OrderId)))
            .Where(OrderFields.EmployeeId == 2);
var results = new TypedListDAO().FetchQuery(q);

Here the typed view Invoices is joined with a normal entity 'Order'. The join uses QueryTarget to append the join to the target of the generated query of the typed view.

Instantiating a TypedView mapped onto a Stored Procedure Resultset

When you map a TypedView onto a resultset of a stored procedure, the fetch action of the TypedView is slightly different. As stored procedures can't be included inside a query, they're separated and are stored in the DB specific part of a project. This means fetching the typed view is located in that part as well, in the RetrievalProcedures, equal to the fetch of the datatable based typed view.

OutputType: TypedDataTable

Instead of a Fill() method as described above, the generated TypedView has a different Fill() method which accepts values for the parameters of the stored procedure to call. In the following example, a TypedView is mapped onto the resultset of a stored procedure which returns the Customers for a given country. No transaction is in progress, so null / Nothing is specified.

var customersTv = new CustomersOnCountryTypedView();
customersTv.Fill(null, "USA");

OutputType: PocoWithLinqQuery

Not supported.

OutputType: PocoWithQuerySpecQuery

For QuerySpec, retrieving a poco typed view mapped onto a stored procedure resultset comes down to using two parts:

  1. Obtain the projection lamdba from the QueryFactory. This projection is produced by the method TypedViewNameTypedViewProjection
  2. Pass it to the method in RetrievalProcedures which fetches the actual typed view.

This looks like the following:

var results = RetrievalProcedures.FetchCustomersOnCountryQsTypedView(
            new QueryFactory().GetCustomersOnCountryTypedViewProjection(), "USA");

var results = await RetrievalProcedures.FetchCustomersOnCountryQsTypedViewAsync(
            new QueryFactory().GetCustomersOnCountryTypedViewProjection(), "USA", 
            CancellationToken.None);

This is a step away from the 'Fill' method used on DataTable based typed view. The projection returned from the Queryfactory method is a DynamicQuery<T> but as there are no mappings defined for that projection, it's not usable as a query.

Alternatively, you can use FetchQueryFromSource to do the same:

var results = new TypedListDAO().FetchQueryFromSource(
                new QueryFactory().GetCustomersOnCountryTypedViewProjection(), 
                RetrievalProcedures.GetCustomersOnCountryCallAsQuery("USA"));
}

var results = await new TypedListDAO().FetchQueryFromSourceAsync(
                new QueryFactory().GetCustomersOnCountryTypedViewProjection(), 
                RetrievalProcedures.GetCustomersOnCountryCallAsQuery("USA"),
                CancellationToken.None);
}

Async calls of a stored procedure with an output parameter

If the stored procedure to call has besides a resultset also an output parameter, calling the stored procedure asynchronically means the procedure will return a custom generated struct with the typed view instance and the output parameter values (one for each output parameter).

This struct is called FetchtypedViewNameTypedViewResult. In the example below, we're fetching the typed view CustomersFromProc and the returned struct therefore is called FetchCustomerFromProcTypedViewResult. The type is generic as the typed view instance is a List<T>. 

var qf = new QueryFactory();
var result = await RetrievalProcedures
                .FetchCustomersFromProcTypedViewAsync(
                    qf.GetCustomersFromProcTypedViewProjection(), 0, 
                    CancellationToken.None);
var amount = result.Amount; // the output parameter of the procedure
var typedViewInstance = result.CustomersFromProcTypedView;

Instantiating a TypedView mapped onto a Table Valued Function

Similar to TypedViews mapped onto a stored procedure resultset, when you map a TypedView onto a resultset of a table valued function, the fetch action of the TypedView is different from the fetch of a TypedView mapped onto a table or view.

OutputType: TypedDataTable

When you map a TypedView onto a resultset of a table valued function, the fetch action of the TypedView is equal to fetching a TypedView mapped onto a table or view: simply call the Fill method, by specifying the Table Valued Function parameter values when calling Fill().  In the following example, a TypedView is mapped onto the resultset of a table valued function which returns the Orders for a given Customer.

The Table Valued Function call, created through the usage of the TvfCallFactory, requires an alias for the Table Valued Function Call's resultset. Appending filters to the fetch is possible by specifying predicates after the Table Valued Function calls's parameters, when calling Fill().

Be aware to specify 'O' as the source for fields in predicates and sort expressions. An example predicate has been specified in the example below, so the fetch filters on all employees with ID 4. The .Source(string) extension method is available in the SD.LLBLGen.Pro.QuerySpec namespace. If no predicate is required, simply use the overload of the Fill() method which doesn't accept a predicate.

var toFetch = new OrdersForCustomerTypedView();
toFetch.Fill("O", "ALFKI", OrdersForCustomerFields.EmployeeId.Source("O").Equal(4));

OutputType: PocoWithLinqQuery

For Linq, a method is generated which has the name of the Table-Valued Function which returns the typed view row and for each parameter of the Table-Valued-Function Call the method has a corresponding argument. This is equal to the method generated for Table Valued Functions which return an entity, so the api is more predictable. The method returns the same DataSource2 object as the property a normal TypedView would get, except it has a wrapped Table-Valued Function call set as well, similar to the Table-Valued Function which returns an entity.

This looks like the following:

var q = from o in metaData.GetOrdersForCustomerTV("ALFKI")
        where o.EmployeeId == 4
        select o;

Here, the typed view 'GetOrdersForCustomerTV' which is mapped onto the Table-Valued function 'GetOrdersForCustomer' is fetched, by using the method generated in the LinqMetaData class. An additional filter is appended, which filters the resultset of the Table-Valued-function on the EmployeeId field.

OutputType: PocoWithQuerySpecQuery

For QuerySpec a method is generated which has the name of the Table-Valued-Function which returns the typed view row, similar to the Linq variant and the methods which are generated for the entity returning Table-Valued-Function calls.

The method accepts an alias string and for each parameter of the Table-Valued-Function Call the method has a corresponding argument. The method signature for a TypedView mapped onto a tvfcall resultset is kept equal with the rest of the typedview methods to differentiate them from the entity returning methods.

Joining is possible too, similar to the join constructs with typed views on tables/views:

var qf = new QueryFactory();
var q = qf.OrdersForCustomerTV("C", "ALFKI")
        .From(QueryTarget.InnerJoin(qf.Employee)
                 .On(TvfOrdersForCustomerTVFields.EmployeeId.Source("C")
                                .Equal(EmployeeFields.EmployeeId)))
        .Where(EmployeeFields.EmployeeId.Equal(4));
var results = new TypedListDAO().FetchQuery(q);
Important!

If you're going to use the result of the generated method in a join construct, be aware that the alias passed to the TVF query method is used for the TVF call itself; the result of the method is a new query which has to be aliased separately. See the following example:

var qf = new QueryFactory();
var q = qf.Create()
         .Select(OrderFields.EmployeeId, OrderFields.EmployeeId.Count().As("EmpCount"))
         .GroupBy(OrderFields.EmployeeId)
         .From(qf.Order.InnerJoin(qf.GetOrdersForCustomerTvQs("TVFCall", "ALFKI").As("O"))
                 .On(OrderFields.OrderId.Equal(TvfOrdersForCustomerPocoQsFields.EmployeeId.Source("O"))));
var results = new TypedListDAO().FetchQuery(q);

Reading a value from a filled Typed View

After we've fetched the typed view, we can use the values read. It depends on what the OutputType of the Typed View definition is how you're going to use the fetched typed view.

OutputType: TypedDataTable

As said, TypedView objects in the form of a typed DataTable are read-only, and therefore handy for filling lists on the screen or website, but not usable for data manipulation. For modifying data you should use the entity classes/collection classes.

Below, we'll read a given value from row 0, the value for the Sales person. We assume the invoices object is filled with data using any of the previously mentioned ways to do so.

string salesPerson = invoices[0].Salesperson;

The index 0 points to the first row, and as the row is 'typed', it has named properties for the individual columns in the object; you can read the value using a property.

Null values

Because the TypedView classes are derived classes from DataTable, the underlying DataTable cells still contain System.DBNull.Value values if the field in the database is NULL. You can test for NULL by using the generated methods IsFieldNameNull(). When reading a field which value is System.DBNull.Value in code, like the example above, will result in the default value for the type of the field, as defined in the TypeDefaultValue class.

Databinding will result in the usage of a DataView, as that's build into the DataTable, which will then return the System.DBNull.Value values and not the TypeDefaultValue values.

OutputType: PocoWithLinqQuery / PocoWithQuerySpecQuery

The POCO classes are simple classes with for each field in the Typed View a property which is read/write. In general the fetch methods of Linq and QuerySpec return the POCO class instances in a List<T> object, where T is the type of the TypedView POCO class. Reading the values from the row instances is simple: just read the property from the object at the given row index.

Null values are present as .NET null / Nothing values.

Limiting and sorting a typed view

To sort the data in the typed view, we're not actually sorting the data in the object, but sorting the data before it is read into the object, thus using a sort operator in the actual SQL query.

OutputType: TypedDataTable

To specify the sort operator, you specify a set of SortClauses to the Fill() method. Below is illustrated the sorting of the invoices typed view on the field 'ExtendedPrice' in descending order. Sortclauses are easily created using the SortClause factory in the generated code. We pass the same filter as mentioned earlier.

invoices.Clear();   // clear al current data
var sorterInvoices = new SortExpression(InvoicesFields.ExtendedPrice.Descending());
invoices.Fill(0, sorterInvoices, true, invoicesFilter);

The rows are now sorted on the ExtendedPrice field, in descending order.

OutputType: PocoWithLinqQuery / PocoWithQuerySpecQuery

To limit / sort the typed-view fetch query for Poco with Linq / QuerySpec, you use normal Linq sort constructs if you use Linq or normal QuerySpec sort constructs if you use QuerySpec.