I wanted to minimize number of calls to the database, but get back results of dynamic query for small number of columns, as well as detailed data(e.g Itinerary table and Itinerary joined with Itinerary items) .In EntitySpaces
I've created a where condition, filled Query and loaded detailed data, then saved the returned table to DataSet.
Then for the same query I've specified distinct select columns and Load it again(When LINQ will be available, it could be done in memory without extra database call). Different table was created, that I've added to the same dataset. Below is code snippet:
VwItinerariesCollection collPendingList = new VwItinerariesCollection();
VwItinerariesQuery queryFilter = collPendingList.Query;
queryFilter.AddWhereConditions();//custom code
collPendingList.Query.Load();
DataTable tbl= collPendingList.ConvertToDataTable();
tbl.TableName = "ListWithDetailsTable";
DataSet itineraries = new DataSet();
itineraries.Tables.Add(tbl);
queryFilter.DistinctSelectForList();//custom code
collPendingList.Query.Load();
tbl = collPendingList.ConvertToDataTable();
tbl.TableName = "ListTable";
itineraries.Tables.Add(tbl);