Walking into an antipattern? Fetching data multiple FKs many layers deep ...

Question

User-146510594 posted

Using SQL and EF Core, I need to efficiently fetch data scattered across many tables.

There are ~50 tables, with many tables having multiple FK relationships, and the layers of FK relationships run up to about 5 layers deep. Almost all are one-to-many where "many" is less than 10. There is one table that all FK relationships eventually flow back to, and which defines the subset of data (<4MB) that a discrete set of calculations works on (consider it a unit of work in the business domain) .

Simplistically I could load that one parent entity and  -- via a ridiculous number of .Include() and .ThenInclude() LINQ statements -- I could get all the dependent data I need. I could run the calculations, make sure everything is okay, and then SaveChanges() to commit a coherent and correct unit of work back to the database.

However the ThenInclude() approach explodes into slow queries shockingly quickly, and so I find myself calling context.Entry(Foo).Collection(x => x.Bars).Load() many times within loops to fetch data just before it is needed.  All those small trips to the database are making me queasy, although so far in my dev-scale database it seems to be working fine. Perhaps is spreads out the load nicely. (Famous last words, I fear.)

• Do you have any EF advice (relative to my ThenInclude() and Load() mentions) for efficiently fetching data scattered across dozens of tables to build my scope of calculations?
• Thinking ahead to Azure SQL latency and async/await, how should I adjust my approach?

Thanks in advance. 

Answer 1

User-821857111 posted

If you can't produce LINQ that translates to efficient SQL, create a view or procedure in the database. Call that instead. 

Answer 2

User1120430333 posted

You could look into the DAO and DTO patterns. A DAO is used on a per table basis. So there would be 50 DAO(s) for the 50 tables with each DAO responsible for CRUD using EF.

Data Access Object (DAO) design pattern in Java - Tutorial Example (javarevisited.blogspot.com)

The DTO allows you to shape the data  for CRUD like a parent DTO carrying its child DTO or children DTO(s) in a collection. A parent DAO would load the parent DTO properties,  and the parent DAO calls out to its child DAO with the child DTO or collection of child DTO(s) loaded into a parent DTO from a child DAO.

You could then do the reverse and call a parent DAO passing the parent DTO with all of its children DTO(s) and call the related child DTO's DAO to persist its data.

Data Transfer Object Design Pattern in C# - CodeProject

Example of DAO and DTO patterns being used in the Data Access Layer.

PubCompanyCore3.x/DAL at master · darnold924/PubCompanyCore3.x (github.com)

PubCompanyCore3.x/Entities at master · darnold924/PubCompanyCore3.x (github.com)

HTH

Answer 3

User-146510594 posted

Thanks, Mike. That sounds like good advice, but with all the deep relationships I'd be navigating are you intending that I'd not use EF?

From here I see that using EF to track or persist changes does not seem feasible:

There are some limitations on the execution of database stored procedures using FromSql or ExecuteSqlCommand methods in EF Core2:

1. 1. Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity.
   2. Result cannot contain related data. This means that a stored procedure cannot perform JOINs to formulate the result.
   3. Insert, Update and Delete procedures cannot be mapped with the entity, so the SaveChanges method cannot call stored procedures for CUD operations.

Answer 4

User-146510594 posted

Thanks, DA. If I follow correctly the DAO is the class-per-table model that EF Core reverse-engineers from the database?

And in my case I'm not building DTOs to send over the wire, but I would do something similar to build up data structures conducive to the calculations - call them DCO's. And just like DTO's we would have the initial stage of building the DCOs from the DAOs, and then doing the calculations, and then updating the DAOs from the DCOs.  I'm resigned to that grunt work at each end, given the demands of the calculations. The key will be to navigate all those deep FK relationships efficiently to only load what is needed within each DAO for the job at hand.

Answer 5

User-821857111 posted

IW.Net

are you intending that I'd not use EF?

Correct - if you can't get EF to perform acceptably, then don't use it. Use another strategy for that particular requirement. EF will be a great choice for most simple and moderately complex tasks and will be the best option for the majority of your application's requirements, but you should not be wedded to it. It is just a tool. You can use plain ADO.NET as well as EF in the same application.

Answer 6

User1120430333 posted

Thanks, DA. If I follow correctly the DAO is the class-per-table model that EF Core reverse-engineers from the database?

The DAO(s) are the classes you make that do the CRUD operations using the underlying database technology which could be nHibernate using Oracle, Entity Framework using MS SQL Server, MySQL using straight up ADO.NET, MySQL Command objects and inline T-SQL, etc. and etc.

The Data Access Object pattern is a design pattern.

Data access object - Wikipedia

And in my case I'm not building DTOs to send over the wire, but I would do something similar to build up data structures conducive to the calculations - call them DCO's. And just like DTO's we would have the initial stage of building the DCOs from the DAOs, and then doing the calculations, and then updating the DAOs from the DCOs.  I'm resigned to that grunt work at each end, given the demands of the calculations. The key will be to navigate all those deep FK relationships efficiently to only load what is needed within each DAO for the job at hand.

DTO is just a simple data container that doesn't have behavior, but its purpose is more than just traveling  over the wire now of days. The DTO can travel between layers. And the DTO can be used to shape the data.

Create Data Transfer Objects (DTOs) | Microsoft Docs

The DOC(s) as you call them for your purpose of calculations are acted upon by the DAO for CRUD,   the DOC can be a data container object and it can have methods/behavior for calculations acted upon by business objects for calculations as an example.  The DOC can be used as a data container to shape the data as well.

But the purpose of the DAO is to do CRUD with the underlying database technology and using the DOC to shape the data like a DTO. The DOC in a way is a smart DTO with the DOC having behavior. You can also look at the DOC as an abstraction away from the underlying database technology like a DTO is an abstraction away from the underlying database technology.