ASP.NET MVC - Select from two tables then compare

Question

I have this Wishlist class:

public class Wishlist
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    [ForeignKey("ProductId")]
    public virtual Product Product { get; set; }
    public string PersonId { get; set;}
    [ForeignKey("PersonId")]
    public virtual ApplicationUser Person { get; set; }
}

public class Product
{
    public string Title { get; set; }
    public int CategoryId { get; set; }
    [ForeignKey("CategoryId")]
    public virtual Category Category { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public bool Featured { get; set; }
    public bool Sold { get; set; }
    public string Image { get; set; }
    [NotMapped]
    public HttpPostedFileBase UploadImage { get; set; }
    public string PersonId { get; set; }
    [ForeignKey("PersonId")]
    public virtual ApplicationUser Person { get; set; }
}

I'm trying to get all the products added to dbcontext.Wishlist table, by using:

string currentUserId = User.Identity.GetUserId();
var list2 = DbContext.Wishlist.Where(p => p.PersonId == currentUserId).ToList();
var list3 = (from p in DbContext.Products
             from w in list2
             where p.PersonId == w.PersonId
             select new Models.Response.ProductIndexResponse()
             {
               Id = p.Id,
               Image = p.Image,
               Title = p.Title,
               Price = p.Price
             }).ToList();

But I get this error:

Unable to create a constant value of type 'Finder.Models.Wishlist'. Only primitive types or enumeration types are supported in this context.'

What am I doing wrong?

on which line that error being thrown? is it on list3? i had doubts on select new Models.Response.ProductIndexResponse() whether it works in sql query. — Bagus Tesa
– Bagus Tesa, Commented Apr 10, 2022 at 12:59
@BagusTesa I don't know if external links are allowed, but :imgur.com/a/J5PywuU — EL02
– EL02, Commented Apr 10, 2022 at 13:03
you can edit your question and put that image on the question - given you have provided the code in text, error message should be fine. also, see Yong's answer. i missed that you passed list2 on list3, which also an illegal move XD — Bagus Tesa
– Bagus Tesa, Commented Apr 10, 2022 at 13:08

Volodymyr Liashenko · Accepted Answer · 2022-04-10 13:13:27Z

1

Can you try to simplify you query like this:

var wishPorducts = DbContext.Wishlist
    .Where(p => p.PersonId == currentUserId)
    .Select(x => new Models.Response.ProductIndexResponse()
         {
           Id = x.Product.Id,
           Image = x.Product.Image,
           Title = x.Product.Title,
           Price = x.Product.Price
         })
     .ToList();

The query above is much simpler and efficient and may help you better understand where is the issue if it still there.

answered Apr 10, 2022 at 13:13

Volodymyr Liashenko

3412 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yong Shun · Accepted Answer · 2022-04-10 13:03:33Z

1

I don't think you can pass the list of WishList to LINQ.

Instead, modify your LINQ query with JOIN as below:

var query = (from p in DbContext.Products
             join w in DbContext.Wishlist on p.PersonId equals w.PersonId
             where p.PersonId == currentUserId
             select new
             {
               Id = p.Id,
               Image = p.Image,
               Title = p.Title,
               Price = p.Price
             }).ToList();

var result = query
    .Select(x => Models.Response.ProductIndexResponse()
    {
        Id = x.Id,
        Image = x.Image,
        Title = x.Title,
        Price = x.Price
    })
    .ToList();

answered Apr 10, 2022 at 13:03

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

ASP.NET MVC - Select from two tables then compare

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related