Databases
StackOverflow2013
Postsdbo
POHow to integrate a Linq to Entity query that queries multiple entities in a repository and send data to View?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to integrate a Linq to Entity query that queries multiple entities in a repository and send data to View?
    text
    copied!<p>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. </p> <p>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. </p> <p>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.</p> <pre><code>public IEnumerable&lt;User&gt; 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 &gt;= todayDate.Date orderby u.DepartDate ascending select j; return viewModel.ToList(); } </code></pre> <p>Below is my User entity</p> <pre><code>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&lt;Journey&gt; Journeys { get; set; } } </code></pre> <p>Below is my Controller</p> <pre><code>public ViewResult Search() { var userJourneyList = from user in repository.GetJourneys() select user; var searchView = new List&lt;SearchViewModel&gt;(); 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()); } </code></pre> <p><strong>UPDATE</strong> In my repository now</p> <pre><code>public IList&lt;User&gt; GetAllUsersWithJourneys() { using (var db = new EfDbContext()) { var users = from userJourney in db.Users.Include(i =&gt; i.Journeys) select userJourney; return users.ToList(); } } </code></pre> <p>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.</p> <pre><code> var userJourney = repository.GetAllUsersWithJourneys(); var searchView = new List&lt;SearchViewModel&gt;(); 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()); </code></pre> <p>My ViewModel looks like this</p> <pre><code>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 } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload