locked
Return a list of product objects that have a product ID that exists as a product ID object another list of objects using LINQ extension method syntax. RRS feed

  • Question

  • User-139700499 posted

    Hi, 

    I have been working on a query to return an IEnumerable list of Booking Request objects that have a product ID attribute value that exists in the 'MyProducts' list as the ID for the objects using LINQ extension method syntax. 

    My application is a product bookings app. The current user needs to view requests made towards the products that he has uploaded - products have an OwnerID attribute. 

    Currently my linq query is the following: 

    var myProducts = (await db.Products.Where(x => x.OwnerId == userId).ToListAsync());
    

    var requests = (await db.BookingRequests.Where(x => x.CustomerId != userId && myProducts.Any(p => p.Id == x.ProductId)).ToListAsync()).Select<BookingRequest, BookingRequestViewModel>(b => b);

    var requests = (await db.BookingRequests.Where(x => x.CustomerId != userId && myProducts.Any(p => p.Id == x.ProductId)).ToListAsync()).Select<BookingRequest, BookingRequestViewModel>(b => b);

    What it does is it tries to get the booking requests where the customer ID is not equal to current user (so this gets requests made by other users) and then does another 'where' on the requests where there are any products in the myProducts list where the product Id is equal to the request's ProductId attribute. 

    The select then converts the database model to one for use with the viewmodel. 

    I get the following error when loading the view which is trying to use the requests model:

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

    Monday, November 27, 2017 7:28 PM

Answers

  • User-335504541 posted

    Hi aladdin1996,

    I have tested your code. The reason for your code cannot work is myProducts is a collection of objects in memory and you cannot use a set of data in the database with another set of data that is in memory. 

    Please try to use the following code:

    var requests = db.BookingRequests.Where(x => x.CustomerId != userId && db.Products.Where(xy => xy.OwnerId == userId).Any(p => p.Id == x.ProductId)).ToList().Select<BookingRequest, BookingRequestViewModel>(b => b);
    

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 29, 2017 5:28 AM

