How do I negotiate joins and groupings based on nested properties in LINQ?

Question

So I've got a nested data structure like this:

public class ContractTerm
{
    public int ContractId { get; set; }
    public string SectionId { get; set; }
    public string SubsectionId { get; set; }
    public string TermId { get; set; }
    public int TermOrder { get; set; }
    public TermItem TermNavigation { get; set; }
}

public class TermItem
{
    public string SectionId { get; set; }
    public string SubsectionId { get; set; }
    public string TermId { get; set; }
    public string Text { get; set; }
    public ICollection<ContractTerm> ContractNavigation { get; set; }
}

I've also got a class to map the section/subsection pairings in a more EF-friendly way (IRL this is an enum with attribute values and a helper, but this class abstracts away some work not necessary to reproduce the issue):

public class Section
{
    public string Name { get; set; }
    public string SectionId { get; set; }
    public string SubsectionId { get; set; }
}

Both ContractTerm and TermItem have their own collections in a DbContext, and I'm trying to get a collection of all text entries assigned to specific Sections for a given ContractId. I have the following class to contain it:

public class TextsBySection
{
    public string SectionName { get; set; }
    public IEnumerable<string> Texts { get; set; }
}

I want to select a collection of TextsBySection, and have something like this:

public class ContractManager
{
    //insert constructor initializing MyContext here

    private MyContext Context { get; }

    public IEnumerable<MyOutputClass> GetTerms(int contractId, IEnumerable<Section> sections)
    {
        Func<string, string, IEnumerable<string>> getBySection =
            (section, subsection) => context.ContractTerms.Include(x => x.TermNavigation)
                                            .Where(x => x.ContractId == contractId
                                                        && x.SectionId == section
                                                        && x.SubsectionId == subsection)
                                            .Select(x => x.TermNavigation.Text);

        var result = sections.Select(x => new MyOutputClass
                              {
                                  SectionName = x.Name,
                                  Texts = getBySection(x.SectionId, x.SubsectionId)
                              }).ToList();

        return result;
    }
}

This works fine and dandy, but it hits the database for every Section. I feel like there's got to be a way to use Join and/or GroupBy to make it only query once, but I can't quite see it. Something like this, perhaps:

var result = context.ContractTerms.Include(x => x.TermNavigation)
                                  .Where(x => x.ContractId == contractId)
                                  .Join(sections,
                                        term => //something
                                        section => //something
                                        (term, section) => /*something*/)

If all this were in SQL, selecting the necessary data would be easy:

SELECT sections.name,
       term_items.text
FROM   contract_terms
JOIN   term_items
ON     term_items.section_id = contract_terms.section_id
AND    term_items.subsection_id = contract_terms.subsection_id
AND    term_items.term_id = contract_terms.term_id
JOIN   sections --not a real table; just corresponds to sections argument in method
ON     sections.section_id = contract_terms.section_id
AND    sections.subsection_id = contract_terms.subsection_id

...and then I could group the results in .NET. But I don't understand how to make a single LINQ query that would do the same thing.

The ORM will generate the JOINs itself *provided you specified the correct relations and navigation properties*. LINQ is *not* SQL, you *don't* convert SQL to LINQ as if they were equivalent. LINQ is a query language that works on top of an ORM. It's the ORM that generates the joins from relations, conditions etc. — Panagiotis Kanavos, Sep 06 '18 at 16:29
As for your code, *you are explicitly executing one query per session* inside that `Select` call. The entire database call is *inside* `getBySection`. That function is called once for every session. — Panagiotis Kanavos, Sep 06 '18 at 16:32
That's what I'm saying. I know there *SHOULD* be a better way to get what I need without having to call multiple queries; I just don't know what it is. — JAF, Sep 06 '18 at 16:36
I updated the question to show what I think I should do; I just don't know how to fill the gaps. — JAF, Sep 06 '18 at 16:45
What version of EF are you using? Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might be helpful? — NetMage, Sep 06 '18 at 17:24
BTW, I would say not providing working SQL and waving your hands about `section` isn't really helpful as that is the entire issue with your LINQ - mixing in-memory `IEnumerable` with in-database `IQueryable`. — NetMage, Sep 06 '18 at 17:32

Carlos H · Accepted Answer · 2018-09-06T20:19:53.167

I changed my answer, well I would do something like this... maybe this may help you.

public static void Main(string[] args)
        {
            List<Section> sections = new List<Section>();
            List<ContractTerm> contractTerms = new List<ContractTerm>();
            List<TermItem> termItens = new List<TermItem>();

            //considering lists have records

            List<TextsBySection> result = (from contractTerm in contractTerms
                          join termItem in termItens
                              on new
                              {
                                  contractTerm.SectionId,
                                  contractTerm.SubsectionId,
                                  contractTerm.TermId
                              }
                              equals new
                              {
                                  termItem.SectionId,
                                  termItem.SubsectionId,
                                  termItem.TermId
                              }
                          join section in sections
                           on new
                           {
                               contractTerm.SectionId,
                               contractTerm.SubsectionId
                           } equals new
                           {
                               section.SectionId,
                               section.SubsectionId
                           }
                          select
                          new
                          {
                              sectionName = section.Name,
                              termItemText = termItem.Text
                          }).GroupBy(x => x.sectionName).Select(x => new TextsBySection()
                          {
                              SectionName = x.Key,
                              Texts = x.Select(i=> i.termItemText)
                          }).ToList();  
        }

How do I negotiate joins and groupings based on nested properties in LINQ?

1 Answers1