LINQ left join only works in the ActionResult

Question

I'm trying to get up to speed with MVC, linq, razor, etc. Some things are working and others I just hit dead ends that really discourage me. This is the first one I haven't been able to work out. I've researched every lead I can before asking this question here and it's my first, so please go easy. I'm sure the answer is right in front of my face, but I just can't see it. I could type 14 pages of things I've tried, but I'm trying to keep this concise and to the point. I've completely mangled my code, so I'm pulling much of this from memory and therefore, it's not so much about punctuation errors. I apologize the error codes are not exact as I'm pulling those from memory too.

I have this left join linq query in my ActionResult:

public ActionResult Index()
{
    var orders = from o in db.Orders
    join u in db.Users on o.UserId equals u.Id into ou
    where o.UserId == uId
    from o in ou.DefaultIfEmpty()
    select o;

    Return View(orders.ToList());
}

In the Index.cshtml I call the results by doing:

@foreach (var item in Model.Orders) {
@item.User.Name //(joined Users table)
@item.OrderNumber //(Orders table)

So far, so good. However, I want to display more than just this list on the Index page. So, near as I can tell, I need a ViewModel to display several other pieces of data (OrdersTotal,etc.) on the same page. However, when I stick my linq query above in my models in a:

public  List<Order> GetOrders()
    {

        var orders = from o in db.Orders
    join u in db.Users on o.UserId equals u.Id into ou
    where o.UserId == uId
    from o in ou.DefaultIfEmpty()
    select o;

        return orders.ToList();
    }

It doesn't recognize the joined Users table, meaning it doesn't know item.User.Name in the cshtml. I then start a pandora's box of experiments that I can not make work, mainly in the select statement like:

select New  //this complains about anonymous type

or

select New Order(o.OrderNumber,u.Id)  //I can't remember the error here, but red squiggly lines

or

select New Order { OrderNumber = o.OrderNumber, UserName = u.Name}  //VS complains about not referencing the Users table, it wants to put a UserName field or property stub in my model, which if I do that, then it errors in the cshtml again as I'm guessing because I don't have a UserName in my Orders table?

A couple times, I got the above queries to work, but then it throws an exception when trying to access the Users table again in the cshtml. My controller for the viewmodel looks like this:

public ActionResult Index()
{

var ordersummary = new OrdersSummary();
var viewModel = new OrderSummary
  {
    Orders = ordersummary.GetOrders(),
    OrdersTotal = ordersummary.GetOrdersTotal(),
  };

return View(viewModel);
}

My viewModel model looks like this:

public class OrderSummary
    {
        public List<Order> Orders { get; set; }
        public decimal OrdersTotal { get; set; }
        public virtual User Users { get; set; }
    {

My Order model basically iterates through the different columns in the db with the same public virtual User Users like above which works fine with the list is pulled from the ActionResult.

public class Order
    {

        public int Id { get; internal set; }
        public int UserId { get; set; }
        public int OrderNumber { get; set; }
        ...
        ...
        public virtual User Users { get; set; }
    }

I can't help but think I'm doing something fundamentally wrong in my model or controller rather than in the syntax of the linq statement. Any help is much appreciated.

UPDATE: I've gone through a few of the tutorials now and I'm basically trying to mimic this page here (down where it starts at ViewModels, although my OrderSummary Model stems from the ShoppingCart GetCartItems,etc. above):

http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-8

However, the above tutorial requires no sql join. Imagine a ticket system where you have tickets (orders) and techs (users) and you're trying to assign tickets to techs (one tech per ticket, but some tickets may not have techs assigned to them yet aka null) and what I'm trying to create is the overview portion where you're looking at all the tickets and the techs assigned to those tickets in a table and then further statistics like Total # of Tickets or maybe eventually TechA has x # of Tickets. So, I need all the info from the orders database and simply the name from the users database and then be able to introduce multiple pieces of info on the Index page.

I notice some of the tutorials use an extra table to join the Ids. So, in my case an extra table that simply has OrderId and UserId. Is that maybe a better way to do the join ("join" for lack of the proper terminology)?

Answer 1

If you want to use anonymous types, you can return IEnumerable<dynamic> and work pretty much just like before with that;

public IEnumerable<dynamic> GetOrders()
{
    var orders = from order in db.Orders
                 join u in db.Users on order.UserId equals u.Id into ou
                 where order.UserId == uId
                 from user in ou.DefaultIfEmpty()
                 select new { order, user };

    return orders.ToList();
}

var item = GetOrders().First();
item.order.Number        // (Orders table)
item.user.Name           // (joined Users table)

Answer 2

So, the complete answer is as follows:

The cshtml weirdness I mentioned in the comments is due to this:

'object' does not contain a definition for 'X'

Anonymous objects are emitted as internal by the compiler. The Razor views are automatically compiled into a separate assembly by the ASP.NET runtime. This means that you cannot access any anonymous objects generated in your controllers.

So, that sucks, it doesn't like the anonymous query above even though I can see the info from both tables when I debug (when I hover over item in my cshtml). Anyway, I'm still using "dynamic" as suggested by Joachim, despite people on here saying it's probably not the best way to do things. However, I had to change my linq query to the following to eliminate the anonymity:

var orders = from order in db.Orders
             join u in db.Users on order.UserId equals u.Id into ou
             where order.UserId == uId
             from user in ou.DefaultIfEmpty()
             select new OrderSummary 
               { 
               OrderNumber = order.OrderNumer, 
               UserName = user.Name
               };

The trick to this is (for me anyway):

1. Before I was "selecting" a "new" "Order" (and several other things), but not "OrderSummary." It was never really clear to me that this value was supposed to be "select new ViewModel Name or I suppose just model name in other cases".
2. Inside the brackets, the "OrderNumber =" can be named anything. But then you have to create a property stub inside your ViewModel, in my case, OrderSummary. (VS will give you the dropdown option to create the property stub.) I thought it was pulling this info from my orders list defined in my ViewModel, but I guess the property stubs are still needed. I was also hoping to not have to define all of my order columns (i.e. looking for an order.* in SQL), but I guess this is the only option.

So then in my Index.cshtml, I'm doing similar to original, except calling the values above as I defined them:

@foreach (var item in Model.Orders) {
@item.UserName //(joined Users table) *This is the only line that changed.
@item.OrderNumber //(Orders table)}

My controller never changed in all this...and I think that's about it. I hope this saves someone else the headache of trying to setup a ViewModel in the future. Live and learn. My programming terminology is pretty weak, so I apologize if I used any of the wrong verbiage. Good luck.