LINQ to SQL filter child collection

Question

I'm strugling with this query, i think I'm missing something. I have two autogenerated dbml models.

    public partial class RegulatorsOrganizationView
    {
        private int regulatorOrgId;
        private string regulatorOrgName;
        private EntitySet<RegulatorsView> regulatorsViews;
    }

    public partial class RegulatorsView
    {
        private int regulatorId;
        private string regulatorName;
    }

• I need to apply filtering by name, input string "filterText" should be a part of regulatorName
• If regulator is not matching - should be filtered out from regulatorsViews
• If regulatorOrganizationView have at least one match in regulatorsViews - should be included
• If regulatorsViews collection of regulatorOrganizationView does not have regulators that match condition, but it's name contains filterText - it should be included.

Currently I'm loading all the matching regualatorsOrganizationViews, and do filtering on regulators down the line.

List<RegulatorOrganizationView> regOrgs = boatDataContext.RegulatorOrganizationView
                .Where(r => r.RegulatorsViews.Any(ar => ar.regulatorName.ToUpper().Contains(filterText.ToUpper()))
                || r.regulatorName.ToUpper().Contains(filterText.ToUpper())
                .ToList();

         

But this way I'm loading redundent Regulators only to filter them out later on. How can I rebuild this query to load only matching regulators from starters ?

It tried to use Select() to assign regulatorOrgnization filter list of Regulators.

            regulatorsOrgs = DataContext.RegulatorOrganizationViews
            .Where(ro => ro.regulatorOrgName.ToUpper().Contains(filterText.ToUpper())
            || ro.RegulatorsViews.Any(r => r.regulatorName.ToUpper().Contains(filterText.ToUpper()))
            .Select(ro => new RegulatorOrganizationView()
            {
                regulatorId = ro.regulatorId,
                regulatorOrgName = ro.regulatorOrgName,

                RegulatorsViews = ro.RegulatorsViews
                         .Where(r => r.regulatorName.ToUpper().Contains(filterText.ToUpper())
                         .Select(r => new RegulatorsView()
                         {
                             regulatorId = r.regulatorId,
                             regulatorName = r.regulatorName,
                         }).ToEntitySet()
            
            }).ToList();

But I'm getting exception: Message="The explicit construction of the entity type 'RegulatorsOrganizationView' in a query is not allowed."

Looks like filtered Include() would be an option (like in EF) but I can't find a way to use it with Linq To SQL. Any ideas ?

Answer 1

In LINQ-to-SQL it's a bit messy and not intuitive to do this. You have to use DataLoadOptions:

var opt = new DataLoadOptions();

opt.AssociateWith((RegulatorsOrganizationView v) 
    => v.regulatorsViews.Where(ar => ar.regulatorName.Contains(filterText)));

opt.LoadWith((RegulatorsOrganizationView v) => => v.regulatorsViews);

DataContext.LoadOptions = opt;

var result = DataContext.RegulatorOrganizationViews
            .Where(ro => ro.regulatorOrgName.Contains(filterText) 
                      && ro.regulatorsViews.Any());

So this says: when loading RegulatorOrganizationViews, then when their regulatorsViews are associated, make them meet the given condition.

Then it says: when when loading RegulatorOrganizationViews, also load their regulatorsViews.

The latter is like Include in Entity Framework. The former makes it behave like filtered Include, or maybe closer, a global query filter.

I removed the ToUpper calls for brevity, but you don't need them if the database collation is case-insensitive.