How to retrieve data from multiple tables and display in a view using viewmodel

Question

I'm trying to develop a messeging system to my mvc application using mvc 5. I have tables called Event, EventUser, EventObject. Each of those tables have following;

Event

ID

CreatedBy

StartTime

IsShared

Budget

EventUser

EventID

UserID

IsAccepted

EventObject

EventID

ObjectID

in my messageController i have the index method which receive the parameter of the user id.i need to display every event that user has invited using this method..

namespace MvcApp.Controllers
{
public class MessageController : Controller
{
    private EPlannerDatabaseEntities db = new EPlannerDatabaseEntities();
    // GET: /Message/
    public ActionResult Index(int UId)
    {

/* linq expressions */

        return View();
    }
}
}

when the parameter has passed in, i want to;

*Select from EventUser table where UID=UserID and join the result with Event and EventObject tables by using EventID attribute.

*Finally by using the final result i need to display every event's infomation that user has invited; like CreatedBy , StartTime, Budget,other users,objects etc..

i'm new to mvc and viewmodel concept.I heard that viewmodel concept can help with these situations.can i overcome this problem by using viewmodel concept.if yes what are the things i need to add in view model?? otherwise what are the other ways to do this?

what you are using for dataaccess ? Entityframework or normal ado.net? You can do that via entity framework joins. — Jalpesh Vadgama
– Jalpesh Vadgama, Commented Apr 6, 2015 at 14:32

user3094087 · Accepted Answer · 2015-04-06 14:40:26Z

1

one way i can see of doing this is creating a custom return object and using EF to join all the tables together. Example

public class MyObject{
      public DateTime DateCreated{get;set}
     // add remaining properties here
     // properties to get back
}

then in code you would use Entity Framework to create a joined data set into a nice list of objects. Example:

    var results =  (from b in bla join bla2 in (Some Second Query Here)
                    from SomeSecondQueryHere
                    where cond1 and cond2 Select new MyObject{
                    // add properties in here})

where you would replace the bla and bla2,etc with respective table names needed. Then all you need to do is

return View(results);

And the changes will be accessible in the View

answered Apr 6, 2015 at 14:40

user3094087

Sign up to request clarification or add additional context in comments.

Comments

Chris Pratt · Accepted Answer · 2015-04-06 15:25:04Z

0

If you question is regarding querying with an ORM like Entity Framework, you need to post your entities, not your table schemas. The whole purpose of an ORM is to abstract away the underlying database structure, so while the schema will often be similar to the entity class, it can also be quite different. As a result, I'll have to make assumptions about your entity classes.

To query everything, you just need something like the following:

var events = db.Events.Where(m => 
    m.EventUsers.Any(u => u.UserID == UId && u.IsAccepted)
).Include(m => m.EventObjects);

That assumes entity classes along the lines of:

public class Event
{
    ...

    public virtual ICollection<EventObject> EventObjects { get; set; }
    public virtual ICollection<EventUser> EventUsers { get; set; }
}

public class EventUser
{
    ...
    public int UserID { get; set; }
    public bool IsAccepted { get; set; }
}

You end up with an enumerable of Event. If you need to access the EventObjects for an individual event, you have to use the appropriate collection property. For example:

foreach (var item in events)
{
    foreach (var obj in item.EventObjects)
    {
        // do something with `obj` (an invidual `EventObject` instance)
    }
}

If you need the actual User object, you're better object querying that first and including related Events and EventObjects:

var user = db.Users.Include("EventUsers.Event.EventObjects").SingleOrDefault(m => m.UserID == UId);

That assumes entities like:

public class User
{
    ...
    public virtual ICollection<EventUser> EventUsers { get; set; }
}

public class EventUser
{
    ...
    public virtual Event Event { get; set; }
}

public class Event
{
    ...
    public virtual ICollection<EventObject> EventObjects { get; set; }
}

With that method, however, there's no way to filter the included Events by whether they're accepted or not. There's a potential way around that, but it requires disabling lazy-loading of EventUsers entirely and complicates querying the information you need. If you need to go that route, see: https://msdn.microsoft.com/en-us/data/jj574232.aspx#explicitFilter.

Otherwise, you can just exclude non-accepted events before iterating over the collection:

var events = user.EventUsers.Where(m => m.IsAccepted).Select(m => m.Event);

Really you don't need a view model, per se, for any of this. As you can either pass the lists of events (which will include any related EventObjects) or the the single user instance (which includes related events and related EventObjects) directly to your view.

answered Apr 6, 2015 at 15:25

Chris Pratt

240k37 gold badges414 silver badges468 bronze badges

2 Comments

Thilina De Silva Over a year ago

yes your assumption are on my code..i tried following code...it worked; but i'm having trouble with getting all the other users who also invited to that particular event to the view.how to i get the list of userIDs to the veiwmodel?? For ex: if the parameter UserId is 2 and user is invited to EventId 1 and 2,i want to display in view who are the other users for event id 1 and event id 2.

Thilina De Silva Over a year ago

public ActionResult Index(int UId)         {             var result1 = from eu in db.EventUsers where eu.UserId == UId select eu;             var result2 = from eu1 in result1                           join e in db.Events on eu1.EventId equals e.Id                           select new MessageViewModel {                                                      EventID1 = e.Id,                           IsAccepted= eu1.IsAccepted,                           CreatedBy = e.CreatedBy,                           Budget = e.Budget,                           };             return View(result2);         }

Fernando Soteras · Accepted Answer · 2015-04-06 17:34:13Z

A very high level description of how to solve your scenario using Entity Framework would be something like this:

First you've got to create a series of entity data objects that will represent your tables in the EF data model using EF Code first techniques. Then you create DbContext objects with DbSets for your previously created entities. Then you create at least one Service class that will have a property representing DbContext and a set of methods encapsulating Linq queries to your entities. In the MVC controller you call an instance of Service that you previously create and assign it to a property ant Controller's construction time. Finally, in the Action method you should call the correct Service method and pass any result to the view. ( I am assuming this is a small Ad-Hoc system with a handful of tables , an elaborate System with production quality would require using IoC techniques).

Collectives™ on Stack Overflow

How to retrieve data from multiple tables and display in a view using viewmodel

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related