Should I ignore repository for a stored procedure that return flat data (multiple objects as a single one)?

Question

I'm using the repository pattern as the way to access the DbContext in my C# application, however, I have a report that generates an expensive query (actually several queries) because of relationships (and poorly written code).

Then I created a Stored Procedure that returns exactly what the report needs, without any extra field, so I can fill the current entities and navigation properties.

In this case, what should be the way to go? Create a new entity/repository just for this report?

Answer 1

Create a new entity/repository just for this report?

New entities, yes, repository, no. But I come to this answer from a slightly different perspective:

I think it's always a good idea to have a separate DbContext subtype for reporting. This specialized context can be optimized for performance by making it read-only:

• Turn off lazy loading and creation of proxies:

  context.Configuration.ProxyCreationEnabled = false;
  

• Turn off change detection:

  context.Configuration.AutoDetectChangesEnabled = false;
  

• (Maybe) turn off validation:

  context.Configuration.ValidateOnSaveEnabled = false;
  

  But that would only be useful if there's a chance that inadvertent SaveChanges calls will be made.

• Disable saving changes by overriding the SaveChanges method without calling base.SaveChanges.

Besides that, if the context also exposes DbSet<T> properties, you should use them with AsNoTracking(), which will prevent entities from being change tracked.

If you have such a context, it's more obvious to use it with specialized entities, not the entities that belong to the other, business-related context(s). This can be entities that exactly capture the result of a stored procedure. It's also possible to populate multiple entities by one stored procedure.

W.r.t. repositories -- I'm not a fan of them anyway. Repositories, when used with EF, should be generic repositories. Generic repositories don't contain any type-specific methods, so they always end up being a thin (i.e. virtually useless) wrapper around DbSets. You may as well work with DbSets directly (or with the output of stored procedures).

One other thing to mention: even with these optimizations, EF is not the fastest tool to read data from a database. You may want to look at Dapper.