Joining tables with EF

Here is my database:



Here is my viewModel:

public class groupsFoldersViewModel
    {
        private DAL.educationSocialNetworkEntities DB = new DAL.educationSocialNetworkEntities();
     public  List sharedFolders { get; set; }

     public groupsFoldersViewModel(int gid , int uid)
     {
         DAL.group_table group = DB.group_table.Find(gid);

         if (group != null)
         {

             sharedFolders = group.sharedFolders_table.ToList();
         }

     }
    }

Which way of joining table is better and faster?

• One heavy query for joining tables in ViewModel constructor

-
Using something like this in a razor page:

@foreach (var f in Model.sharedFolders) 
{
     @f.folder_table.folderName 
}

At the end of the day, only you can tell what performs best. But there are two things that in most cases will help improve performance.

1. Reduce the number of queries.

Essentially, you do

DAL.group_table group = DB.group_table.Find(gid);
sharedFolders = group.sharedFolders_table.ToList();

And in the view, for each sharedFolders_table you diplay

sharedFolders_table.folder_table.folderName

That's one query for each sharedFolders_table.

So you end up having 1 + 1 + n queries.

No doubt, it's better to turn these into one. For example:

sharedFolders = DB.sharedFolders_table
                  .Include(s => s.sharedFolders_table)
                  .Where(s => s.groupId == gid).ToList();

But since you only seem to use folder_table.folderName it should be better yet to get only folder_table objects:

sharedFolders = DB.sharedFolders_table
                  .Where(s => s.groupId == gid)
                  .Select(s => s.folder_table)
                  .ToList();

(Note that this changes your model data type.)

1. Use a projection

These queries return complete entities. But in the end, you only display one field (let's ignore the fact that in reality you probably do more). So you may as well get that field only.

Reducing the number of fields in a query can considerably improve performance, especially when records are wide and many joins are involved. I often see people focus on reducing the number of records, but reducing the number of fields is equally important.

In your reduced example, it's enough to get only the names from the database:

sharedFolders = DB.sharedFolders_table
                  .Where(s => s.groupId == gid)
                  .Select(s => s.folder_table.folderName)
                  .ToList();

(again, this changes the model type)

Now you have one lean and mean query, instead of 1 + 1 + n wide queries.

StackExchange Code Review Q#114465, answer score: 2