EF Core header-detail query optimization-CodePudding

I have a header-detail relation in my SQL Server database. I have around 10k headers, each of them having 1-1k details. And the number of unique elements is about 1k.

Elements [id]
1
2
3

Headers [id]
1
2
3

Details [id, header_id, element_id]
1 1 1
2 1 2
3 1 3
4 2 1
5 3 1

It's very easy to query a list of headers with their details with such structure:

var searchHeaderIds = new List<int>{1,2,3};
var headers = context.Headers
                     .Where(h => searchHeaderIds.Contains(h.Id))
                     .Include(h => h.Details)
                     .ToList();

But what I want to query is a list of elements (1-200) where every element has a list of headers it belongs to (something like an inversion). I can write it in C# as below:

public class Element {
   int id { get; set; }
   IEnumerable<int> header_ids { get; set; }
}

var searchElementIds = new List<int>{1,2,3};
var headers = context.Details
                     .Where(d => searchElementIds.Contains(d.element_id))
                     .ToList();
                     .GroupBy(d => d.element_id)
                     .Select(g => new Element() { 
                                       id = g.Key, 
                                       header_ids = g.Select(x => x.header_id)
                     .ToList();

But I wonder, what will be the fastest way to do it using the power of SQL/EF?

UPD: I'm ready to use extra data structures, preprocess the data in the database, or do anything else to improve performance.

CodePudding user response：

what about:

var searchElementIds = new List<int>{1,2,3};
var headers = (
    from header in context.Headers
    join detail in context.Details on header.id equals detail.header_id
    where searchElementIds.Contains(detail.element_id)
    select header).Distinct();

If you want instances of the Element class:

var headers = 
    context.Details
        .Where(d => searchElementIds.Contains(d.element_id))
        .GroupBy(d => d.element_id)
        .Select(g => new Element 
                     { 
                         id = g.Key, 
                         header_ids = g.Select(x => x.header_id
                     })
        .ToList();

Don't cal ToList() in the middle of your query.

CodePudding user response：

This is most optimal query in your case. It is closer to original post, but reduced number of retrieved fields for intermediate result:

var headers = context.Details
    .Where(d => searchElementIds.Contains(d.element_id))
    .Select(d => new { d.element_id, d.header_id })
    .ToList() // we need this, EF do not support retrieving grouping detals
    .GroupBy(d => d.element_id)
    .Select(g => new Element
    { 
        id = g.Key, 
        header_ids = g.Select(x => x.header_id).ToList()
    })
    .ToList();