UIDAlexD UIDAlexD - 4 months ago 15
SQL Question

Joining tables, getting Object Reference Not Set

I'm converting an existing program to MVC5, and I need to join multiple tables from a database. I made a dummy project project to experiment so I don't irreparably damage anything, and I have it successfully pulling and showing all the individual tables of the database. However, when I try to join them, I get "Object reference not set to an instance of an object."

Note: Products and tasks are existing models and tables in the DB. It's already deployed and we can't alter them.

Relevant controller code:

public ActionResult Index()
{
_db.Configuration.ProxyCreationEnabled = false;
var JoinTest = (
from a in _db.Task.AsEnumerable()
join b in _db.Product on a.ProductId equals b.ProductId
select new
{
TaskId = a.TaskId,
TaskDesc = a.TaskDescription,
ProdDesc = b.ProductDescription
});
ViewData["CompositeData"] = JoinTest;
ViewData["ProductData"] = _db.Product.ToList();
}


View code:

@foreach (var item in ViewData["CompositeData"] as List<TomFoolery.Models.ViewModel>)
{
//Printing relevant items from DB
}


And, to be thorough, the definition of ViewModel:

namespace TomFoolery.Models
{
public class ViewModel
{
public Product Product { get; set;}
public Task Task { get; set; }
}
}


So, where am I going wrong?

Answer

ViewData["CompositeData"] as List<TomFoolery.Models.ViewModel> will return null because the items you set to ViewData["CompositeData"] is a list of anonymous type.

You should create a view model and use that.

 public class MyViewModel
 {
     public int TaskId  {set;get;}
     public string TaskDesc {set;get;}
     public string ProductDesc {set;get;}
 }

and use it in your projection part.

ViewData["CompositeData"] = (
            from a in _db.Task.AsEnumerable()
            join b in _db.Product on a.ProductId equals b.ProductId
            select new MyViewModel
            {
                TaskId = a.TaskId,
                TaskDesc = a.TaskDescription,
                ProdDesc = b.ProductDescription
            }).ToList();

and in the view, you need to cast to this view model collection.

@foreach (var item in ViewData["CompositeData"] as List<TomFoolery.Models.MyViewModel>)
{
   <p>@item.TaskId</p>
}

You may also consider eliminating the use of ViewData and go for a complete strongly typed view model approach. Create a view model for your view for that

public class MyPageViewModel
{
  public List<MyViewModel> Tasks {set;get;}
  public List<Product> Products {set;get;}
}

and in your GET action, instead of setting to ViewData, create an object of this view model and set to it's corresponding properties

var vm = new MyPageViewModel();
vm.Products = _db.Product.ToList();
vm.Tasks = (
                from a in _db.Task.AsEnumerable()
                join b in _db.Product on a.ProductId equals b.ProductId
                select new MyViewModel
                {
                    TaskId = a.TaskId,
                    TaskDesc = a.TaskDescription,
                    ProdDesc = b.ProductDescription
                }).ToList();
return View(vm);

and in your view which is strongly typed to our new view model

@model MyPageViewModel
@foreach(var item in Model.Tasks)
{
  <p>@item.TaskId</p
  <p>@item.TaskDesc</p>
  <p>@item.ProdDesc</p>
}
Comments