Monday, December 3, 2007

NetTiers "SELECT DISTINCT" method

Using Nettiers CRUD methods and custom methods we get a very complete DAL that covers a wide range of the queries needed by a application. A frequently question in forums is about how obtain a "Select Distinct" clause equivalent in Nettiers. I've developed a method to obtain the distinct entities based on the values of one or more properties.

public static TList<T> SelectDistinctFromList<T>(TList<T> originalList, System.Enum[] distinctProperties) where T : IEntity, new()
Type instanceType = originalList.GetType().GetGenericArguments()[0].BaseType;

PropertyInfo[] propertyColumns = new PropertyInfo[distinctProperties.Length];
for(int i = 0; i < distinctProperties.Length; i++)
propertyColumns[i] = instanceType.GetProperty(distinctProperties[i].ToString());

TList<T> resultList = new TList<T>();
System.Collections.Specialized.StringCollection keys = new System.Collections.Specialized.StringCollection();
foreach (T instance in originalList)
string key = string.Empty;
foreach (PropertyInfo pi in propertyColumns)
key += pi.GetValue(instance, null) + "|";
if (!keys.Contains(key))
return resultList;

As you can see there are two steps to get the "Select Distinct", in the first step is retrieve the base collection (TList) and in the second step apply the SelectDistinctFromList method.
This approach is adequate when the first step data is cached, have not a big collection or want to do many "Select Distinct" over the collection. For example, using a Nettiers project generated from
the AdventureWorks DB first obtains the orders for a SalesPerson and after get a list of orders with the distinct Territories and other list with the distinct Customers and Status.

SalesOrderHeaderService service = new SalesOrderHeaderService();
TList<SalesOrderHeader> orders = service.GetBySalesPersonId(279);

TList<SalesOrderHeader> distinctTerritoriesFromOrders = SelectDistinctFromList<SalesOrderHeader>(orders, new Enum[] { SalesOrderHeaderColumn.TerritoryId });

TList<SalesOrderHeader> distinctCustomerAndStatusFromOrders = SelectDistinctFromList<SalesOrderHeader>(orders, new Enum[] { SalesOrderHeaderColumn.CustomerId, SalesOrderHeaderColumn.Status });