// C# ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFieldIndex.Country, 0, "Country"); fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers"); fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct; // QuerySpec offers extension methods per aggregate function: fields[1].CountDistinct();
' VB.NET Dim fields As New ResultsetFields(2) fields.DefineField(CustomerFieldIndex.Country, 0, "Country") fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers") fields(1).AggregateFunctionToApply = AggregateFunction.CountDistinct ' QuerySpec offers extension methods per aggregate function: fields(1).CountDistinct()
AggregateFunction value | Description | SQL | Remarks |
None | No aggregate function applied (default) | Just the fieldname, no wrapper function | |
Avg | Calculates the average value for the field. | AVG(field) | works on numeric fields (decimal / int / float / byte / etc.) only |
AvgDistinct | Calculates the average value for the distinct values for field. | AVG(DISTINCT field) | works on numeric fields (decimal / int / float / byte / etc.) only |
Count | Calculates the number of rows for field. | COUNT(field) | |
CountDistinct | Calculates the number of rows with distinct values for field. | COUNT(DISTINCT field) | |
CountRow | Calculates the number of rows. | COUNT(*) | |
CountBig | Same as Count but now as 64bit value, if supported by target RDBMS. Otherwise same as Count | COUNT_BIG(field) | Only supported on SQL Server 2005+ |
CountBigDistinct | Same as CountDistinct but now as 64bit value,, if supported by target RDBMS. Otherwise same as CountDistinct | COUNT_BIG(DISTINCT field) | Only supported on SQL Server 2005+ |
CountBigRow | Same as CountRow but now as 64bit value, if supported by target RDBMS. Otherwise same as CountRow | COUNT_BIG(*) | Only supported on SQL Server 2005+ |
Max | Calculates the max value for field. | MAX(field) | works on numeric fields (decimal / int / float / byte / etc.) only |
Min | Calculates the min value for field. | MIN(field) | works on numeric fields (decimal / int / float / byte / etc.) only |
Sum | Calculates the sum of all values of field. | SUM(field) | works on numeric fields (decimal / int / float / byte / etc.) only |
SumDistinct | Calculates the sum of all distinct values of field. | SUM(DISTINCT field) | works on numeric fields (decimal / int / float / byte / etc.) only |
StDev | Calculates statistical standard deviation for the values of field. | SqlServer: STDEV(field) Oracle: STDDEV(field) Access: STDEV(field) |
works on floating point fields (float / single / etc.) only |
StDevDistinct | Calculates statistical standard deviation for the distinct values of field. | Oracle: STDDEV(DISTINCT field) | works on floating point fields (float / single / etc.) only |
Variance | Calculates statistical variance for the values of field. | SqlServer: VAR(field) Oracle: VARIANCE(field) Access: VAR(field) |
works on floating point fields (float / single / etc.) only |
VarianceDistinct | Calculates statistical variance over the distinct values of field. | Oracle: VARIANCE(DISTINCT field) | works on floating point fields (float / single / etc.) only |
// C# OrderDetailsCollection orderDetails = new OrderDetailsCollection(); decimal orderPrice = (decimal)orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, (OrderDetailsFIelds.OrderId == 10254));
' VB.NET Dim orderDetails As New OrderDetailsCollection() Dim orderPrice As Decimal = CDec(orderDetails.GetScalar(OrderDetailsFieldIndex.OrderId, _ (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, _ (OrderDetailsFIelds.OrderId == 10254)))
// C# DataAccessAdapter adapter = new DataAccessAdapter(); decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId, (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, (OrderDetailsFIelds.OrderId == 10254));
' VB.NET Dim adapter As DataAccessAdapter = New DataAccessAdapter() Dim orderPrice As Decimal = CDec(adapter.GetScalar(OrderDetailsFields.OrderId, _ (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, _ (OrderDetailsFIelds.OrderId == 10254)))
-- SQL SELECT OrderID, ProductID, (UnitPrice * Quantity) AS RowTotal FROM [Order Details]
// C# ResultsetFields fields = new ResultsetFields(3); fields.DefineField(OrderDetailsFields.OrderID, 0); fields.DefineField(OrderDetailsFields.ProductID, 1); fields.DefineField(new EntityField("RowTotal", (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2); DataTable results = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0); // QuerySpec alternative var qf = new QueryFactory(); var q = qf.Create() .Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, (OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal")); var dao = new TypedListDAO(); var results = dao.FetchAsDataTable(q);
' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(OrderDetailsFields.OrderID, 0)
fields.DefineField(OrderDetailsFields.ProductID, 1)
fields.DefineField(New EntityField("RowTotal", _
(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2)
Dim results As New DataTable()
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, results, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0)
' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Create() _
.Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, _
(OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal"))
Dim dao As New TypedListDAO()
Dim results = dao.FetchAsDataTable(q)
// C# ResultsetFields fields = new ResultsetFields(3); fields.DefineField(OrderDetailsFields.OrderID, 0); fields.DefineField(OrderDetailsFields.ProductID, 1); fields.DefineField(new EntityField2("RowTotal", (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2); DataTable results = new DataTable(); using(DataAccessAdapter adapter = new DataAccessAdapter()) { adapter.FetchTypedList(fields, results, null); } // QuerySpec alternative var qf = new QueryFactory(); var q = qf.Create() .Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, (OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal")); var results = new DataTable(); using(var adapter = new DataAccessAdapter()) { adapter.FetchAsDataTable(q, results); }
' VB.NET Dim fields As New ResultsetFields(3) fields.DefineField(OrderDetailsFields.OrderID, 0) fields.DefineField(OrderDetailsFields.ProductID, 1) fields.DefineField(New EntityField2("RowTotal", _ (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2) Dim results As New DataTable() Using adapter As New DataAccessAdapter() adapter.FetchTypedList(fields, results, Nothing) End Using ' QuerySpec alternative Dim qf As New QueryFactory() Dim q = qf.Create() _ .Select(OrderDetailFields.OrderId, OrderDetailFields.ProductId, _ (OrderDetailFields.UnitPrice * OrderDetailFields.Quantity).As("RowTotal")) Dim results As New DataTable() Using adapter As New DataAccessAdapter() adapter.FetchAsDataTable(q, results) End Using
// C# IPredicate filter = ((MyEntityFields.Field1 + MyEntityFields.Field2) > (MyEntityFields.Field3 * MyEntityFields.Field4));
' VB.NET Dim filter As IPredicate = ((MyEntityFields.Field1 + MyEntityFields.Field2) > _ MyEntityFields.Field3 * MyEntityFields.Field4))
When an entity is saved and it is a new entity, an insert query is issued. By default the entity fields' values are used for the insert, however you can also execute an expression as the value of the field to insert, e.g. a function call or a scalar query expression. To do so, set the ExpressionToApply property of the entity field in the entity to an Expression object.
There are a couple of restrictions for this:
// C# EmployeeEntity employee = new EmployeeEntity(); employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = (EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f)); EmployeeCollection employees = new EmployeeCollection(); // no filter is specified, everybody gets 10% extra, but you could of course // specify a filter to limit the scope of the update. employees.UpdateMulti(employee, null);
' VB.NET Dim employee As New EmployeeEntity() employee.Fields(CInt(EmployeeFieldIndex.Salary)).ExpressionToApply = _ (EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f)) Dim employees As New EmployeeCollection() ' no filter is specified, everybody gets 10% extra, but you could of course ' specify a filter to limit the scope of the update. employees.UpdateMulti(employee, Nothing)
// C# EmployeeEntity employee = new EmployeeEntity(); employee.Fields[(int)EmployeeFieldIndex.Salary].ExpressionToApply = (EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f)); DataAccessAdapter adapter = new DataAccessAdapter(); // no filter is specified, everybody gets 10% extra, but you could of course // specify a filter to limit the scope of the update. adapter.UpdateEntitiesDirectly(employee, null);
' VB.NET Dim employee As New EmployeeEntity() employee.Fields(CInt(EmployeeFieldIndex.Salary)).ExpressionToApply = _ (EmployeeFields.Salary + (EmployeeFields.Salary * 0.01f)) Dim adapter As DataAccessAdapter = New DataAccessAdapter() ' no filter is specified, everybody gets 10% extra, but you could of course ' specify a filter to limit the scope of the update. adapter.UpdateEntitiesDirectly(employee, Nothing)
-- SQL SELECT CustomerID, ( SELECT COUNT(*) FROM Orders WHERE CustomerID = Customers.CustomerID ) AS NumberOfOrders FROM Customers
// C# ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFields.CustomerID, 0); fields.DefineField(new EntityField("NumberOfOrders", new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), (CustomerFields.CustomerId == OrderFields.CustomerId))), 1); DataTable results = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0); // QuerySpec alternative var qf = new QueryFactory(); var q = qf.Create() .Select(CustomerFields.CustomerId, qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) .CountRow() .As("NumberOfOrders")); var dao = new TypedListDAO(); var results = dao.FetchAsDataTable(q);
' VB.NET Dim fields As New ResultsetFields(2) fields.DefineField(CustomerFields.CustomerID, 0) fields.DefineField(New EntityField("NumberOfOrders", _ New ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), _ (CustomerFields.CustomerId = OrderFields.CustomerId))), 1) Dim results As New DataTable() Dim dao As New TypedListDAO() dao.GetMultiAsDataTable(fields, results, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0) ' QuerySpec alternative Dim qf As New QueryFactory() Dim q = qf.Create() _ .Select(CustomerFields.CustomerId, _ qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) _ .CountRow() _ .As("NumberOfOrders")) Dim dao As New TypedListDAO() Dim results = dao.FetchAsDataTable(q)
// C# ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFields.CustomerID, 0); fields.DefineField(new EntityField2("NumberOfOrders", new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), (CustomerFields.CustomerId == OrderFields.CustomerId))), 1); DataTable results = new DataTable(); using(DataAccessAdapter adapter = new DataAccessAdapter()) { adapter.FetchTypedList(fields, results, null); } // QuerySpec alternative var qf = new QueryFactory(); var q = qf.Create() .Select(CustomerFields.CustomerId, qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) .CountRow() .As("NumberOfOrders")); var results = new DataTable(); using(var adapter = new DataAccessAdapter()) { adapter.FetchAsDataTable(q, results); }
' VB.NET Dim fields As New ResultsetFields(2) fields.DefineField(CustomerFields.CustomerID, 0) fields.DefineField(New EntityField2("NumberOfOrders", _ New ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), _ (CustomerFields.CustomerId = OrderFields.CustomerId))), 1) Dim results As New DataTable() Dim adapter As New DataAccessAdapter() adapter.FetchTypedList(fields, results, Nothing) ' QuerySpec alternative Dim qf As New QueryFactory() Dim q = qf.Create() _ .Select(CustomerFields.CustomerId, _ qf.Order.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId) _ .CountRow() _ .As("NumberOfOrders")) Dim results As New DataTable() Using adapter As New DataAccessAdapter() adapter.FetchAsDataTable(q, results) End Using