Select query to match on parent category OR sub-category

Hi

I need to construct a query that will return all "Job Descriptions" that have the same industry OR industry sub-category(s) as a given business. Businesses can have multiple industries but Job Descriptions only have one. Some industries have sub-categories, some don't. If a Job Description's industry has sub-categories then I only want to return that Job Description for the given business if the business has the same industry sub-category(s). Another Job Description might have an industry that doesn't have sub-categories and I need to return that too if the business has that industry.

For example, let's say the logged in business has two industries - Beauty Therapy (no sub-categories) and Fitness (4 sub-categories of which the business has 2: Personal Training and Tennis Coaching). I basically want to display a grid to the business user of all Job Descriptions that have industry "Beauty Therapy" or have industry sub-categories Personal Training and/or Tennis Coaching. But not Job Descriptions that have industry Fitness is the sub-category(s) aren't Personal Training or Tennis Coaching.

What should that Linq or Lightspeed query look like?

Thanks for advice.

Depending on how you have modelled this, presumably something like

Business.Industries.SelectMany(i => i.JobDescriptions) union Business.Industries.SelectMany(i => i.SubCategories.SelectMany(c => c.JobDescriptions)) 

with some sort occuring post selection would be what you are after?

If you can you send through a small program containing your model, some test data and your expected result then I can have a quick look at the specifics for you.

Thanks!
Jeremy

Thanks Jeremy - that looks promising. A couple of things (which are probably easier to ask about than creating a demo project), the keyword "union" doesn't seem to be recognised - the VS compiler says "; expected". Do I need the nightly build?

I assume in your example that "Business" would be an instantiated variable of type "Business" - yes? And the line would look like:

var results = theBiz.Industries.SelectMany(i => i.JobDescriptions) union theBiz.Industries.SelectMany(i => i.IndustrySubCats.SelectMany(c => c.JobDescriptions));

Thanks!

Ok, I had a fiddle and answered those questions myself.

Here's what I ended up with:

var nonSubCatIndustries = theBiz.Industries.Where(i => i.IndustrySubCats.Count == 0);

var results = theBiz.Industries.SelectMany(i => i.JobDescriptions).Union( theBiz.IndustrySubCats.SelectMany(i => i.JobDescriptions)) .OrderByDescending(job => job.DateAdded);

I added the first line to isolate the industries that don't have sub-categories (otherwise job descriptions with sub-categories will match on the industry regardless of whether they have matching sub-categories or not). Then sussed out the union syntax and it does just what I need.

Thanks a lot for pointing me in the right direction Jeremy.

Also...

JobDescriptions has a many to one relationship with SalaryBands. How would you modify the above to get SalaryBands.Name for each JobDescription (as well as all the JobDescription cols as it does currenctly)?

I am binding this to a grid, at present it works fine for displaying the JobDescription details but I have added a column to display the salary band so I want that in the result set too.

Thanks!

Keep in mind you are selecting out a LightSpeed entity in your query above, so you can just access .SalaryBands.Name on the JobDescription instance. For performance you will want to ensure that SalaryBands is eager loaded (select Eager Load Backreference on the properties for the association in the Designer) to avoid it lazy-loading each SalaryBands instance as you enumerate your JobDescription instances.

In your bindings for your grid presumably you are just going to bind jobDescriptionInstance.SalaryBands.Name?

Jeremy

Ah, yes of course, thanks.