All replies

  • User-335504541 posted

    Hi aladdin1996,

    What's the BookingRequestViewModel?

    Could you show me your models?

    Best Regards,

    Billy

    Tuesday, November 28, 2017 3:19 AM
  • User-139700499 posted

    Hi Billy,

    Here are my models:

    public class Product
        {
            public int Id { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            [StringLength(256)]
            [Display(Name = "Title")]
            public string Title { get; set; }
    
            [Required]
            [DataType(DataType.MultilineText)]
            [StringLength(256)]
            [Display(Name = "Description")]
            public string Description { get; set; }
    
            [Required]
            [DataType(DataType.Currency)]
            [Display(Name = "Price")]
            public double Price { get; set; }
              
            public string OwnerId { get; set; }
    
            [Required]
            [Display(Name = "Main Photo")]
            public byte[] MainPhoto { get; set; }
    
            [Required]
            [Display(Name ="Product Photos")]
            public List<Image> Images { get; set; }
    
        }
    
        public class Image
        {
            public int Id { get; set; }
            [Required]
            public byte[] ImageByteData { get; set; }
            public int ProductId { get; set; }
        }
    
        // Users will create booking request objects on each initial booking
        public class BookingRequest
        {
    
            public int Id { get; set; }
            
            public Product Product { get; set; }
            
            [Required]
            public int ProductId { get; set; }
    
            [Required]
            public string CustomerId { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            public string VenueFirstLineOfAddress { get; set; }
            
            [DataType(DataType.Text)]
            public string VenueSecondLineOfAddress { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            public string City { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            public string Postcode { get; set; }
    
            [Required]
            [DataType(DataType.DateTime)]
            public DateTime Date { get; set; }
        
            [Required]
            [DataType(DataType.DateTime)]
            public DateTime From { get; set; }
    
            [Required]
            [DataType(DataType.DateTime)]
            public DateTime To { get; set; }
    
            [DataType(DataType.Text)]
            public string CustomProposal { get; set; }
            
            [Required]
            public bool IsApproved { get; set; }
    
            [Required]
            public string Status { get; set; } 
    
            public double CustomPrice { get; set; }
    
            [Required]
            public double TotalRequestPrice { get; set; } 
    
            public string RejectionMessage { get; set; }
        }

    Here is the booking view model: 

    public class BookingRequestViewModel
        {
            public int Id { get; set; }
            public int ProductId { get; set; }
            public string CustomerId { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            [Display(Name = "First Line of Venue Address")]
            public string VenueFirstLineOfAddress { get; set; }
            
            [DataType(DataType.Text)]
            [Display(Name = "Second Line of Venue Address (not required)")]
            public string VenueSecondLineOfAddress { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            [Display(Name = "Venue City")]
            public string City { get; set; }
    
            [Required]
            [DataType(DataType.Text)]
            [Display(Name = "Venue Postcode")]
            public string Postcode { get; set; }
    
            [Required]
            [DataType(DataType.DateTime)]
            [Display(Name = "Date")]
            public DateTime Date { get; set; }
    
            [Required]
            [DataType(DataType.DateTime)]
            [Display(Name = "From")]
            public DateTime From { get; set; }
    
            [Required]
            [DataType(DataType.DateTime)]
            [Display(Name = "To")]
            public DateTime To { get; set; }
    
            [DataType(DataType.Text)]
            [Display(Name = "Custom Proposal")]
            public string CustomProposal { get; set; }
    
            public bool IsApproved { get; set; }
            public string Status { get; set; }
            public double CustomPrice { get; set; }
            public double TotalRequestPrice { get; set; }
            public string RejectionMessage { get; set; }
    
            public static implicit operator BookingRequest(BookingRequestViewModel viewModel)
            {
                var model = new BookingRequest()
                {
                    Id = viewModel.Id,
                    ProductId = viewModel.ProductId,
                    CustomerId = viewModel.CustomerId,
                    VenueFirstLineOfAddress = viewModel.VenueFirstLineOfAddress,
                    VenueSecondLineOfAddress = viewModel.VenueSecondLineOfAddress,
                    City = viewModel.City,
                    Postcode = viewModel.Postcode,
                    Date = viewModel.Date,
                    From = viewModel.From,
                    To = viewModel.To,
                    CustomProposal = viewModel.CustomProposal,
                    IsApproved = viewModel.IsApproved,
                    Status = viewModel.Status,
                    CustomPrice = viewModel.CustomPrice,
                    TotalRequestPrice = viewModel.TotalRequestPrice,
                    RejectionMessage = viewModel.RejectionMessage
                };
    
                return model;
            }
    
    
            public static implicit operator BookingRequestViewModel(BookingRequest model)
            {
                var viewModel = new BookingRequestViewModel()
                {
                    Id = model.Id,
                    ProductId = model.ProductId,
                    CustomerId = model.CustomerId,
                    VenueFirstLineOfAddress = model.VenueFirstLineOfAddress,
                    VenueSecondLineOfAddress = model.VenueSecondLineOfAddress,
                    City = model.City,
                    Postcode = model.Postcode,
                    Date = model.Date,
                    From = model.From,
                    To = model.To, 
                    CustomProposal = model.CustomProposal,
                    IsApproved = model.IsApproved,
                    Status = model.Status,
                    CustomPrice = model.CustomPrice,
                    TotalRequestPrice = model.TotalRequestPrice,
                    RejectionMessage = model.RejectionMessage
                };
    
                return viewModel;
            }
    
    
        
    Tuesday, November 28, 2017 10:41 AM
  • User-335504541 posted

    Hi aladdin1996,

    I have tested your code. The reason for your code cannot work is myProducts is a collection of objects in memory and you cannot use a set of data in the database with another set of data that is in memory. 

    Please try to use the following code:

    var requests = db.BookingRequests.Where(x => x.CustomerId != userId && db.Products.Where(xy => xy.OwnerId == userId).Any(p => p.Id == x.ProductId)).ToList().Select<BookingRequest, BookingRequestViewModel>(b => b);
    

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 29, 2017 5:28 AM
  • User-139700499 posted

    Hi Billy, 

    Thank you for that. It works. I didn't know that kind of restriction existed with ASP.NET, but it's good to now know. 

    Thank you so much again. 

    Wednesday, November 29, 2017 5:41 PM