CODEWITHSUNDEEP
c#sqllinqasp.net-mvc-5
mcloud313
mcloud313

Reputation: 37

How to pass a LINQ statement getting data from multiple tables to a model to populate a View?

I'd very much like to be pointed in the right direction as to what I am doing wrong. I am attempting to pull data from multiple tables in my database and then plan to populate a table within a view with that data. I've done this before from a single table and had no problem, but I'm stumbling as to how to do this with joins in the LINQ statement.

Here is my Controller where I am trying to get the relevant data:

public ActionResult PurchaseHistory()
    {
        //Get White Label ID to filter results
        var whiteLabelId = new WhiteLabelHelper().GetWhiteLabelId();

        PurchaseHistoryModel model = new PurchaseHistoryModel();



        model = from pc in db.PurchasedCards
                              join c in db.Cards on pc.CardId equals c.CardId
                              join u in db.Users on pc.UserId equals u.UserId
                              join g in db.FundraisingGroups on pc.GroupId equals g.GroupId
                              where c.WhiteLabelId == whiteLabelId
                              select new PurchaseHistoryModel { Card = c.Name, User = u.Name, Group = g.Name, Proceeds = pc.NPOProceeds };



        return View(model);
    }

Here is the Model I started to create when I realized that I wasn't sure how to just throw all of this in anonymous typed list because I couldn't access the List's attributes to populate a table on the view:

    public class PurchaseHistoryModel
{
    public string Card { get; set; }

    public string User { get; set; }

    public string Group { get; set; }

    public decimal? Proceeds { get; set; }


}


public class PurchaseHistoryViewModel
{

    IPagedList<PurchaseHistoryModel> PurchaseList { get; set; }

}

}

I am trying to pass all of this to a view where I can do a table head and then a

@foreach (var item in model)

and then access it all with @model.Card, @model.User, @model. Group i.e. I am having problems though in regards to what datatype I should cast my LINQ iQueryable into to pass it into the view to still access all of the attributes and to get the entire list of rows from my database. Could someone please help to point me into the right direction?

Upvotes: 1

Views: 1241

Answers (1)

yohannist
yohannist

Reputation: 4204

The linq operation returns an IQueryable that will resolve to a collection. So your model will need to be some kind of enumerable(I chose to make it a List here, but you can use Collection or other enumerable). You can add a ToList() at the end of your linq query to avoid lazy loading. 

public ActionResult PurchaseHistory()
        {
            //Get White Label ID to filter results
            var whiteLabelId = new WhiteLabelHelper().GetWhiteLabelId();

            var model = new List<PurchaseHistoryModel>();



            model = (from pc in db.PurchasedCards
                                  join c in db.Cards on pc.CardId equals c.CardId
                                  join u in db.Users on pc.UserId equals u.UserId
                                  join g in db.FundraisingGroups on pc.GroupId equals g.GroupId
                                  where c.WhiteLabelId == whiteLabelId
                                  select new PurchaseHistoryModel { Card = c.Name, User = u.Name, Group = g.Name, Proceeds = pc.NPOProceeds }).ToList();



            return View(model);
        }

If you do choose to access it using a @foreach (var item in model) then you'll need to do @item.Card, @item.User... NOT @model.Card

Upvotes: 1

Related Questions