Pass a column value to model from a different table based on foreign key id

Question

I have a products table which has a CategoryId which represents the respective primary key from the Categories table.

ProductViewModel

public ProductVM(ProductDTO productDTO)
{
    Id = productDTO.Id;
    Name = productDTO.Name;
    Description = productDTO.Description;
    Price = productDTO.Price;
    CategoryId = productDTO.CategoryId;
    ImageName = productDTO.ImageName;
}

public int Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public string Description { get; set; }
[Required]
public decimal Price { get; set; }

public int? CategoryId { get; set; }
public IEnumerable<SelectListItem> Categories { get; set; }
public string ImageName { get; set; }
public IEnumerable<string> GalleryImages { get; set; }

Product DTO

public class ProductDTO
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Slug { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public string ImageName { get; set; }

    [ForeignKey("CategoryId")]
    public virtual CategoryDTO Category { get; set; }
}

This is how I get a list of products:

List<ProductVM> productVM;
using (Db db = new Db())
{
    productVM = db.Products
        .ToArray()
        .Select(x => new ProductVM(x))
        .ToList();
}

As you can see I am passing the CategoryId around and I can display it in my views with the ProductVM ViewModel, but I also want to get the Name of the category in there as well.

I can think of some hacks, e.g. accessing the DB from the constructor in the ViewModel based on the CategoryId and assigning it that way, but I wanna see if there is a more elegant solution?

Bottom line - I have a Name column in my Categories table and I want to pass that name to the ProductVM in a most effective way.

`productVM = db.Products.Include(x => x.Category).Select(...` and add a property for `CategoryName` so you can use `CategoryName = productDTO.Category.Name`; — , Nov 09 '16 at 10:51
@StephenMuecke I do not get intellisense to include anything, `productVM = db.Products .Include(x => x.)` gives me an error. I have also added the CategoryName proeprty and updated my Q with it. — frc, Nov 09 '16 at 11:29
What error? (and have you included `using System.Data.Entity;`)? - and you can always use `.Include("Category")` instead — , Nov 09 '16 at 11:32
@StephenMuecke No I had not and that was the problem. Works great, thanks! — frc, Nov 09 '16 at 11:39

score 1 · Accepted Answer · 2016-11-09T12:38:02.423

1

Add a property to you model for the category name, say

public string CategoryName { get; set; }

and modify the constructor to populate it

public ProductVM(ProductDTO productDTO)
{
    CategoryName  = productDTO.Category.Name;
    ....

and modify the query to

List<ProductVM> productVM = db.Products.AsEnumerable().Include(x => x.Category)
    .Select(x => new ProductVM(x)).ToList();

Note that you view model also required a parameterless constructor if your using this for editing otherwise an exception will be thrown in the POST method.

Note also that you do not need the using (Db db = new Db())

edited Nov 09 '16 at 12:38

answered Nov 09 '16 at 12:04

Have you run this?Don't have time to test it now but I don't think this works.You can't use an constructor on LinqToEntities.(Of course you need to load the Categories) – George Vovos Nov 09 '16 at 12:06
@GeorgeVovos Of course it works (and OP has already confirmed it in the comments). And of course you can use a constructor – Nov 09 '16 at 12:07
The OP has a ToArray() before the select...So that is Linq to Objects – George Vovos Nov 09 '16 at 12:08
@GeorgeVovos And the answer does not (but that's really irrelevant) – Nov 09 '16 at 12:08
Hmm I was almost certain that it doesn't work because it can't generate the SQL.Anyway,I'll try it myself before I upvote... – George Vovos Nov 09 '16 at 12:10
As expected this doesn't work. Please check my full example in my second answer – George Vovos Nov 09 '16 at 12:27
Of course now (With .AsEnumerable) it works but it's not better than a ToArray/ToList.It still does a SELECT * .Anyway since it is a valid answer you get an upvote from me but @frc should not use this in production – George Vovos Nov 09 '16 at 12:46
@GeorgeVovos, I agree using a constructor is not ideal, but more so because OP has a `SelectList` so a loop is still required after the query to populate that property – Nov 09 '16 at 12:51
@StephenMuecke I did it without `AsEnumerable()`, what is the point of it? Also, should I just stop using using (Db db = new Db()) alltogether in MVC with EF anf LINQ? – frc Nov 09 '16 at 12:57
@frc, You don't really need it (and it can cause problems as you discovered in your previous question). And what you really should be doing is first generating the `SelectList` (say) var categories = new SelectList(...);` and then using `.Select(x => new ProductVM{ Id = x,Id, ...., Categories = categories }).ToList();` otherwise you need another loop to populate the `SelectList` – Nov 09 '16 at 13:03

score 0 · Answer 2 · answered Nov 09 '16 at 13:10

The best solution is the following (I removed all other answers)

namespace Test
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;

    internal class Program
    {
        private static void Main(string[] args)
        {
            List<ProductVM> productVM;
            //It doesn't matter if you use a using block or not
            using (Db db = new Db())
            {
                db.Database.Log = Console.WriteLine;//To see the generated SQL

                productVM = db.Products
                              .Include(p => p.Category)
                              .Select(p => new ProductVM
                              {
                                  Id = p.Id,
                                  Name = p.Name,
                                  Description = p.Description,
                                  Price = p.Price,
                                  CategoryId = p.CategoryId,
                                  CategoryName = p.Category.Name,
                                  ImageName = p.ImageName,
                              }).ToList();



            }

            Console.ReadKey();
        }
    }

    public class Db : DbContext
    {
        public DbSet<ProductDTO> Products { get; set; }
        public DbSet<CategoryDTO> Categories { get; set; }
    }

    public class ProductDTO
    {
        [Key]
        public int Id { get; set; }

        public string Name { get; set; }
        public string Slug { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
        public int CategoryId { get; set; }
        public string ImageName { get; set; }

        [ForeignKey("CategoryId")]
        public virtual CategoryDTO Category { get; set; }
    }

    public class CategoryDTO
    {
        [Key]
        public int Id { get; set; }

        public string Name { get; set; }
    }

    public class ProductVM
    {
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public string Description { get; set; }

        [Required]
        public decimal Price { get; set; }

        public int? CategoryId { get; set; }
        public string CategoryName { get; set; }
        public string ImageName { get; set; }
    }
}

Pass a column value to model from a different table based on foreign key id

2 Answers2