Optimizing related data queries with a View Model

Question

I have a Kendo UI MVC Core Orders grid with a MultiSelectFor column which can have multiple customers. All is working fine but I have more than 2000 records so it takes really too long to display my list because of the related data that needs to be fetched from the many-to-many table.

I am using database first with EF so I scaffold the db model.

Here is the model of the Orders table generated by EF (I have removed unnecessary code):

public partial class Orders
{
    public Orders()
    {
        OrdersCustomers = new HashSet<OrdersCustomers>();
    }
    public int JobNo { get; set; }
    public ICollection<OrdersCustomers> OrdersCustomers { get; set; }
}

My view model OrdersVM (View Model):

public class OrdersVM
{
    public int JobNo { get; set; }

    [UIHint("OrdersCustomersET")]
    [Required(ErrorMessage = "A Customer is required")]
    public IEnumerable<OrdersCustomersVM> OrdersCustomers { get; set; }
}

EF generated OrdersCustomers code:

 public partial class OrdersCustomers
{
    public int JobNo { get; set; }
    public string ClientId { get; set; }
    public Customers Client { get; set; }
    public Orders JobNoNavigation { get; set; }
}

My OrdersCustomers View Model:

public class OrdersCustomersVM
{

    public int JobNo { get; set; }
    public string ClientName { get; set; }
    public string ClientId { get; set; }
}

My controller:

public IActionResult Read([DataSourceRequest] DataSourceRequest request)
    {
        var List = _context.Orders
            .Select(c => new OrdersVM
            {
                JobNo = c.JobNo,
                OrdersCustomers = c.OrdersCustomers.Select(t => new OrdersCustomersVM
                {
                    ClientId = t.ClientId,
                    ClientName = t.Client.ClientName
                }),
            }).OrderByDescending(c => c.JobNo).Where(r =>r.OfficeId == _getempinfo.GetOID(User.Identity.Name);

        return Json(List.ToDataSourceResult(request));
    }

I would like to optimize my code with either cache, etc. since it makes no sense to have so many SQL queries (I can see that in SQL Server profiler).

My Kendo grid is an Ajax batch(true) incell type of grid.

Answer 1

By creating an interface, the query is optimized automatically it seems. Not being an expert in Asp.net Core / EF Core, maybe someone can explain why it does this.

If the query is inside the controller, the page takes 32 seconds to return data because it makes a seperate query for each row.

If the query runs trough an interface, it takes 947 ms.

public interface IOrdersService
{
    IEnumerable<Orders> GetAll();
}

public IEnumerable<Orders> GetAll()
    {
        return _context.Orders.AsNoTracking().Include(o => o.OrdersCustomers).ThenInclude(o =>o.Client);

    }

Then in the controller:

public IActionResult Read([DataSourceRequest] DataSourceRequest request)
{
    var orders = _orderservice.GetAll();

    var vm = orders.Select(c => new OrdersVM
        {
            JobNo = c.JobNo,
            OrdersCustomers = c.OrdersCustomers.Select(t => new OrdersCustomersVM
            {
                ClientId = t.ClientId,
                ClientName = t.Client.ClientName
            }),
        }).OrderByDescending(c => c.JobNo).Where(r =>r.OfficeId == _getempinfo.GetOID(User.Identity.Name);

    return Json(vm.ToDataSourceResult(request));
}