Loading multiple sets of data in a content page in ASP.NET MVC Entitiy Framework

Question

I need to load multiple entity types in my View page. I am using ViewModel for this purpose. However, I need to make around 5-6 database calls to load each set of data and assign them to the relevant property of the ViewModel. I wonder if this is a recommended approach since it requires multiple database calls. Or, am I being over-concerned about this? Here is a snapshot from my code:

    var model = new EntryListVM();

    string userid = "";
    if (ViewBag.CurrentUserId == null)
        userid = User.Identity.GetUserId();
    else
        userid = ViewBag.CurrentUserId;

    ViewBag.CurrentUserId = userid;

    //First database call
    model.DiscussionWall = db.DiscussionWalls.Find(wallId);

    //Second database call to learn if the current students has any entry
    model.DiscussionWall.DoesStudentHasEntry = db.Entries.Any(ent => ent.DiscussionWallId == wallId && ent.UserId == userid);
    model.PageIndex = pageIndex;

    //Third database call
    model.TeacherBadges = db.Badges.Where(b => b.CourseId == model.DiscussionWall.CourseId && b.IsSystemBadge == false && b.IsEnabled == true).ToList();

    //Fourth database call
    model.Reactions = db.Reactions.Where(re => re.CourseId == model.DiscussionWall.CourseId).ToList();

    int entryPageSize = Convert.ToInt32(ConfigurationManager.AppSettings["EntryPageSize"]);
    int firstChildSize = Convert.ToInt32(ConfigurationManager.AppSettings["FirstChildSize"]);

    List<ViewEntryRecord> entryviews = new List<ViewEntryRecord>();
    bool constrainedToGroup = false;
    if (!User.IsInRole("Instructor") && model.DiscussionWall.ConstrainedToGroups)
    {
        constrainedToGroup = true;
    }

    //Fifth database call USING VIEWS
    //I used views here because of paginating also to bring the first 
    //two descendants of every entry 
    entryviews = db.Database.SqlQuery<ViewEntryRecord>("DECLARE @return_value int;EXEC  @return_value = [dbo].[FetchMainEntries] @PageIndex = {0}, @PageSize = {1}, @DiscussionWallId = {2}, @ChildSize={3}, @UserId={4}, @ConstrainedToGroup={5};SELECT  'Return Value' = @return_value;", pageIndex, entryPageSize, wallId, firstChildSize, userid, constrainedToGroup).ToList();

    model.Entries = new List<Entry>();
    //THIS FUNCTION MAP entryviews to POCO classes
    model.Entries = ControllerUtility.ConvertQueryResultsToEntryList(entryviews);

    //Sixth database call
    var user = db.Users.Single(u => u.Id == userid);

    model.User = user;

I wonder if this is too much of a burden for the initial page load?

I could use SQL-View to read all data at once, but I guess I would get a too complicated data set to manage.

Another option could be using Ajax to load the additional results after the page loading (with the main data) is completed. For example, I could load TeacherBadges with AJAX after the page is being loaded.

I wonder which strategy is more effective and recommended? Are there specific cases when a particular strategy could be more useful?

Thanks!

Answer 1

It all depends on your scenario - different scenarios have different ways of doing things. There is no single right way of doing things that are similar in nature. What might work for me might not work for you. Ever heard that saying: there are many ways to kill a cat? Well this certainly applies to programming.

I am going to answer based on what I think you are asking. Your questions are very broad and not that specific.

However, I am not sure if this is a recommended approach since it requires multiple database calls.

Sometimes you need to do one database call to get data, and sometimes you need to do more than one database call to get the data. For example:

• User details with addresses: one call for user and one call for addresses
• User details: one call

I am using ViewModel for this purpose.

Using view models for your views is a good thing. If you want to read up more on what I had to say about view models then you can go and read an answer that I gave on the topic:

What is ViewModel in MVC?

View models are ideal for when you have data that is coming from multiple datasets. View models can also be used to display data coming from one dataset, for example:

• Displaying user details with multiple addresses
• Displaying only user details

I read the data in the controller in separate linq statements, and assign them to the relevant List property of the ViewModel.

I would not always return a list - it all depends on what you need.

If I have a single object to return then I will populate a single object:

User user = userRepository.GetById(userId);

If I have a list of objects to return then I will return a list of objects:

List<User> users = userRepository.GetAll();

It is of no use to return a single object and then to populate a list for this object:

List<User> user = userRepository.GetByUserId(userId).ToList();

Second option could be using SQL-View to read all data with one database call, and then map them to the entities properly in controller.

This is similar to your first question, how you return your data on the database level is up to you. It can be stored procedures or views. I personally prefer stored procedures. I have never used views before. Irrespective of what you choose your above mentioned repository methods should still look the same.

Third option could be using Ajax to load the additional results after the page loading (with the main data) is completed.

You can do this if you want to. I would not do it if it is not really needed. I try to load data on page load. I try to get as much data on the screen before the page is fully loaded. There have been times that I had to go the AJAX route after the page was loaded. After the page was loaded I had to do an AJAX call to load my HTML table.

If you really just need to have data displayed then do just that. You do not need any fancy ways of doing this. Maybe later you need to change on screen data, then AJAX is cool to use.

I wonder which strategy is more effective and recommended? Are there specific cases when a particular strategy could be more useful?

Let us say you want to display a list of users. We do a database call and return the list to the view. I do not normally use view models if I only return a list:

public class UserController : Controller
{
     private IUserRepository userRepository;
     private IAddressRepository addressRepository;

     public UserController(IUserRepository userRepository, IAddressRepository addressRepository)
     {
          this.userRepository = userRepository;
          this.addressRepository = addressRepository;
     }

     public ActionResult Index()
     {
          List<User> users = userRepository.GetAll();

          return View(users);
     }
}

And your view could look like this:

@model List<YourProject.Models.User>

@if (Model.Count > 0)
{
     foreach (var user in Model)
     {
          <div>@user.Name</div>
     }
}

If you need to get a single user's details and a list of addresses, then I will make use of a view model because now I need to display data coming from multiple datasets. So a user view model can look something like this:

public class UserViewModel
{
     public UserViewModel()
     {
          Addresses = new List<Address>();
     }

     public int Id { get; set; }

     public string Name { get; set; }

     public List<Address> Addresses { get; set; }
}

The your details action method could look like this:

public ActionResult Details(int id)
{
     User user = userRepository.GetById(id);
     UserViewModel model = new UserViewModel();
     model.Name = user.Name;
     model.Addresses = addressRepository.GetByUserId(id);

     return View(model);
}

And then you need to display the user details and addresses in the view:

@model YourProject.ViewModels.UserViewModel

<div>First Name: @Model.Name</div>
<div>
     @if (Model.Addresses.Count > 0)
     {
          foreach (var address in Model.Address)
          {
               <div>@address.Line1</div>
               <div>@address.Line2</div>
               <div>@address.Line3</div>
               <div>@address.PostalCode</div>
          }
     }
</div>

I hope this helps. It might be to broad of an answer but it can guide you on the correct path.

Answer 2

Includes for linked data

For linked data it's simple (you probably know this way):

var users = context.Users.Include(user => user.Settings).ToList();

It queries all users and pre-loads Settings for each user.

---

Use anonymous class for different data sets

Here is an example:

context.Users.Select(user => new 
{
    User = user,
    Settings = context.Settings
        .Where(setting => setting.UserId == user.Id)
        .ToList()
}).ToList();

You still kinda need to choose your main query collection (Users in this case), but it's an option. Hope it helps.