1

I have an ASP.NET Web API project, and have implemented repository and unitOfWork patterns.

I currently have a big SQL query that I want to implement in code, it includes a lot of tables and joins.

My current solution is just fetching data from repositories one by one, returning an in-memory value or IEnumerable (not an IQueryable as it defeats the purpose of repositories) and just joining them later and projecting them to a DTO.

But this makes 20 round trips to the database for 20 tables I'm using, and it's very inefficient.

How can I improve performance (mainly focused on lowering number of round trips to database) by following repository pattern?

Improve this question
11
  • 2
    and have implemented repository and unitOfWork patterns - why?
    – Svyatoslav Danyliv
    Commented Jun 29 at 18:52
  • 3
    1) reusability - you can create extension methods. 2) testability - create in-memory Sqlite database and test it simple and easy. 3) separation... - that's what is performance gap in using ORM like EF using generic repository pattern. DbSet is already Repository, DbContext is already UoW.
    – Svyatoslav Danyliv
    Commented Jun 29 at 19:09
  • 2
    Well, I know LINQ and query translation very well. If you do not expose IQueryable from your repositories, effective joins are not possible.
    – Svyatoslav Danyliv
    Commented Jun 29 at 19:33
  • 5
    A repository shouldn't be 'per table', it should be 'per aggregate root object'. If a table cannot exist without another one (e.g. a school class has a teacher and some students), then the repository should be able return a class and it's students in one query. You should not have to query multiple times to retrieve related data (e.g. 1 query to get class, then another to retreive it's teacher and another to retrieve the students) .
    – Neil
    Commented Jun 29 at 19:41
  • 1
    I don't understand this question. It's your own choice to tie your hands behind your back. Then don't come here complaining about impediments and refusing help.
    – Gert Arnold
    Commented Jun 30 at 6:57

2 Answers 2

Reset to default
0

Your repositories are implemented at the wrong layer.

I currently have a big SQL query that I want to implement in code, it includes a lot of tables and joins.

Then there should be a single repository method that returns the result of that query. Although in EF it would normally be shaped with multiple related objects, instead of a big flat query result.

A repository shouldn't be 'per table', it should be 'per aggregate root object'. If a table cannot exist without another one (e.g. a school class has a teacher and some students), then the repository should be able return a class and it's students in one query. You should not have to query multiple times to retrieve related data (e.g. 1 query to get class, then another to retreive it's teacher and another to retrieve the students)

Improve this answer
1
  • This. When you look at implementing a repository pattern with EF you absolutely do not want a Generic repository pattern. EF already provides a generic repository through DbSet<T>. Repositories at an aggregate root level can help organize data access for common use, enforce common low-level domain rules, (Authorization, soft-delete, etc.) and help simplify unit testing. I implement repositories for these reasons at an aggregate root level, and leverage IQueryable to empower consumers to use projection, sorting, pagination, etc. It enforces core standard rules and is easier to mock out.
    – Steve Py
    Commented Jun 30 at 22:01
-1

I don't think you can say "I need to change the design of this without changing the patterns I'm using".

I think you might have a boundary issue if you're doing 20 queries that you can't do in 1 Batch.

Solution 1: Write SQL

SELECT top 10 * FROM database1.dbo.Users;
SELECT top 10 * FROM database2.dbo.Users;
SELECT top 10 * FROM database3.dbo.Users;
SELECT top 10 * FROM database4.dbo.Users;

Execute this as one sproc or command and parse each result into your DTOs.

If you're in MS SQL Server land you can use Linked Servers to cross boundaries. I don't think this is a great idea, but pushes this logic into the DB side and makes it 1 round trip as far as the application is concerns.

Solution 2: Do them concurrently in a Manager or Helper

Your manager will control getting data from your Repositories.

public class CrossDomainHelper
{
    public DTO.User[] Load()
    {
        IRepo[] rr = new IRepo[]{new RepoA(), new RepoB(), new RepoC(), new RepoD()… new RepoX()};
        var tasksToRun = rr.Select( r => r.Get10UsersAsync() ); // Do not await here
        var repoCompletions = await Task.WhenAll(tasksToRun);
        foreach(var c in repoCompletions)
        {
            DTO.User[] users = c;
            // Do something with them
        }
    }
}

Solution 3: You need a DataWarehouse or some sort of Aggregation

If you're doing a bunch of queries all over the place that should be part of a single process, then create a single place for that.

Another SQL Server database that collects this information. Maybe events in that update it's state. The structure of this should be suited to the business question you are answering.

You could use CDC to create the events. Or if you already have a system with an aggregate simply read those events and re-build something more suited to what you really want to query.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.