Filtering the LoadWith Results

Question

Is there a way to filter a LoadWith in Linq

I currently have ReportCategory and Reports tables. I want to retrieve all the Categories and then only want to load the active reports.

This is what I have so far.

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<ReportCategory>(report => report.Reports);
db.LoadOptions = dlo;

var categories = from c in db.ReportCategory
                where c.InUse == true
                select c;

It is returning all the active categories and all the reports for each category as expected but I dont need all the reports, I only need the ones that are marked as InUse.

So I've tried this...

dlo.LoadWith<ReportCategory>(report => report.Reports.Where(r => r.InUse == true));

but I'm getting the following error.

InvalidOperationException: The expression specified must be of the form p.A, where p is the parameter and A is a property or field member.

Is there a way to do this with a LoadWith or should I just move to using a join?

Answer 1

Found it...

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<ReportCategory>(report => report.Reports);
dlo.AssociateWith<ReportCategory>(r => r.Reports.Where(i => i.InUse == true));
db.LoadOptions = dlo;

That is bringing back all the categories and active reports