Using the TypedView classes

LLBLGen Pro supports read-only lists based on either a database view/table or POCO classes, called 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 in the LLBLGen Pro Designer. 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:

TypedDataTable (default)
PocoWithLinqQuery
PocoWithQuerySpecQuery

The default is TypedDataTable, which will result in a class like in versions v3.x and earlier: a DataTable-derived class which is retrievable using the low-level api. The other two are both resulting in a class, typedViewNameRow, and a query to fetch the typed view, either in Linq to LLBLGen or in QuerySpec.

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 Element Setting 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.

Fetching POCO typed views is slightly faster than DataTable based typed views.

Instantiating and using a TypedView

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 TypedView 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' TypedView and fill it with all the data in the view, the following code is sufficient: (using the LowLevel API)

C#VB.NET

InvoicesTypedView invoices = new InvoicesTypedView();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedView(invoices);
}

Dim invoices As New InvoicesTypedView()
Using adapter As New DataAccessAdapter()
    adapter.FetchTypedView(invoices)
End Using

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. See for filtering information and how to set up sorting clauses Getting started with filtering and Sorting.

C#VB.NET

InvoicesTypedView invoices = new InvoicesTypedView();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    RelationPredicateBucket bucket = new RelationPredicateBucket(InvoicesFields.OrderId > 11000);
    adapter.FetchTypedView(invoices, bucket, true);
}

Dim invoices As New InvoicesTypedView()
Using adapter As New DataAccessAdapter()
    Dim bucket As New RelationPredicateBucket(InvoicesFields.OrderId > 11000)
    adapter.FetchTypedView(invoices, bucket, True)
End Using

See the LLBLGen Pro runtime framework reference manual for the details on more overloads of the DataAccessAdapter.FetchTypedView method.

OutputType: PocoWithLinqQuery

For Linq, similar to entities mapped onto tables/views, a property is generated for each typed view which returns a DataSource2 Object, named after the typed view. The DataSource2 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 DataAccessAdapter().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 the normal FetchTypedView method as described above, a different overload of FetchTypedView is used by passing in a call to the stored procedure which resultset the TypedView is mapped on. In the following example, a TypedView is mapped onto the resultset of a stored procedure which returns the Customers for a given country.

C#VB.NET

CustomersOnCountryTypedView customersTv = new CustomersOnCountryTypedView();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedView(customersTv, RetrievalProcedures.GetQueryForCustomersOnCountryTypedView("USA"));
}

Dim customersTv as New CustomersOnCountryTypedView()
Using adapter As New DataAccessAdapter()
    adapter.FetchTypedView(customersTv, RetrievalProcedures.GetQueryForCustomersOnCountryTypedView("USA"))
End Using

Catalog / Schema name overwriting and / or Output parameters

When using the above construct, catalog/schema name overwrites and output parameters of the stored procedure are ignored. To get the values from output parameters or to utilize catalog/schema name overwriting, instead call the method RetrievalProcedures.FetchTypedViewNameTypedView() or one of its overloads. The above example then becomes:

C#VB.NET

CustomersOnCountryTypedView customersTv = new CustomersOnCountryTypedView();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    // adapter uses name overwriting. 
    RetrievalProcedures.FetchCustomersOnCountryTypedView(adapter, customersTv, "USA");
}

Dim customersTv as New CustomersOnCountryTypedView()
Using adapter As New DataAccessAdapter()
    // adapter uses name overwriting. 
    RetrievalProcedures.FetchCustomersOnCountryTypedView(adapter, customersTv, "USA")
End Using

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:

Obtain the projection lamdba from the QueryFactory. This projection is produced by the method TypedViewNameTypedViewProjection
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");

Alternatively, you can use FetchQueryFromSource to do the same:

List<CustomersOnCountryRow> results;
using(var adapter = new DataAccessAdapter())
{
    results = adapter.FetchQueryFromSource(
                new QueryFactory().GetCustomersOnCountryTypedViewProjection(), 
                RetrievalProcedures.GetCustomersOnCountryCallAsQuery("USA");
}

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

While the same FetchTypedView method is used as with table/view mapped TypedView fetches described above, using Table Valued Function calls requires the Table Valued Function call to be wrapped in a DynamicRelation. 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. We also specify the same alias (in our example: 'O') with the RelationsCollection in the RelationPredicateBucket to signal the fetch logic the projection has to target the set aliased with 'O'.

Appending filters to the fetch is possible by adding predicates to the bucket's PredicateExpression. 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.

C#VB.NET

var bucket = new RelationPredicateBucket(
                    new DynamicRelation(TvfCallFactory.GetOrdersForCustomer2("O", "ALFKI")));
bucket.PredicateExpression.Add(OrdersForCustomerFields.EmployeeId.Source("O") == 4);
bucket.Relations.SelectListAlias = "O";
var orders = new OrdersForCustomerTypedView();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedView(orders, bucket, true);
}

Dim bucket As New RelationPredicateBucket(New DynamicRelation(TvfCallFactory.GetOrdersForCustomer2("O", "ALFKI")))
bucket.PredicateExpression.Add(OrdersForCustomerFields.EmployeeId.Source("O") = 4)
bucket.Relations.SelectListAlias = "O"
Dim orders As New OrdersForCustomerTypedView()
Using adapter As New DataAccessAdapter()
    adapter.FetchTypedView(orders, bucket, True)
End Using

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 DataAccessAdapter().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 DataAccessAdapter().FetchQuery(q);

Reading a value from a filled TypedView

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.