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

i'm learning mvc, repository pattern , ef , need advice how best integrate method contains query queries multiple entities repository.

at moment, have created repository class implements interface , uses instance of dbcontext retrieve data database using entity framework, 1 entity.

edited... have getjourneys() method in repository, unsure how obtain journey details query in controller. can user details.

public ienumerable<user> getjourneys()     {         var todaydate = datetime.now;          //woking many many relationship          //(join tables) in asp.net mvc/entity framework         var viewmodel = j in dbcontext.users                         u in dbcontext.journeys                         u.departdate >= todaydate.date                         orderby u.departdate ascending                         select j;          return viewmodel.tolist();     } 

below 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 controller

public viewresult search()     {         var userjourneylist = 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 repository now

public ilist<user> getalluserswithjourneys()     {         using (var db = new efdbcontext())         {             var users = userjourney in db.users.include(i => i.journeys)                         select userjourney;             return users.tolist();         }     }  

however, still don't understand how journey details. user , journey entities correct in terms of many-to-many relationship. below controller 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 notified no results found , given option create journey seek                 returnamount = 0;                 viewbag.amount = returnamount;             }         }         catch (nullreferenceexception ex)         {             messagebox.show(ex.message);         }          return view(searchview.tolist()); 

my viewmodel looks 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 } 

if you're trying flatten user journeys single list (assumption based on shape of model you're passing view), 1 way 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, refactor 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 better if repository returned iqueryable<user> instead of calling tolist() , returning ilist<user>, but won't work repository disposing dbcontext right away. stands (using tolist()), you're going end doing more processing in memory if let sql work. if repository knew searchviewmodel, this:

public ilist<searchviewmodel> getsearchviewmodels() {     using (var db = new efdbcontext())     {         var users = user in db.users                     journey in user.journeys                     select new searchviewmodel()                     {                         username = user.firstname,                         journeydestination = journey.journeydestination                      }                     select userjourney;          return users.tolist();     } }  

however, may unacceptable mixing of presentation , data layers, depending on how strict architecture is.