I’m using .NET 2, Adapter, SQLServer. Here’s my problem:
Entity Setup:
“State” has many “Address” has many “User” has many “Recipes”
The recipe table has an enum for the recipe type (Appetizer, Entree, or Dessert).
So I want a list of all states. For each state I want a count of each type of recipe. For example:
Name - Appetizers/Entrees/Desserts
Alabama – 4/1/3
Alaska – 6/8/4
Arizona – 0/0/0
Arkansas – 2/0/1
etc...
MY CODE:
I’m not sure if this is even possible. Here’s my closest shot:
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(StateFields.Name, 0, "Name");
fields.DefineField(RecipeFields.Status, 1, "RecipeType");
fields.DefineField(RecipeFields.RecipeId, 2, "RecipeCount", AggregateFunction.Count);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
groupBy.Add(fields[1]);
ISortExpression sortExpression = new SortExpression();
sortExpression.Add(StateFields.Name | SortOperator.Ascending);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(StateEntity.Relations.AddressEntityUsingStateId, JoinHint.Left);
bucket.Relations.Add(AddressEntity.Relations.UserEntityUsingAddressId, JoinHint.Left);
bucket.Relations.Add(UserEntity.Relations.RecipeEntityUsingUserId, JoinHint.Left);
DataTable table = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, table, bucket, 0, sortExpression, false, groupBy);
}
MY RESULTS:
The problem with this is it doesn’t group the states into one row:
**Name – Type – Count **
Alabama – Appetizer - 4
Alabama – Entree - 1
Alabama – Dessert - 3
Alaska – Appetizer – 6
Alaska – Entree - 8
Alaska – Dessert – 4
etc.
Does anyone know how to get a datatable that looks like my first example above?
Thanks