How to create more complex models from many tables in asp.net mvc, linq

Question

Hi All,

I know that there are a few questions like this on SOF, but I was interested if someone could succintly answer it with examples.

I am new to linq and mvc, and although a lot of it is sticking, I am finding it hard how to imagine that I can do lots of stuff with data. Here is an example.

Lets say that I have 5 tables, some of which have relationships, some don't.

And I want data from all of these tables to apear on my view.

Should I be adding all this data to the model, and passing it all at that stage, or doing it at some later point? I could imagine how I could create a dataSet with all this info in, and extract it on the view, but that feels wrong.

I also could imagine making 5 separate sql requests, but that feels like it would be going against the mvc pattern, and also I would be unsure about how all these would be sent to the view, unless they were all formed into a model. (however I am interested in doing this, as I believe this would be a way of overcoming the performance issues that linq can have, but not going to scale untill I have too, I just like the idea of power that would bring.

Any help would be greatly appreciated, or suggestions for tutorials that can make me feel more at home at these points.

Thanks in advance.

Here is some model code I'm trying to use at the moment, in nerd dinner terms

 public IQueryable<Dinner> GetAllUserDinnersAndRSVP(string userId)
    {
        return from dinner in db.Dinners
               where dinner.userId == userId

               join rsvp in db.Dinners
               on userId equals rsvp.userId
               select dinner;
    }

But when I come to listing all the items from this model in the view, I can't get it to loop through the rsvp records as well.

Not sure if that is a helpful example or not!!

Answer 1

i have just tackled this problem where i had an orders table ( 1row) and an order items table ( many rows) and wanted to show 1 view - invoice!

here is an example:

code in actionResults test(string quoteid)

 ViewData["quoteid"] = quoteid;

        var model = new QuoteViewModel
        {
            quoteInfo = new_online_quote.SingleOrDefault(x => x.quoteid == Convert.ToInt32(quoteid)),
            quoteItems = new_quote_item.Find(x => x.quote_id == Convert.ToInt32(quoteid))
        };

        return View(model);

then i have a new class outside of the controllerClass but in the same page

public class QuoteViewModel
{
    public new_online_quote quoteInfo { get; set; }
    public IEnumerable<new_quote_item> quoteItems { get; set; }
}

the quoteItems is IENumerable because it will be a list of items that i will be moving from etc.

on the actual page i just created a normal view, not strongly typed, but you can do.

at the top of the page i have this

Inherits="System.Web.Mvc.ViewPage<namespace.Controllers.QuoteViewModel>"

this inherits my class:

now i can access both classes and the data like so:

Total Quote Cost <%=String.Format("{0:C}",Model.quoteInfo.totalcost) %> inc VAT @ 15%<br />

Your Items:

<% foreach (var item in Model.quoteItems) { %> <%= Html.Encode(item.prodid) %> <%} %>

again notice i can use a for each on my quoteItems and get all the records together.

hope this helps, im quite new to MVC and this kind of coding really but the above was put together with help in this forum, so its only right to pass it back on.