How to integrate a Linq to Entity query that queries multiple entities in a repository and send data to View?

Question

I'm learning MVC, the repository pattern and EF and I need some advice as to how best to integrate a method which contains a query that queries multiple entities into the repository.

At the moment, I have created a repository class which implements an interface and uses an instance of DbContext to retrieve data from the database using the Entity Framework, but for one entity.

Edited... I have the GetJourneys() method in my repository, however I am unsure how to obtain the Journey details from the query in the Controller. I can get the User details.

public IEnumerable<User> GetJourneys()
    {
        var todayDate = DateTime.Now;

        //Woking with many to many Relationship 
        //(join tables) in Asp.net MVC/Entity Framework
        var viewModel = from j in dbContext.Users
                        from u in dbContext.Journeys
                        where u.DepartDate >= todayDate.Date
                        orderby u.DepartDate ascending
                        select j;

        return viewModel.ToList();
    }

Below is my User entity

public class User
{
    [Key, Required]
    public int UserID { get; set; }

    [MaxLength(30), Required]
    public string FirstName { get; set; }

    [MaxLength(30), Required]
    public string LastName { get; set; }

    [Required]
    public string ProfileImg { get; set; }

    [MaxLength(30), Required]
    public string FbLink { get; set; }

    public ICollection<Journey> Journeys { get; set; }
}

Below is my Controller

public ViewResult Search()
    {
        var userJourneyList = from user in repository.GetJourneys() select user;
        var searchView = new List<SearchViewModel>();
        try
        {
            if (userJourneyList.Any())
            {
                foreach (var user in userJourneyList)
                {
                    searchView.Add(new SearchViewModel()
                    {
                        //JourneyDestination = user.Journeys.FromDestination,
                        //JourneyDate = user.Journeys.DepartDate,
                        UserName = user.FirstName,
                        ProfileImage = user.ProfileImg,
                        //SeatsAvailable = user.Journeys.SeatsAvailable,
                        //UserType = user.Journeys.UserType
                    });
                }
                returnAmount = 1;
                ViewBag.Amount = returnAmount;
            }
            else
            {
                returnAmount = 0;
                ViewBag.Amount = returnAmount;
            }
            var todayDate = DateTime.Now;
        }
        catch (NullReferenceException ex)
        {
            MessageBox.Show(ex.Message);
        }

        return View(searchView.ToList());
    }

UPDATE In my repository now

public IList<User> GetAllUsersWithJourneys()
    {
        using (var db = new EfDbContext())
        {
            var users = from userJourney in db.Users.Include(i => i.Journeys)
                        select userJourney;
            return users.ToList();
        }
    } 

However, I still don't understand how to get the journey details. My User and Journey entities are correct in terms of a many-to-many relationship. Below is the controller with the new repository method.

        var userJourney = repository.GetAllUsersWithJourneys();
        var searchView = new List<SearchViewModel>();
        try
        {
            if (userJourneyList.Any())
            {
                foreach (var user in userJourney)
                {
                    searchView.Add(new SearchViewModel()
                        {
                            UserName = user.FirstName,
                            JourneyDestination = user.Journeys.ToDestination //ERROR
                        });
                }
            }
            else
            {
                //user will be notified that no results were found and that they are given the option to create the journey that they seek
                returnAmount = 0;
                ViewBag.Amount = returnAmount;
            }
        }
        catch (NullReferenceException ex)
        {
            MessageBox.Show(ex.Message);
        }

        return View(searchView.ToList());

My ViewModel looks like this

public class SearchViewModel
{
    public string ProfileImage { get; set; } //User
    public string JourneyDestination { get; set; } //Journey
    public DateTime JourneyDate { get; set; } //Journey
    public string UserName { get; set; } //User
    public int SeatsAvailable { get; set; } //Journey
    public bool UserType { get; set; } //Journey
}

Answer 1

If what you're trying to do is flatten all user journeys into a single list (assumption based on the shape of the model you're passing to the view), then one way to do it would be like this:

var userJourney = repository.GetAllUsersWithJourneys();
var searchView = new List<SearchViewModel>();
try
{   
    if (userJourneyList.Any())
    {
        foreach (var user in userJourney)
        {
            foreach(var journey in user.Journeys)
            {
                searchView.Add(new SearchViewModel()
                    {
                        UserName = user.FirstName,
                        JourneyDestination = journey.JourneyDestination
                    });                
            }
        }
    }
}
 catch (NullReferenceException ex)
 {
     // ... 
 }

Alternatively, you could refactor it to be more functional:

var userJourney = repository.GetAllUsersWithJourneys();
var searchView = userJourney.SelectMany(
    user => user.Journeys.Select(
        journey => new SearchViewModel()
            {
                UserName = user.FirstName,
                JourneyDestination = journey.JourneyDestination
            }
        )
    )
    .ToList();        

if (!searchView.Any())
{
    // no results logic
}

The second method would be even better if your repository returned IQueryable<User> instead of calling ToList() and returning IList<User>, BUT that won't work with your repository disposing the DbContext right away. As it stands (using ToList()), you're going to end up doing more processing in memory than you would if you let SQL do the work. If your repository knew about SearchViewModel, you could do this:

public IList<SearchViewModel> GetSearchViewModels()
{
    using (var db = new EfDbContext())
    {
        var users = from user in db.Users
                    from journey in user.Journeys
                    select new SearchViewModel()
                    {
                        UserName = user.FirstName,
                        JourneyDestination = journey.JourneyDestination
                     }
                    select userJourney;

        return users.ToList();
    }
} 

However, that may be an unacceptable mixing of presentation and data layers, depending on how strict your architecture is.

Answer 2

how best to integrate a method which contains a query that queries multiple entities into the repository.

Considering the relationship between User and Journey, you should decide which entity owns the relationship and use Aggregate Root to fetch data.

The description of Aggregate Root at What's an Aggregate Root? would be helpful.

Update:

Entities

public class User
{
    public User()
    {
        this.Journeys = new List<Journey>();
    }

    public int Id { get; set; }
    public virtual IList<Journey> Journeys { get; set; }
}

public class Journey
{
    public Journey()
    {
        this.Users = new List<User>();
    }

    public int Id { get; set; }
    public virtual IList<User> Users { get; set; }
}

Repositiories

public class UserRepository
{
    public IList<User> GetAllUsersWithJourneys()
    {
         //Fetch all Users; Include Journeys 
    }
}

public class JourneyRepository
{
    public IList<Journey> GetAllJourneysWithUsers()
    {
         //Fetch all journeys; Include Users 
    }
}

ViewModel

public class UserJourneyViewModel
{
    public int UserId { get; set; }
    public int JourneyId { get; set; }
}

Controller's Action

public ViewResult Search()
{
    // Use UserRepository or JourneyRepository to make a list of
    // UserJourneyViewModel that provides appropriate data for the view.  
}