public static DataTable GetUserUsingUserNamePasswordAsDataTable(string userName, string password)
{
var fields = new ResultsetFields(4);
fields.DefineField(UserFields.Id, 0);
fields.DefineField(UserFields.FullName, 1);
fields.DefineField(UserFields.IsAdmin, 2);
fields.DefineField(UserFields.IsInstructor, 3);
var filter = new PredicateExpression
{
(UserFields.Username == userName | UserFields.Email == userName),
UserFields.Password == password
};
var relations = new RelationCollection()
{
};
var toReturn = new DataTable();
var dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, toReturn, 0, null, filter, relations, true, null, null, 0, 0);
return(toReturn);
}
/// <summary>
/// Gets the customer with the most orders and also the customers' number of orders.
/// </summary>
/// <param name="numberOfOrders">The number of orders.</param>
/// <returns>the CustomerEntity with the most orders and also the number of orders of this customer</returns>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
public CustomerEntity GetCustomerWithMostOrdersAndNumberOfOrders(out int numberOfOrders)
{
Console.WriteLine("GetCustomerWithMostOrdersAndNumberOfOrders called");
CustomerEntity toReturn = null;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
// create a list to obtain the customerid + # of orders. We'll just fetch the 1st row, ordering on the # of orders.
ResultsetFields orderCustomerFields = new ResultsetFields(2);
orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerId");
orderCustomerFields.DefineField(OrderFields.OrderId, 1, "NumberOfOrders", "", AggregateFunction.Count);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderCustomerFields[0]);
SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(orderCustomerFields[1], null, SortOperator.Descending));
// now fetch the list, specify to fetch just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
adapter.FetchTypedList(orderCustomerFields, orderCustomer, null, 1, sorter, true, groupBy);
numberOfOrders = (int)orderCustomer.Rows[0]["NumberOfOrders"];
// we'll assume the data was there, so there is a row.
toReturn = new CustomerEntity(orderCustomer.Rows[0]["CustomerId"].ToString());
adapter.FetchEntity(toReturn);
}
return(toReturn);
}
/// <summary>
/// Gets various order statistics from the orders table
/// </summary>
/// <param name="averageOrderPrice">The average order price.</param>
/// <param name="highestOrderPrice">The higest order price.</param>
/// <param name="numberOfOrders">The number of orders.</param>
/// <param name="orderIdWithHighestPrice">The order id with highest price.</param>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
public void GetOrderStatistics(out decimal averageOrderPrice, out decimal highestOrderPrice, out int numberOfOrders, out int orderIdWithHighestPrice)
{
Console.WriteLine("GetOrderStatistics called");
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
// get all order prices using a dynamic list.
ResultsetFields orderPricesFields = new ResultsetFields(2);
orderPricesFields.DefineField(OrderDetailsFields.OrderId, 0, "OrderId");
orderPricesFields.DefineField(OrderDetailsFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
orderPricesFields[1].ExpressionToApply = (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderPricesFields[0]);
// fetch in a datatable, orderid + the order price.
DataTable orderPrices = new DataTable();
adapter.FetchTypedList(orderPricesFields, orderPrices, null, 0, null, true, groupBy);
// calculate average order price and which customer has the most expensive order
averageOrderPrice = 0.0M;
highestOrderPrice = 0.0M;
orderIdWithHighestPrice = 0;
for (int i = 0; i < orderPrices.Rows.Count; i++)
{
decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"];
if (currentOrderPrice > highestOrderPrice)
{
highestOrderPrice = currentOrderPrice;
orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"];
}
averageOrderPrice += currentOrderPrice;
}
averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count;
numberOfOrders = orderPrices.Rows.Count;
}
}
public List <OrderedPair <DateTime, DateTime> > GetMedicalVendorInvoicePayPeriods(long organizationRoleUserId,
PaymentStatus paymentStatus)
{
var payPeriodFields = new ResultsetFields(2);
payPeriodFields.DefineField(PhysicianInvoiceFields.PayPeriodStartDate, 0);
payPeriodFields.DefineField(PhysicianInvoiceFields.PayPeriodEndDate, 1);
var payPeriodsDataTable = new DataTable("Medical Vendor Invoice Pay Periods");
IRelationPredicateBucket bucket = new RelationPredicateBucket
(PhysicianInvoiceFields.PhysicianId == organizationRoleUserId);
bucket.PredicateExpression.Add(PhysicianInvoiceFields.PaymentStatus == (byte)paymentStatus);
using (var myAdapter = PersistenceLayer.GetDataAccessAdapter())
{
myAdapter.FetchTypedList(payPeriodFields, payPeriodsDataTable, bucket, false);
}
var payPeriods = new List <OrderedPair <DateTime, DateTime> >();
foreach (DataRow row in payPeriodsDataTable.Rows)
{
var orderedPair = new OrderedPair <DateTime, DateTime>((DateTime)row["PayPeriodStartDate"],
(DateTime)row["PayPeriodEndDate"]);
payPeriods.Add(orderedPair);
}
return(payPeriods);
}
/// <summary>Builds the resultset fields.</summary>
/// <returns>ready to use resultset</returns>
protected override IEntityFields BuildResultset()
{
ResultsetFields toReturn = new ResultsetFields(AmountOfFields);
toReturn.DefineField(ThreadFields.ThreadID, 0, "ThreadID", "", AggregateFunction.None);
toReturn.DefineField(ThreadFields.Subject, 1, "Subject", "", AggregateFunction.None);
toReturn.DefineField(ForumFields.ForumName, 2, "ForumName", "", AggregateFunction.None);
toReturn.DefineField(SectionFields.SectionName, 3, "SectionName", "", AggregateFunction.None);
toReturn.DefineField(ThreadFields.ThreadLastPostingDate, 4, "ThreadLastPostingDate", "", AggregateFunction.None);
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields
// be sure to call toReturn.Expand(number of new fields) first.
// __LLBLGENPRO_USER_CODE_REGION_END
OnResultsetBuilt(toReturn);
return(toReturn);
}
public OrderedPair <ResultsetFields, GroupByCollection> CreateMedicalVendorInvoiceStatisticFieldsAndGroupByClause()
{
var resultsetFields = new ResultsetFields(5);
resultsetFields.DefineField(PhysicianInvoiceItemFields.PhysicianInvoiceId, 0, "MedicalVendorInvoiceId");
resultsetFields.DefineField(PhysicianInvoiceItemFields.PhysicianInvoiceId, 1, "NumberOfEvaluations", AggregateFunction.Count);
resultsetFields.DefineField(PhysicianInvoiceItemFields.AmountEarned, 2, "InvoiceAmount", AggregateFunction.Sum);
//Need to map from Physican Evaluation
//resultsetFields.DefineField(PhysicianInvoiceItemFields.EvaluationStartTime, 3, "EvaluationStartTime");
//resultsetFields.DefineField(PhysicianInvoiceItemFields.EvaluationEndTime, 4, "EvaluationEndTime");
var groupByCollection = new GroupByCollection(resultsetFields[0])
{
resultsetFields[3], resultsetFields[4]
};
return(new OrderedPair <ResultsetFields, GroupByCollection>(resultsetFields, groupByCollection));
}
/// <summary>Builds the resultset fields, stored in the private _fields object</summary>
private void BuildResultset()
{
_fields = new ResultsetFields(AmountOfFields);
_fields.DefineField(RegionFields.RegionId, 0, "RegionId", "", AggregateFunction.None);
_fields.DefineField(RegionFields.RegionDescription, 1, "RegionDescription", "", AggregateFunction.None);
_fields.DefineField(TerritoryFields.TerritoryId, 2, "TerritoryId", "", AggregateFunction.None);
_fields.DefineField(TerritoryFields.TerritoryDescription, 3, "TerritoryDescription", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.EmployeeId, 4, "EmployeeId", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.FirstName, 5, "EmployeeFirstName", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.LastName, 6, "EmployeeLastName", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.City, 7, "EmployeeCity", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.Country, 8, "EmployeeCountry", "", AggregateFunction.None);
_fields.DefineField(EmployeeFields.Region, 9, "EmployeeRegion", "", AggregateFunction.None);
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields
// be sure to call _fields.Expand(number of new fields) first.
// __LLBLGENPRO_USER_CODE_REGION_END
OnResultsetBuilt(_fields);
}
internal DataTable GetAvailableMatchDays_(long homeTeamId, DateTimePeriod dateSegment, List <DateTime> excludedDates)
{
/* Generate Query:
* SELECT DISTINCT (DATE(`available_match_date`.MatchDateTime) + INTERVAL 0 SECOND) AS `MatchDate` FROM `available_match_date`
* WHERE ( ( `available_match_date`.TournamentId = @TournamentId1 AND `available_match_date`.HomeTeamId = @HomeTeamId2
* AND ( (DATE(`available_match_date`.MatchDateTime) + INTERVAL 0 SECOND) BETWEEN @MatchDate3 AND @MatchDate4)
* AND ( (DATE(`available_match_date`.MatchDateTime) + INTERVAL 0 SECOND) NOT IN (@MatchDate5)))) ORDER BY MatchDate ASC
*
* Attention: Date parameters always have 00:00:00 included in LLBLGenPro, that's why we have to add 0 seconds to date fields!
*/
var fields = new ResultsetFields(2);
fields.DefineField(AvailableMatchDateFields.MatchStartTime, 0, "MatchStartTime");
fields.DefineField(AvailableMatchDateFields.Id, 1);
// truncate the time part of the match date
fields[0].ExpressionToApply = new DbFunctionCall("(DATE({0}) + INTERVAL 0 SECOND)", new object[] { AvailableMatchDateFields.MatchStartTime });
// General limitation: TournamentId
IRelationPredicateBucket bucket = new RelationPredicateBucket(AvailableMatchDateFields.TournamentId == _matchPlanner.Tournament.Id);
bucket.PredicateExpression.AddWithAnd(AvailableMatchDateFields.HomeTeamId == homeTeamId);
// date within desired segment
IPredicateExpression dateFilter = new PredicateExpression(new FieldBetweenPredicate(fields[0], null, dateSegment.Start, dateSegment.End));
bucket.PredicateExpression.AddWithAnd(dateFilter);
// dates NOT IN excluded dates (matches already assigned)
if (excludedDates.Count != 0)
{
IPredicateExpression excludedDatesFilter = new PredicateExpression(new FieldCompareRangePredicate(fields[0], null, true, excludedDates.ToArray()));
bucket.PredicateExpression.AddWithAnd(excludedDatesFilter);
}
ISortExpression sortClause = new SortExpression(new SortClause(fields[0], null, SortOperator.Ascending));
var dynamicList = new DataTable();
_matchPlanner.Adapter.FetchTypedList(fields, dynamicList, bucket, 0, sortClause, false, null);
return(dynamicList);
}
/// <summary>Builds the resultset fields.</summary>
/// <returns>ready to use resultset</returns>
protected override IEntityFields BuildResultset()
{
ResultsetFields toReturn = new ResultsetFields(AmountOfFields);
toReturn.DefineField(MessageFields.MessageID, 0, "MessageID", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.PostingDate, 1, "PostingDate", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.MessageTextAsHTML, 2, "MessageTextAsHTML", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.ThreadID, 3, "ThreadID", "", AggregateFunction.None);
toReturn.DefineField(ThreadFields.Subject, 4, "Subject", "", AggregateFunction.None);
toReturn.DefineField(UserFields.EmailAddress, 5, "EmailAddress", "", AggregateFunction.None);
toReturn.DefineField(UserFields.EmailAddressIsPublic, 6, "EmailAddressIsPublic", "", AggregateFunction.None);
toReturn.DefineField(UserFields.NickName, 7, "NickName", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.MessageText, 8, "MessageText", "", AggregateFunction.None);
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields
// be sure to call toReturn.Expand(number of new fields) first.
// __LLBLGENPRO_USER_CODE_REGION_END
OnResultsetBuilt(toReturn);
return(toReturn);
}
/// <summary>Builds the resultset fields.</summary>
/// <returns>ready to use resultset</returns>
protected override IEntityFields BuildResultset()
{
ResultsetFields toReturn = new ResultsetFields(AmountOfFields);
toReturn.DefineField(ForumFields.ForumID, 0, "ForumID", "", AggregateFunction.None);
toReturn.DefineField(ForumFields.ForumName, 1, "ForumName", "", AggregateFunction.None);
toReturn.DefineField(SectionFields.SectionName, 2, "SectionName", "", AggregateFunction.None);
toReturn.DefineField(ForumFields.ForumDescription, 3, "ForumDescription", "", AggregateFunction.None);
toReturn.DefineField(SectionFields.SectionID, 4, "SectionID", "", AggregateFunction.None);
toReturn.DefineField(ForumFields.OrderNo, 5, "ForumOrderNo", "", AggregateFunction.None);
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields
// be sure to call toReturn.Expand(number of new fields) first.
// __LLBLGENPRO_USER_CODE_REGION_END
OnResultsetBuilt(toReturn);
return(toReturn);
}
internal DataTable GetAllAvailableMatchDays_(RoundLegEntity roundLeg)
{
var fields = new ResultsetFields(1);
fields.DefineField(AvailableMatchDateFields.MatchStartTime, 0, "MatchStartTime");
fields[0].ExpressionToApply = new DbFunctionCall("(DATE({0}) + INTERVAL 0 SECOND)", new object[] { AvailableMatchDateFields.MatchStartTime });
IRelationPredicateBucket bucket = new RelationPredicateBucket(AvailableMatchDateFields.TournamentId == _matchPlanner.Tournament.Id);
// date within round leg limits
IPredicateExpression dateFilter = new PredicateExpression(new FieldBetweenPredicate(fields[0], null, roundLeg.StartDateTime.Date, roundLeg.EndDateTime.Date));
bucket.PredicateExpression.AddWithAnd(dateFilter);
ISortExpression sortClause = new SortExpression(new SortClause(fields[0], null, SortOperator.Ascending));
var dynamicList = new DataTable();
_matchPlanner.Adapter.FetchTypedList(fields, dynamicList, bucket, 0, sortClause, true, null);
return(dynamicList);
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
RelationCollection locationToMessage = new RelationCollection
{
new EntityRelation(LocationFields.LocationId, DeviceFields.LocationId, RelationType.OneToOne),
new EntityRelation(DeviceFields.DeviceId, DeviceMessageFields.DeviceId, RelationType.OneToOne),
new EntityRelation(DeviceMessageFields.MessageId, MessageFields.MessageId, RelationType.OneToOne)
};
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(MessageFields.MessageId, 0, "MessageId");
fields.DefineField(MessageFields.Title, 1, "Title");
fields.DefineField(MessageFields.Body, 2, "Body");
fields.DefineField(MessageFields.CreateTime, 3, "CreateTime");
fields.DefineField(MessageFields.StartTime, 4, "StartTime");
fields.DefineField(MessageFields.EndTime, 5, "EndTime");
fields.DefineField(LocationFields.Name, 6, "LocationName");
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
if (Roles.IsUserInRole("Service Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, locationToMessage, true, null, null, 0, 0);
}
else if (Roles.IsUserInRole("Organization Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null,
new PredicateExpression(LocationFields.OrganizationId == Membership.GetUser().GetUserId().OrganizationId),
locationToMessage, true, null, null, 0, 0);
}
//else
// messages.GetMulti(EcMessageBankFields.CustomerLocationId == user.LocationId);
if (dynamicList.Rows.Count > 0)
{
bool alternate = false;
foreach (DataRow message in dynamicList.Rows)
{
TableRow messageHeader = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageHeader.Controls.Add(new TableCell()
{
Text = message["CreateTime"].ToString()
});
messageHeader.Controls.Add(new TableCell()
{
Text = message["LocationName"].ToString()
});
MainTable.Controls.Add(messageHeader);
TableRow messageBody = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageBody.Controls.Add(new TableCell()
{
ColumnSpan = 2,
Text =
"<strong style='float:left; display:inline-block; padding-right:5px;'>" +
message["Title"] + "</strong>" + message["Body"]
});
MainTable.Controls.Add(messageBody);
alternate = !alternate;
}
}
else
{
TableRow empty_list = new TableRow();
empty_list.Controls.Add(new TableCell()
{
Text = "No messages to display."
});
MainTable.Controls.Add(empty_list);
}
}
/// <summary>
/// Calculates the statistics for the Northwind database and shows them in the form, illustrating the
/// direct database power LLBLGen Pro offers you through scalar functions, aggregates, group by and expressions.
/// </summary>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
private void CalculateStatistics()
{
// get amount customers
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
int amountCustomers = (int)adapter.GetScalar(CustomerFields.CustomerId, AggregateFunction.CountRow);
_amountCustomersTextBox.Text = amountCustomers.ToString();
// get all order prices
ResultsetFields orderPricesFields = new ResultsetFields(2);
orderPricesFields.DefineField(OrderDetailFields.OrderId, 0, "OrderId");
orderPricesFields.DefineField(OrderDetailFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
orderPricesFields[1].ExpressionToApply = (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderPricesFields[0]);
// fetch in a datatable, orderid + the order price.
DataTable orderPrices = new DataTable();
adapter.FetchTypedList(orderPricesFields, orderPrices, null, 0, null, true, groupBy);
// calculate average order price and which customer has the most expensive order
decimal averageOrderPrice = 0.0M;
decimal highestOrderPrice = 0.0M;
int orderIdWithHighestPrice = 0;
for (int i = 0; i < orderPrices.Rows.Count; i++)
{
decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"];
if (currentOrderPrice > highestOrderPrice)
{
highestOrderPrice = currentOrderPrice;
orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"];
}
averageOrderPrice += currentOrderPrice;
}
averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count;
_highestOrderPriceTextBox.Text = highestOrderPrice.ToString("C");
_averageOrderPriceTextBox.Text = averageOrderPrice.ToString("C");
// get order with highest price. Pull customer also in 1 go
IPrefetchPath2 prefetchPathCustomer = new PrefetchPath2((int)EntityType.OrderEntity);
prefetchPathCustomer.Add(OrderEntity.PrefetchPathCustomer);
OrderEntity highestOrder = new OrderEntity(orderIdWithHighestPrice);
adapter.FetchEntity(highestOrder, prefetchPathCustomer);
_customerWithHighestOrder = highestOrder.Customer; // already loaded through prefetch path.
_highestOrderCustomerTextBox.Text = _customerWithHighestOrder.CompanyName;
// get customer with most orders.
ResultsetFields orderCustomerFields = new ResultsetFields(2);
orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerId");
orderCustomerFields.DefineField(OrderFields.OrderId, 1, "AmountOrders", "", AggregateFunction.Count);
groupBy = new GroupByCollection();
groupBy.Add(orderCustomerFields[0]);
SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(orderCustomerFields[1], null, SortOperator.Descending));
// now fetch the list, just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
adapter.FetchTypedList(orderCustomerFields, orderCustomer, null, 1, sorter, true, groupBy);
_mostOrdersPerCustomerTextBox.Text = orderCustomer.Rows[0]["AmountOrders"].ToString();
// we'll assume the data was there, so there is a row.
_customerWithMostOrders = new CustomerEntity(orderCustomer.Rows[0]["CustomerId"].ToString());
adapter.FetchEntity(_customerWithMostOrders);
_mostOrdersCustomerTextBox.Text = _customerWithMostOrders.CompanyName;
}
}
/// <summary>Builds the resultset fields.</summary>
/// <returns>ready to use resultset</returns>
protected override IEntityFields BuildResultset()
{
ResultsetFields toReturn = new ResultsetFields(AmountOfFields);
toReturn.DefineField(MessageFields.MessageID, 0, "MessageID", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.PostingDate, 1, "PostingDate", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.MessageTextAsHTML, 2, "MessageTextAsHTML", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.ThreadID, 3, "ThreadID", "", AggregateFunction.None);
toReturn.DefineField(MessageFields.PostedFromIP, 4, "PostedFromIP", "", AggregateFunction.None);
toReturn.DefineField(UserFields.UserID, 5, "UserID", "", AggregateFunction.None);
toReturn.DefineField(UserFields.NickName, 6, "NickName", "", AggregateFunction.None);
toReturn.DefineField(UserFields.SignatureAsHTML, 7, "SignatureAsHTML", "", AggregateFunction.None);
toReturn.DefineField(UserFields.IconURL, 8, "IconURL", "", AggregateFunction.None);
toReturn.DefineField(UserFields.Location, 9, "Location", "", AggregateFunction.None);
toReturn.DefineField(UserFields.JoinDate, 10, "JoinDate", "", AggregateFunction.None);
toReturn.DefineField(UserFields.AmountOfPostings, 11, "AmountOfPostings", "", AggregateFunction.None);
toReturn.DefineField(UserTitleFields.UserTitleDescription, 12, "UserTitleDescription", "", AggregateFunction.None);
// __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields
// be sure to call toReturn.Expand(number of new fields) first.
// __LLBLGENPRO_USER_CODE_REGION_END
OnResultsetBuilt(toReturn);
return(toReturn);
}
/// <summary>
/// Builds the dynamic list for a query with all threads with statistics.
/// </summary>
/// <returns>setup in and ready to use resultset object</returns>
/// <remarks>Doesn't add the forum fields</remarks>
internal static ResultsetFields BuildDynamicListForAllThreadsWithStats()
{
ResultsetFields fields = new ResultsetFields(14);
fields.DefineField(ThreadFields.ThreadID, 0);
fields.DefineField(ThreadFields.ForumID, 1);
fields.DefineField(ThreadFields.Subject, 2);
fields.DefineField(ThreadFields.StartedByUserID, 3);
fields.DefineField(ThreadFields.ThreadLastPostingDate, 4);
fields.DefineField(ThreadFields.IsSticky, 5);
fields.DefineField(ThreadFields.IsClosed, 6);
fields.DefineField(ThreadFields.MarkedAsDone, 7);
fields.DefineField(ThreadFields.NumberOfViews, 8);
// the next field refers to an object alias, as we'll join User twice.
fields.DefineField(UserFields.NickName.SetObjectAlias("ThreadStarterUser"), 9);
// the next field is a scalar query with the # of postings in the thread.
fields.DefineField(new EntityField("AmountMessages",
new ScalarQueryExpression(MessageFields.MessageID.SetAggregateFunction(AggregateFunction.Count),
(MessageFields.ThreadID == ThreadFields.ThreadID)), typeof(int)), 10);
// the next two field refer to an object alias, as we'll join User twice.
fields.DefineField(UserFields.UserID, 11, "LastPostingByUserID", "LastPostingUser");
fields.DefineField(UserFields.NickName, 12, "NickNameLastPosting", "LastPostingUser");
// the next field sets the alias for the field to a different string than the default.
fields.DefineField(MessageFields.MessageID, 13, "LastMessageID", "LastMessage");
return(fields);
}