0

I am writing a web app that has an admin dashboard and that has a summary of all the data in the db.

MSSql is being used here.

Is there a way to get all these data from different tables in one round call ?

I am also concerned about my repository design. I return an IQueryable since it's by no way gonna be efficient to get all the data as IEnumerable in the memory and to perform more filtering/pagin in the middle using extension methods.

Is there a better way to make my repository?

Here is my ViewComponent action (which can be a controller action as well):

public async Task<IViewComponentResult> InvokeAsync()
    {
        var agents = _repository.AgentData.GetAll();
        var applications = _repository.ApplicationData.GetAll();
        var paymentRequests = _repository.PaymentRequestData.GetAll();

        var universityCommissionCalcuator = new CommissionUniversityCalculator(0);
        var commissionCalcuator = new CommissionReferralCalculator(universityCommissionCalcuator);
        var commission = await _repository.AgentData.GetTotalCommissions(applications, commissionCalcuator);

        var result = AdminSummaryMapper.ToViewModel(agents, applications, paymentRequests, commission);

        return View(result);
    }

AdminSummaryMapper:

public static class AdminSummaryMapper
{
    public static AdminSummaryViewModel ToViewModel(IQueryable<Agent> agents, 
                                                    IQueryable<Application> applications, 
                                                    IQueryable<PaymentRequest> paymentRequests,
                                                    Commission commission)
    {
        var result = new AdminSummaryViewModel()
        {
            TotalStudents = applications.Count(),
            ConfirmedStudents = applications.Count(app => app.ApplicationStatus == ApplicationStatus.Confirmed),
            UnderEvaluationStudents = applications.Count(app => app.ApplicationStatus == ApplicationStatus.UnderEvaluation),
            TotalAgents = agents.Count(),
            PaymentRequests = paymentRequests.Count(),
            TotalCommission = commission.TotalComission,
            NetCommission = commission.NetComission,
        };

        return result;
    }
}
Yaser Alnajjar
  • 395
  • 5
  • 18
  • In your repository introduce one method that returns all the data in IEnumerable for the specified page. Use EF to get all the data in the least number of round trips. Do a performance test and if performance is good enough for your needs, then you are done. If not, push the work to a stored procedure and do performance testing again. If not fast enough, analyze the execution plan and find the bottle neck. Use paging. Do not do any premature optimization. – CodingYoshi Dec 31 '17 at 17:09
  • Very wise approach, I have actually tried adding paging into my repository, but it turned out to be a bad option cuz I need filtering before paging... so basically paging should extend an IQueryable. – Yaser Alnajjar Dec 31 '17 at 17:25

3 Answers3

2

You did not mention, which type of database you use with EF (SQLServer, MySql, Oracle, ...).

Retrieving aggregated data from a relational database is quite an easy job with SQL. You could define a view and use subselects like those here: https://www.essentialsql.com/get-ready-to-learn-sql-server-19-introduction-to-sub-queries/

LosWochos
  • 322
  • 1
  • 9
1

If you need to make multiple calls as data is coming from different places and cannot be requested in a single database query, then this is not a problem. Optimise and cache where it makes sense.

Mark Redman
  • 24,079
  • 20
  • 92
  • 147
  • "Optimise and cache" how? – Yaser Alnajjar Dec 31 '17 at 15:50
  • You can review your queries and include single queries where you can, and you can cache by storing the results, for example in session, for a specific time period to reduce round trips to the database. You will need to look up caching techniques available. – Mark Redman Dec 31 '17 at 18:38
  • Session != cache. Please don't recommend session for things like this. ASP.NET Core has actual caching infrastructure you can use, so use that. – Chris Pratt Jan 02 '18 at 16:40
  • Agreed, "You will need to look up caching techniques available" – Mark Redman Jan 03 '18 at 09:48
1

Traditionally, if you want lots of data from one round trip, the answer for SQL Server is a stored procedure. Entity framework probably has a way of directly accessing stored procedures, but keeping to your format, a view could be built on the database from such a stored procedure that pulls data from multiple queries, so each query gets executed server-side, then the results are pulled back in one go.

Directly accessing a stored procedure:

http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

How to call Stored Procedure in Entity Framework 6 (Code-First)?

This requires that the stored procedure manipulate the data so it comes back in one table. Entity Framework really isn't the best way to do this, the real purpose is to make it harder to perform rookie mistakes such as UPDATEs and DELETEs without WHERE clauses while adding auto-complete.

NeedsAnswers
  • 169
  • 5