.NET Linq query for one-to-many relationsiops-CodePudding

The example below shows a simple one-to-many relationship between customer and invoices. The LINQ query retrieves all invoices and the related customer. I just wanted to get an opinion on the efficiency of the query, since there is like a recursion where an invoice has a customer, and the customer has many invoices, and each invoice has a customer, etc. Also, in read-only scenarios am I ok to use AsNoTracking() ?

public class Customer
{
    public int CustomerID { get; set; }
    public string CustomerName { get; set; }

    [ForeignKey("CustomerID")]
    [NotMapped]
    public virtual IList<Invoice> Invoices { get; set; }
}

public class Invoice
{
    public int InvoiceID { get; set; }
    public int CustomerID { get; set; }

        [ForeignKey("CustomerID")]
        public Customer Customer { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    //........
         builder.Entity<Invoice>().ToTable("Invoices")
            .HasOne(s => s.Customer).WithMany(c => c.Invoices)
            .OnDelete(DeleteBehavior.Restrict);
}

public class SomeClass
{
    //....
    List<Invoice> invList = await context.Invoices.Include(x => x.Customer).toListAsync();
}

CodePudding user response：

I don't think there's a recursion per se but there might be when you're looking at it, just because EF will link related objects up in both directions

The query will translate to something simple like

SELECT * FROM Invoices JOIN Customers ON ...

Which causes a repetition of customer info in the result set for customers that have more than one invoice, but as EF is mapping them to objects (creating instances of Invoice and Customer as it enumerates the results), if it encounters customer data it has seen before it will use the Customer entity it knows about already.

This means if you take a look in the debugger and start expanding the invList in the Locals window, you'll get e.g. an Invoice ID 1, which is an object at memory address 0x01 that has a Customer ID 1 which is an object at memory address 0x82and you'll see that this customer has 20 Invoices. If you find Invoice ID 1 in that list of 20 it will be the object at memory address 0x01. If you expand it and look at its Customer, it will be the Customer at memory address 0x82, which has a list of 20 inoices.. and so on and so on.. You can keep expanding them forever because you're just cycling back and forth between the same two object instances, and there's only one Customer for those 20 Invoices. It's not recursively generating ever more data.

Where you have to be more careful in terms of data loading, is with AsNoTracking; if you turn off tracking entirely then repetitions in the database customer data will cause new Customer instances to be generated all the time, even if they have the same key as a previously seen instance. In that case expanding the object graph in the locals window would see Invoice 1, mem address 0x03 having Customer 1, mem address 0x85 who has a single invoice, which is Invoice 1, mem address 0x03. You could then expand Invoice 2, mem address 0x05 and see Customer 1, mem address 0x89 - same customer details but a different object instance (0x85 vx 0x89) to before

In reality the human buyer does have 20 invoices associated with them but in your object graph there are 20 different Customer instances of the same customer data, that all have an invoices collection bearing one Invoice each

If you want to avoid this instance explosion but not track data changes for a read only scenario, you can look at AsNoTrackingWithIdentityResolution to get a "one invoice with one customer with 20 invoices..." shaped graph, but not tracking old/new values for UPDATE query purposes