DeepSearch?

I would love to hear what people think would be best practice for the following scenario:

Imagine you have three tables: Employees, Subscriptions and Courses. An employee is a one to many relation to Subscriptions, and Subscriptions is a one to many relation to Courses.

How would you search for the employees that have completed Courses Where Courses.Name LIKE 'x' OR Course.Name LIKE 'y' returning a list of Deeploaded TList<Employees>?

EmployeeService.Find takes parameters for searching within the Employee object, not within the child relations, or am I mistaken?

I thought I might be able to do this in reverse, hence get the Courses and work backwards, but I couldn't see how to reference the Subscriptions collection parent:

TList<Courses> courses = CoursesService.Find("Name = 'x' OR Name = 'y'");

If that isn't enough I would like to add another search parameter Where Employees.Surname LIKE 'z'.

I think I'm going to answer myself, but a custom SP would seem to be the way forward. Thoughts?

Regards

Ben

Ben,

For complex search scenarios we hand code custom stored procs.

Alternatively you could also consider joining all of the tables you want to search in a View and then generate code for that View.

hth

swin 

Hi,

Thanks for the quick reply. I faiyl quickly realised that my only option was to write a custom stored procedure and so went ahead and did it. It wasn't too painful at all, and I now have it successfully passing back an entity collection. My problem now lies with paging.

Essentially, what do I need to do in my custom stored procedure so that the .netTiers templates will translate my stored procedure (with paging) into the appropriate method for supporting paging with the EntityGridView? I'd like to support a call to either all or paged results, as an overloaded method, as per the standard .netTiers table entities. Do I need to have two custom SPs, one for the equivalent of GetAll, and the other as GetPaged? I'm basically implementing something like: _MyEntity_Search_List, _MyEntity_SearchPaged. What are the templates looking for when they generate methods from custom stored procedures so that it understands that one method is returning a basic list and another a paged dataset, or is it not that smart and I need to pass it specific parameters in the datasource declaration that are mimicking the paged method calls?

BTW, I love .netTiers. Awesome work by all who have been involved.

Regards

Ben

The way I do it is add a @rowCount output parameter in my custom sprocs.

Then when I use the datasource, specifiy CustomMethodRecordCountParamName="rowCount"  (or whatever the output param is named).

I'm not doing the fancy paging yet that the netTiers GetPaged sprocs do.  I'll worry about that later.

You rock! I've been round and round with this problem, and then finally came back to your post. This is a great way to do this. Note to self to read property names properly. For some reason I assumed it was a method name and couldn't figure out why it always returned a null value.

I can go to bed now and stop dreaming about EntityDataSources!