Pass table join to model using Linq to Entity

asp.net-mvc entity-framework linq

Question

Good time of a day!

I have a MVC project with query in controller:

var getPhotos = (from m in db.photos
    join n in db.comments on m.id equals n.photoid
    where n.ownerName == User.Identity.Name
    orderby n.id descending
    select new { 
        m.imgcrop, m.id, 
        n.commenterName, n.comment 
    }).Take(10);

How to pass this query to view model, and the model to view.

Spend all evening to find the examples, but cant. Thanks for help!

UPDATED Full Model Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace photostorage.Models
{
    public class GlobalModel
    {
        public class PhotoViewModel
        {
            public photos Photos { get; set; }
            public profiles Profile { get; set; }
            public IQueryable<comments> Comments { get; set; }
            public IQueryable<photos> NextPrev { get; set; }
        }

        public class UserPhotoList
        {
            public IQueryable<photos> Photos { get; set; }
            public profiles Profile { get; set; }
        }

        public class UserProfileView
        {
            public IQueryable<photos> Photos { get; set; }
            public profiles Profile { get; set; }
        }

        public class GetLastComments
        {
            public IQueryable<photos> uPhoto { get; set; }
            public IQueryable<comments> uComments { get; set; }
        }
    }
}

Controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using photostorage.Models;

namespace photostorage.Controllers
{
    public class HomeController : Controller
    {
        private photostorageEntities db = new photostorageEntities();

        public ActionResult Index()
        {
            if(Request.IsAuthenticated) {
                GlobalModel.GetLastComments model = new GlobalModel.GetLastComments();
                var getPhotos = (from m in db.photos
                                    join n in db.comments on m.id equals n.photoid
                                    where n.ownerName == User.Identity.Name
                                    select new { 
                                        m.imgcrop, m.id, 
                                        n.commenterName, n.comment 
                                    }).Take(10);
                return View("Index_Auth", model);
            }else{

                ViewBag.Message = "Welcome to ASP.NET MVC!";
                return View("Index");
            }
        }

        public ActionResult About()
        {
            return View();
        }
    }
}
1
1
5/12/2011 9:49:00 PM

Accepted Answer

In this case you can make a "view model" that will only be used by your view and not by the rest of your application. Something like the following:

public class CommentsViewModel
{
    public int MessageId { get; set; }
    public string ImageCrop { get; set; }
    public string CommenterName { get; set; }
    public string Comment { get; set; }
}

Then change your query like so:

var getPhotos = (from m in db.photos
    join n in db.comments on m.id equals n.photoid
    where n.ownerName == User.Identity.Name
    orderby n.id descending
    select new CommentsViewModel { 
        ImageCrop = m.imgcrop, 
        MessageId = m.id, 
        CommenterName = n.commenterName, 
        Comment = n.comment 
    }).Take(10).ToList();

Make your view strongly typed to the new class and pass the data to it like so:

View("name_of_your_view", getPhotos);
3
5/12/2011 10:24:00 PM

Popular Answer

If you wanted to do this, like you had:

var getPhotos = (from m in db.photos
                                join n in db.comments on m.id equals n.photoid
                                where n.ownerName == User.Identity.Name
                                select new { 
                                    m.imgcrop, m.id, 
                                    n.commenterName, n.comment 
                                }).Take(10);

You could actually have this without creating a new "CommentsViewModel", but just use what should be the existing tables and models:

var getPhotos = (from m in db.Photos
                                join n in db.Comments on m.Id equals n.PhotoId
                                where n.OwnerName == User.Identity.Name
                                select new { 
                                    ImageCrop = m.ImageCrop, 
                                    Id = m.Id, 
                                    CommenterName = n.CommenterName, 
                                    Comment = n.Comment 
                                }).Take(10);

The models would be something like these examples, if you had a foreign key relationship on the Photo.Id to Comments.PhotoId:

public class Photos
{
    public int Id { get; set; }
    public string ImageCrop { get; set; }

    [ForeignKey("PhotoId")]
    public virtual Comments Comment { get; set; }
}
public class Comments
{
    public int Id { get; set; }
    public int PhotoId { get; set; }
    public string CommenterName { get; set; }
    public string OwnerName { get; set; }
    public string Comment { get; set; }
}

Just a note: The models you displayed in your question had none of these columns, yet you were building a query against them. It's best to remember to give a complete picture when asking for help.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow