1

I have three tables in EF Core code-first database which have a one-to-many-to-one relationship:

public class Operator
{
    [Key]
    public string Username { get; set; }
    public string PhoneNumber { get; set; }

    public List<Ticket> Tickets { get; set; }
}

public class Ticket
{
    public int TicketId { get; set; }
    public DateTime Date { get; set; }
    public int LotNumber { get; set; }
    public enum TicketStatus { get; set; }

    // navigation properties
    public Specification Specification { get; set; }
    
    // foreign keys
    public string OperatorUsername { get; set; }
    public int SpecificationId { get; set; }
}

public class Specification
{
    public int SpecificationId { get; set; }
    public int Wages { get; set; }
    public string ColorName { get; set; }
    public string SizeName { get; set; }
    public string Description { get; set; }
}

As you can see, one operators can have many tickets, also one specification can have many tickets.

However in one specification we can have hundreds to thousands of tickets. The problem is right now I want to fetch data using EF Core starting from operators to check for all the the tickets that the operator has attended and what is the specification for the tickets.

So this is inside my controller right now.

public Task<Operator> GetOperatorWithTickets(Username)
{
    return await context.Operator
                        .Include(x => x.Tickets)
                        .ThenInclude(y => y.Specification)
                        .FirstOrDefaultAsync(c => c.Username == Username);
}

However after inspecting the query that EF Core generated, I find that EF Core uses join query to get all three tables together, and I think it might not be very efficient to keep scanning table Specification for every single tickets in join query.

I am considering this method using FindAsync to reuse existing tracked Specification entities

public Task<Operator> GetOperatorWithTickets(Username)
{
    Operator Operator await context.Operator
                                   .Include(x => x.Tickets)
                                   .FirstOrDefaultAsync(c => c.Username == Username);
 
    foreach (Ticket ticket in Operator.Tickets)
    {
         ticket.Specification = await context.ticket.FindAsync(ticket.SpecificationId);
    }

    return Operator;
}

I believe second method can reduce database overhead because database scan for table Specification will only be performed when the entity Specification is not yet retrieve for all previous any tickets, else the existing entities will be used again and again for hundreds or even thousands of tickets that share same specification.

I am still a novice and not sure about all the caching that database might performs to reduce the overhead in first method, so in my view second method should outperforms the first method. What is your view on this? Should I stick with the first methods or migrate to second methods?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fitri Halim
  • 584
  • 4
  • 11
  • 2
    [Examine the generated SQL](https://stackoverflow.com/questions/37527783/get-sql-code-from-an-entity-framework-core-iqueryablet), and then decide which way is better. – Robert Harvey Sep 30 '21 at 15:13
  • firstly check that efficient indices are set in the db, and consider using a db View, or temp tables to narrow the intersting data – Romka Sep 30 '21 at 15:39
  • I didn't get what you need and why you have written second variant? Your first query is perfect. If you have problems with performance, try `AsSplitQuery()`. – Svyatoslav Danyliv Sep 30 '21 at 15:56
  • @SvyatoslavDanyliv Thank you for replying.. Ya currently my controller is working great, just that from the generated sql I find out that ef core generate a join sql query for all these three tables, thus might causes unnecessary scan on the third tables that will impact performance. – Fitri Halim Oct 01 '21 at 06:12
  • @RobertHarvey Owh that will be great, thank you sir – Fitri Halim Oct 01 '21 at 06:13
  • @NurFitriAbdHalim, you retrieve data from 3 tables, for sure you need joins, at least 2. You second variant is bad idea. – Svyatoslav Danyliv Oct 01 '21 at 10:01
  • Ok sure I get it now @SvyatoslavDanyliv. Thanks for the advice! – Fitri Halim Oct 01 '21 at 17:09

1 Answers1

0

After reading some comments, I believe database catching will automatically improve the data retrieval using first method, thus even though same data will be retrieve again and again, but database catching will help to reduce the overhead and significantly improve database speed on join methods.

Fitri Halim
  • 584
  • 4
  • 11
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 03 '21 at 17:54