How to Use a View Model with LINQ to Entities

Question

I'm new to ASP.NET MVC, but I cant seem to understand the rules concerning View Models. The example I'm showing here definitely resembles other examples from various sources so I'm totally flumuxed.

I've used Code First With Entity Framework 6 on VS 2013 MVC 5, and created the following two Models.

    public class Task
    {
        public int TaskId { get; set; }
        public int UserId { get; set; }
        public String Title { get; set; }
        public String Description { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }

        public virtual User User { get; set; }

    }



    public class User
    {
        public int UserId { get; set; }
        public String UserName { get; set; }
        public String Password { get; set; }
        public String FullName { get; set; }
        public String Email { get; set; }
        public String Status { get; set; }
        public String Role { get; set; }
        public virtual ICollection<Task> Tasks { get; set; }

    }

And here is my Context.

public class FRESHContext : DbContext
    {


        public DbSet<User> Users { get; set; }
        public DbSet<Task> Tasks { get; set; }
        public DbSet<Artefact> Artefacts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

        public System.Data.Entity.DbSet<FRESH.ViewModel.ManageTasksViewModel> ManageTasksViewModels { get; set; }

    }

Now seeing that there is a one to many relationship between User and Task, I want to list tasks out showing which User has been assigned to them (Well this 'is' just a learning example), so I figured that this would benefit from designing a special View Model to strongly type to the View page and to use a LINQ to Entities query to derive the model to send to the View Page. Here is my View Model to be used with the strongly typed View Page.

  public class ManageTasksViewModel
    {

        public int TaskId { get; set; }
        public int UserId { get; set; }
        public String FullName { get; set; }
        public String Title { get; set; }
        public String Description { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
    
    }

And don't worry, I've correctly strongly typed the View Page as follows;

@model IEnumerable<FRESH.ViewModel.ManageTasksViewModel>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.UserId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.FullName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Title)
        </th>
        <th>

etc etc .............(with brevity in mind lol.)

So now I create a LINQ to Entities query, which seems quite reasonable to me after watching, oh I don't know a thousand videos, lol.

 public class ManageTasksController : Controller
    {
        private FRESHContext db = new FRESHContext();

        // GET: ManageTasks
        public ActionResult Index()
        {
          
           var query = (from U in db.Users
                        join T in db.Tasks on U.UserId equals T.UserId
                        select new ManageTasksViewModel
                        {
                            TaskId = T.TaskId,
                            UserId = U.UserId,
                            FullName = U.FullName,
                            Title = T.Title,
                            Description = T.Title,
                            StartDate = T.StartDate,
                            EndDate = T.EndDate

                        }).Take(10).ToList();
            
            
            
            return View("Index",query);
        }

And of course it built! yeehaar!, but when the code gets to the ActionResult Index(), I get this Exception.

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The entity or complex type 'FRESH.DAL.ManageTasksViewModel' cannot be constructed in a LINQ to Entities query.

So what have I done wrong, and also could I do this better another way, I haven't looked into LINQ to SQL, as I thought that this method would surely work. Any help would be much appreciated. Thank you.

Answer 1

Thanks for all those who gave their time to help me on this, Basically I was going over the Contoso University tutorial again and got the answer to this, basically it's ridiculously similar to my first attempt, except that I was trying.

select new ManageTasksViewModel { . . . };

whereas I should have been saying;

select new ManageTasksViewModel() { . . . }

    public class ManageTasksController : Controller
    {
        private FRESHContext db = new FRESHContext();

        // GET: ManageTasks
        public ActionResult Index()
        {

            IQueryable<ManageTasksViewModel> query = from tasks in db.Tasks
                                                     join users in db.Users on tasks.UserId equals users.UserId
                                                     select new ManageTasksViewModel()
                                                   {
                                                       TaskId = tasks.TaskId,
                                                       UserId = users.UserId,
                                                       FullName = users.FullName,
                                                       Title = tasks.Title,
                                                       Description = tasks.Description,
                                                       StartDate = tasks.StartDate,
                                                       EndDate = tasks.EndDate
                                                   };
            return View(query.ToList());
        }

Anyway hope this is useful to someone.

Answer 2

You should not have a viewmodel in your dbcontext. This is a viewModel only and is totally different than your entities.

public System.Data.Entity.DbSet<FRESH.ViewModel.ManageTasksViewModel> 
      ManageTasksViewModels { get; set; }