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
}

What's the conceptual relationship between `User` and `Journey`? — Abbas Amiri, Aug 23 '13 at 14:51
In your second "controller" code block, on the line commented "ERROR", it looks like you're assigning a value typed as `ICollection` to a property typed as `string`. Is that what your code really is? — Steve Ruble, Aug 25 '13 at 13:32

score 2 · Answer 1 · answered Aug 25 '13 at 14:05

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.

Thanks for the help Steve, but with no success. My goal is to read all journeys and their user and show in a list in the view. User and Journey have a many-to-many relationship. Is it correct to use Eager Loading to get the data? — Colin Roe, Aug 26 '13 at 17:57
@ColinRoe, what went wrong? Yes, you should use eager loading in this case. — Steve Ruble, Aug 26 '13 at 18:19
I call the method GetAllUsersWithJourneys in the controller but I don't get any Journey data from the query. When I try to assign values to the properties in the SearchViewModel within the foreach loop, I cannot access the Journey properties (e.g. JourneyDestination = journey.ToDestination) not that they contain anything at the moment. Is there an issue with my eager loading within the GetAllUsersWithJourneys method and how I access the data in the controller? — Colin Roe, Aug 26 '13 at 19:54

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

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.  
}

edited May 23 '17 at 12:05

Community

1
1

answered Aug 23 '13 at 15:50

Abbas Amiri

3,074
1
23
23

I have included my User entity above. User would be the Root. I tried changing the relationship to an one to one relationship just to see if I could obtain the Journey data from the query in the controller and I was successful. With the relationship being many to many is there something wrong with my User entity and how can I obtain the data from the query relating to the Journey details. – Colin Roe Aug 24 '13 at 12:26
I think the `Search` method is not actually a search, it fetches all users with their journeys. Am I right? – Abbas Amiri Aug 24 '13 at 13:48
Yes it is just the name of the Action – Colin Roe Aug 24 '13 at 13:59
OK, I will update the answer with my solution for Many-to-Many relationship. I hope, it would be helpful. – Abbas Amiri Aug 24 '13 at 14:05
Thanks for your contribution. However, I'm still unable to get the Journey data when I fetch all Users, including Journeys. I edited my original question based on your feedback – Colin Roe Aug 24 '13 at 17:50
Have you initialized the `Journeys` collection in the constructor of `User`? – Abbas Amiri Aug 24 '13 at 18:17

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

2 Answers2