Getting subsets from Subsonic Collections

Question

Hey,

I'm using Subsonic Collections to pull a list of timesheet entries from our database and then databinding them to ASP.net user controls I've created on a page. Is it possible to break one large collection into multiple subsets based on a certain parameter?

For example, each of our timesheet entries contain a job code for the particular job they are related to. What I would like to do is pull one large collection of entries for a particular employee by their employeeid and within a specified time range. Then what I'd like to do is segment that collection into multiple smaller collections based on the entries' job code, this way I'd have to freedom to bind different collections to different controls based on the jobcode that the entries share.

It would also be nice since I wouldn't first have to pull a list of job codes the employee has data for, and then run multiple database calls to populate each collection (qry.AddWhere("job_code", job_code); qry.ExecuteReader();)

If anyone knows if this is something that is possible to do please let me know. Any help is greatly appreciated.

Mike

Answer 1

Should be able to do this

Dim bigCollection as TimeSheetCollection
Dim job1Collection as New TimeSheetCollection = Nothing
Dim job2Collection as New TimeSheetCollection = Nothing

bigCollection = New TimeSheetCollection().Where("employeeid",employeeID).Load()
job1Collection = bigCollection.Where("job_code",jc1).Load()
job2Collection = bigCollection.Where("job_code",jc2).Load()

Answer 2

You can use LINQ to slice your big collection and use it as a data source for the grids. Something like this:

var dbResults = new TimesheetCollection()
    .Where(Timesheet.Columns.EmployeeID, empId)
    .Load();

grid1.DataSource = dbResults.Where(t => t.JobCode == jc1);
grid2.DataSource = dbResults.Where(t => t.JobCode == jc2);

This way you're not hitting the database multiple times. You can get the list of job codes for the employee via:

// IEnumerable<int> of jobs
var jobs = dbResults.Select(t => t.JobCode).Distinct();

However, note that the grid DataSources are now IEnumerable<Timesheet> instead of a TimesheetCollection object, if that matters.

Answer 3

Sorry for the delayed response, I didn't set the question up right so I would get notifications of any responses, then I ended up forgetting about the post all together! When I researched the topic again, low and behold I found my question again.

Corey,

Load() seems to pull more records from the database and append them to the collection.

I have a table that contains 14 rows. 5 of the rows have a type of "MEAL", the other 9 have a type of "OTHER". When I run the following code:

Dim allActivities As New OnsScheduleActivityCollection
allActivities.LoadAndCloseReader(OnsScheduleActivity.FetchAll())

Dim justMeals As New OnsScheduleActivityCollection
justMeals = allActivities.Where("ActivityType", "MEAL").Load()

Response.Write(allActivities.Count & "<br />")
Response.Write(justMeals.Count & "<br />")

The response displays: 19 19

When I use Filter() in place of Load() the response displays: 5 5

That would be ok except that I don't actually want to modify the main collection, I just want to get a subset that I store in the new collection (justMeals) but leave the main collection (allActivities) in its original condition My desired result would be to have the response display "14 5". 14 for the number of rows in allActivies and 5 in justMeals.

Is this at all possible?

Mike