Projections
Projections are specified by using the method query.Select(elements to project). The Select method is defined for both EntityQuery<T> and DynamicQuery. For EntityQuery<T> it creates a DynamicQuery of the entity query, using the projection specified, while Select() for a DynamicQuery simply defines the projection of the DynamicQuery.
.Select() has a generic and non-generic variant. The generic variant returns a DynamicQuery<T>, the non-generic variant returns a DynamicQuery.
Elements to project is a list of 1 or more elements which can be assigned to or are equal to entity fields or constants. There are exceptions to this, see nested queries below.
For example a .Select(CustomerFields.CompanyName), will define a single field (Customer.CompanyName) in the projection. Expressions can be specified as well as an element to project. Expressions (Every IExpression implementing element) will be assigned to new entity fields.
Projection.Full
To make life easier there is a placeholder defined which simply copies the complete projection of the query Select is called on. This object is added using the method Projection.Full. This is useful when calling Select() on a query in situations where the complete projection has to be re-used.
When using Projection.Full without .Source, it's only allowed on EntityQuery<T> instances. For DynamicQuery / DynamicQuery<T> instances, use Projection.Full.Source("aliasOfSourceQuery").
Examples:
// On EntityQuery<T>
// 'q' will be a dynamic query over Customer (which will be wrapped in a derived table), with the full projection
// of customer and also the scalar query 'TheSum'
var q = qf.Customer
.Select(Projection.Full,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerUsingCustomerId)
.Sum(OrderFields.EmployeeId).As("TheSum"));
// On DynamicQuery.
var q = qf.Create()
.Select(Projection.Full.Source("g"))
.From(qf.Create()
.Select(CustomerFields.Country.Source("c"),
Functions.CountRow().As("NumberOfCustomers"),
qf.Field("c", "NumberOfOrders").Sum().As("TotalNumberOfOrders"))
.From(qf.Customer
.Select(CustomerFields.Country,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.CountRow().As("NumberOfOrders"))
.As("c"))
.GroupBy(CustomerFields.Country.Source("c"))
.As("g"))
.OrderBy(CustomerFields.Country.Source("g").Ascending());
Value aliases
If a field, expression or boolean representing predicate requires an alias, the element has to be appended with a .As(alias) call. The element is assigned the specified alias in the projection.
If no alias is specified and the element requires an alias, e.g. in the case of an expression or aggregated field, the element gets an automatic alias, LLBLV_number. Number is a unique number per query, increased with every element in the projection.
Nested queries
Nested queries are just queries like any other query, and can be an EntityQuery<T>, DynamicQuery<T> or DynamicQuery. A nested query has to have a correlated predicate, which is either specified with .CorrelatedOver(entity relation) or .CorrelatedOver(predicate).
The predicate specified has to be a comparison between fields, so the nested query can be tied to the outer query's result-set. This is similar to the rules for nested queries in Linq.
Nested queries are specified in the projection of a dynamic query and if the outer query's projection they're defined in is a typed projection, the nested query also has to have a typed projection (EntityQuery<T> or DynamicQuery<T>).
Nested queries result in either a resultset or a single value/object result. You have to call ToResultset() at the end of the nested query if it has to return a resultset, and ToSingleResult() if the query results in a single value/object result.
Examples
Nested query which results in a resultset
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Country == "Germany")
.Select(() => new
{
CustomerId = CustomerFields.CustomerId.ToValue<string>(),
Orders = qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.Select(() => new
{
OrderId = OrderFields.OrderId.ToValue<int>(),
OrderDate = OrderFields.OrderDate.ToValue<DateTime?>()
})
.ToResultset()
});
Nested query which results in a single result
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
EmployeeId = OrderFields.EmployeeId.ToValue<int?>(),
CompanyNameValue =
qf.Customer.CorrelatedOver(
CustomerEntity.Relations.OrderEntityUsingCustomerId)
.Select(() => new
{
CompanyName = CustomerFields.CompanyName.ToValue<string>()
})
.ToSingleResult()
})
.OrderBy(OrderFields.EmployeeId.Ascending(), OrderFields.CustomerId.Descending())
.Distinct();
Booleans/predicates in projection
To specify a predicate in the projection, e.g. through a Contains, All or Any call (which produce predicates), it's sufficient to simply specify the predicate. The QuerySpec system will wrap the predicate with an Functions.IIF( predicate , true, false) call. If no alias is specified, an artificial alias is used.
Typed Projections
Typed projections are either an EntityQuery<T> or DynamicQuery<T> returned by a Select call. The lambda specified as argument for the Select() call is converted in a lambda for the WithProjector method, and a list of objects to pass to the untyped Select(params object[]) method.
A typed projection is also constructible by calling WithProjector manually. This gives fine grained control over what projection lambda is used and allows re-use of values in the raw set. The Select(lamdba) is easier to use, and the separated Select(params object[]) and WithProjector(lambda) combination gives more power.
Specifying the type of an element in the projection lambda
To construct a result type, the elements in the projection have to be specified as a specific type. E.g. when the field CustomerFields.CompanyName is specified in the projection lambda, it is a field object, not a string, while the value it represents is a string. To specify the type, append .ToValue<type>()** to the element, where type is the result type of the element.
Example:
qf.Create()
.Select(()=>new { CompanyName = CustomerFields.CompanyName.ToValue<string>() });
The example above defines a projection lambda and constructs an anonymous type with one projection element: CustomerFields.CompanyName. This is stored in the CompanyName property of the anonymous type during projection.
The .ToValue<string>() call signals the QuerySpec processor to handle the CustomerFields.CompanyName element as a projection element. It also makes sure the property CompanyName of the anonymous type is of type string.
Under the hood, the above lambda is converted to the lambda:
r=>new { CompanyName = (string)r[0]};
and it will pass CustomerFields.CompanyName as the element to use in the projection for the SQL query.
The above example also could have been specified as:
qf.Create()
.Select(CustomerFields.CompanyName)
.WithProjector(r=> new { CompanyName = (string)r[0]});
This separates projection of the query to execute on the database from the projection to in-memory objects, which can be useful in some scenarios, while it can be verbose in others. As both are equivalent, it depends on the situation at hand which one to choose.
The type specifying extension methods available in a projection
The following extension methods are available for specifying the element type in a typed projection lambda (the one passed to Select(lambda)) and on which types they're working on.
- ToValue<T>(). This method is available on IEntityFieldCore and IExpression implementing objects like fields, expessions, function calls, scalar query expressions etc.
- ToValue(). This method returns a boolean and is available on predicates which are specified in the projection.
- ToResultset(). This method returns an IList<T> with the results of a DynamicQuery<T> or an EntityQuery<T>. In the case of an EntityQuery<T>, the IList<T> is an Entity Collection object, of the type related to the used template group
- ToSingleResult(). This method returns a single instance (the first one) of type T for an EntityQuery<T> or DynamicQuery<T>.
If an element doesn't have one of the calls above, e.g. you specify CustomerFields.CompanyName in the projection but not with .ToValue<string>(), it's not taken into account as an element to project from the resultset, and is compiled into the projection lambda together with the rest of the code inside the projection lambda.
So in short:
// Incorrect!
.Select(()=> new
{
CompanyName = CustomerFields.CompanyName,
CustomerId = CustomerFields.CustomerId
});
// correct
.Select(()=> new
{
CompanyName = CustomerFields.CompanyName.ToValue<string>(),
CustomerId = CustomerFields.CustomerId.ToValue<string>()
});
Example with nested query
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Country.Equal("Germany"))
.Select(() => new
{
CustomerId = CustomerFields.CustomerId.ToValue<string>(),
Orders = qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.Select(() => new
{
OrderId = OrderFields.OrderId.ToValue<int>(),
OrderDate = OrderFields.OrderDate.ToValue<DateTime?>()
})
.ToResultset()
});
The example above has a nested query which fetches order information (OrderId and OrderDate) of all orders of the customers from Germany, directly into a resultset (A List<anonymoustype>) using the ToResultset<T>() method.
This will result in two queries, one for the customers (the outer query) and one for the orders for all the customers matching the outer query. QuerySpec will then merge the two resultsets efficiently in-memory, avoiding a SELECT N+1 problem.
-- Query for customers.
SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
1 /* @p2 */ AS [LLBLV_1]
FROM [Northwind].[dbo].[Customers]
WHERE (((([Northwind].[dbo].[Customers].[Country] = 'Germany' /* @p3 */))))
-- Query for order data
SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId],
[Northwind].[dbo].[Orders].[OrderDate],
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId]
FROM [Northwind].[dbo].[Orders]
WHERE (((([Northwind].[dbo].[Orders].[CustomerID] IN
('ALFKI' /* @p1 */, 'BLAUS' /* @p2 */, 'DRACD' /* @p3 */, 'FRANK' /* @p4 */,
'KOENE' /* @p5 */, 'LEHMS' /* @p6 */, 'MORGK' /* @p7 */, 'OTTIK' /* @p8 */,
'QUICK' /* @p9 */, 'TOMSP' /* @p10 */, 'WANDK' /* @p11 */)))))
Easily create typed projections
To avoid typing long lists of ToValue<T>() calls for projections, there are several shortcuts to use for creating a typed projection. These are given below.
DynamicQuery.Select<T>(field list)
To create a custom typed projection of a set of fields you specify on a query you can use the Select<T>(field list) method to specify the list of fields to use in the projection and as well the result type in one go. Below is an example:
var qf = new QueryFactory();
var q = qf.Create()
.Select<CustomerDTO>(CustomerFields.Country,
Functions.IIF(CustomerFields.Country == "USA", CustomerFields.ContactName,
CustomerFields.ContactTitle).As("CheckValue"),
CustomerFields.ContactName,
CustomerFields.ContactTitle);
The above query specifies a projection of 4 fields, one being a function call (IIF(), which will result in a CASE ELSE statement) and the results are projected into instances of CustomerDTO. The above code is equal to the more verbose variant below:
// verbose variant of example above.
var qf = new QueryFactory();
var q = qf.Create()
.Select(()=>new CustomerDTO() {
Country = CustomerFields.Country.ToValue<string>(),
CheckValue = Functions.IIF(CustomerFields.Country == "USA", CustomerFields.ContactName,
CustomerFields.ContactTitle).ToValue<string>(),
ContactName = CustomerFields.ContactName.ToValue<string>(),
ContactTitle = CustomerFields.ContactTitle.ToValue<string>());
The Select<T>(field list) method will automatically create a lambda with ToValue() calls for every field - property combination which is found when examining the field list specified and the type specified as generic argument. This is done using case-insensitive comparisons so the field name / alias doesn't have to have the same casing as the property name.
ProjectionLambdaCreator
To create a custom typed projection, a helper class is available, called ProjectionLambdaCreator. This class has two overloads of its Create() method which creates at runtime a projection lambda for T from either a specified fields set or a fields creation class (e.g. CustomerFields).
The overload which accepts the fields creation class caches the created lambda and is therefore much faster than a lambda created in code and compiled by the C# / VB.NET compiler which will create a new Expression> at runtime each time it is run.
Usage:
var qf = new QueryFactory();
var q = qf.Create()
.Select(ProjectionLambdaCreator.Create<OrderPocoQsRow, OrderPocoQsFields>())
.Where(OrderPocoQsFields.CustomerId == "ALFKI");
The above projection is equal to:
//...
.Select(() => new NW26.Adapter.TypedViewClasses.OrderPocoQsRow()
{
CustomerId = OrderPocoQsFields.CustomerId.ToValue<System.String>(),
EmployeeId = OrderPocoQsFields.EmployeeId.ToValue<Nullable<System.Int32>>(),
Freight = OrderPocoQsFields.Freight.ToValue<Nullable<System.Decimal>>(),
OrderDate = OrderPocoQsFields.OrderDate.ToValue<Nullable<System.DateTime>>(),
OrderId = OrderPocoQsFields.OrderId.ToValue<System.Int32>(),
RequiredDate = OrderPocoQsFields.RequiredDate.ToValue<Nullable<System.DateTime>>(),
ShipAddress = OrderPocoQsFields.ShipAddress.ToValue<System.String>(),
ShipCity = OrderPocoQsFields.ShipCity.ToValue<System.String>(),
ShipCountry = OrderPocoQsFields.ShipCountry.ToValue<System.String>(),
ShipName = OrderPocoQsFields.ShipName.ToValue<System.String>(),
ShippedDate = OrderPocoQsFields.ShippedDate.ToValue<Nullable<System.DateTime>>(),
ShipPostalCode = OrderPocoQsFields.ShipPostalCode.ToValue<System.String>(),
ShipRegion = OrderPocoQsFields.ShipRegion.ToValue<System.String>(),
ShipVia = OrderPocoQsFields.ShipVia.ToValue<Nullable<System.Int32>>()
})
DynamicQuery.Select<T, U>()
There's a special Select method available which produces its own lambda projector from two types given:
var qf = new QueryFactory();
var q = qf.Create.Select<SomeDTO, SomeElementFields>();
This method, dynamicQuery.Select<T, U>() creates a projection and projector lambda from T and U for dynamicQuery and makes it a DynamicQuery<T>. The type T is the type of a class which instances are returned by the created DynamicQuery<T>, and can be an entity type or DTO or e.g. generated typed view type. The type U is the type of the generated helper class which creates the fields for T, e.g. CustomerFields.