1

I have an IQueryable of a complex EF model, let's call it GeneralForm. This GeneralForm entity aggregates a member called Section. The Section contains a list of FormFields and each FormField has a name. I want to select only the FormFields whose names are in a list of given names.

IQueryable<GeneralForm> query = InitializeMyQuery();

What is the correct "Where" clause to do so. something like this:

if (criteria.FormFieldNames.Any())
{
   query = query.Where(gf => gf.Section.FormFields.Where(x => criteria.FormFieldNames.Contains(x.FormField.FieldName)).Any());
}

does not work, as it still retrieves all FormFields, not just the ones I want.

Any suggestion would be highly appreciated.

Thanks, Ed

Edit 1: This is how the query is built (for privacy reason, I renamed some entities and I also removed the ones that do not really pertain to the issue I am trying to resolve):

 query = (from genFormEntry in _context.GeneralForms
                            .Include(r => r.Sections)
                            .Include(r => r.Form.FormFields)
                            .Include(r => r.Form.FormFields.Select(x => x.FormField))
                     select genFormEntry);
Eddie
  • 271
  • 4
  • 18
  • "I want to select only the FormFields whose names ..." <-- Do you want _just_ the `FormFields`, or do you want to keep all the `GeneralForms` with their respective `Section`, but filter each `Section`'s `FormFields`? (Judging by your code example, it looks like the latter, but the phrasing made me wonder) – Astrid E. Mar 14 '22 at 20:15
  • 1
    Hi @Astrid, thanks for reaching out. Yes, I want to select a list of GeneralForms, with their corresponding Section and within the Section subfield (child) I want to select only The FormFields that have their name appear in my FormFieldNames list which is passed inside the selection criteria. So you were right, it is the latter. – Eddie Mar 14 '22 at 20:30

1 Answers1

0

This query retrieves Sections that have any matching form name. It doesn't do any filtering on the FormField side.

You may try to join those tables manually, or depending on your Ef version, you can try using filtered includes: 

if (criteria.FormFieldNames.Any())
{
   query = query
          .Include(gf => gf.Section.FormFields.Where(x => criteria.FormFieldNames.Contains(x.FormField.FieldName)) // Include the FormFields that match the criteria
          .Where(gf => gf.Section.FormFields.Where(x => criteria.FormFieldNames.Contains(x.FormField.FieldName)).Any());
}

Edit:

As Ef 6.1 doesn't support filtered includes. Only two options left. 1 is mentioned above which is manual linq joins (which is pretty ugly and not versatile) and the other is to rewrite the query like below :

// guessing navigation property names here.
query = _context.FormFields.Include(r => r.Form.Section.GeneralForm); 

// and later in your code
if (criteria.FormFieldNames.Any())
{
   query = query.Where(f => criteria.FormFieldNames.Contains(f.FieldName));
}
Eldar
  • 9,781
  • 2
  • 10
  • 35
  • Hi @Eldar, thanks for trying to answer my question. I am not sure I fully understand your suggestion. I edited the question to include the code that builds the query. – Eddie Mar 14 '22 at 20:02
  • @Eddie i saw your code and question before I wrote this answer. You are filtering the `GeneralForm` table according to form names and not filtering the navigation property `FormFields` – Eldar Mar 14 '22 at 20:14
  • Yes, I figured that, hence my question. How do I do it right? Adding the "where" clause in inside the "Include" call triggers a System.ArgumentException: The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path – Eddie Mar 14 '22 at 20:27
  • @Eddie that means you are using an EF version that doesn't support filtered includes. That leaves you two options you either do manual joins or change your result set into `FormFields` but include the `Section` and `GeneralForm` navigation properties. – Eldar Mar 14 '22 at 20:32
  • The project uses EntityFramework for .NET v6.1.3. Can you give me more details regarding "change your result set into FormFields but include the Section and GeneralForm navigation properties"? Are you suggesting a projection on the Section member where only the desired FormFields would be retained? Sorry, I am trying to understand what you suggest. Thanks. – Eddie Mar 14 '22 at 20:45
  • I'm not sure EF6.1 will do filtered include without help; Eldar's suggestion seems to be EFCore 5+ (different thing entirely). See something like https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework – Caius Jard Mar 14 '22 at 20:51
  • @Eddie check the updated answer, added a sample. – Eldar Mar 14 '22 at 20:58
  • Hi @Eldar, the query you have in your updated answer, is that a new one or do you recommend I add that include into my query? – Eddie Mar 14 '22 at 22:05
  • @Eddie It's a new one. It changes the result set of your query from `GeneralForm`s to `FormField`s – Eldar Mar 14 '22 at 22:08
  • Hi @Eldar, maybe I am missing something or maybe I have not explained properly. The code I posted is only a fraction of the stuff that's happening in the application I am trying to fix. My original query has a lot more ".Include" added to it that bring data from 8 or 9 different tables. I cannot simply replace it. I need to adapt it to, so that it does what I need. The issue I try to fix is a performance issue where it has been noted that the repo and the Web API are returning too many FormFields, the majority of them not being used anywhere. – Eddie Mar 15 '22 at 01:00
  • Well, @Eddie if the performance is the main issue, then you should definitely change how you organize your query. Probably you should split the query into multiple subqueries and then merge them into the result. – Eldar Mar 15 '22 at 08:34
  • @Eldar - This is a legacy system and (AFAIK) there is no budget for major overhauls at this time. There's only room for small fixes, here and there. – Eddie Mar 15 '22 at 12:15