destructi6n destructi6n - 2 months ago 10
C# Question

EF Code First One To Many

How can I get a class to have a collection composed of another model, and have that be populated when I fetch my original model. I have a Wishlist, and there are 0 or many products inside that wishlist. What does my data annotation or fluent API need to say in order for that to populate if I were to do a db.Wishlist.find(id). Here is what I currently have in my wishlist model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Models
{
[Table("Wishlist")]
public class Wishlist
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[ScaffoldColumn(false)]
public int ID { get; set; }

[StringLength(100)]
public string Name { get; set; }

public int ProductID { get; set; }

public virtual ICollection<Product> Product { get; set; }

public int CustomerID { get; set; }

[Required]
public Customer Customer { get; set; }

public virtual List<Product> Products { get; set; }

[DisplayFormat(DataFormatString = "{0:f}")]
public DateTime CreateDate { get; set; }


[DisplayFormat(DataFormatString = "{0:f}")]
public DateTime LastModifiedDate { get; set; }


}
}


what is required to get the products to populate as either a collection or as a list. What is the correct approach to achieving this? I'm aware one of the collections of products must go, just not sure which and what is needed.

UPDATE: added display of my product model.

namespace Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;

[Table("Product")]
public partial class Product
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Product()
{
OrderLines = new HashSet<OrderLine>();
SKU_Table = new HashSet<Sku>();
XREF_CatalogProduct = new HashSet<XREF_CatalogProduct>();
ProductImages = new List<ProductImage>();
}

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }

[NotMapped]
public string FormattedPrice { get { return this.Price.ToString("C"); } }

[Required]
[MaxLength]
public string PageURL { get; set; }

[Required]
[StringLength(250)]
public string Name { get; set; }

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

public string Description { get; set; }

public int CategoryID { get; set; }

[Column(TypeName = "money")]
[DisplayFormat(DataFormatString = "${0:#,0}", ApplyFormatInEditMode = true)]
public decimal Price { get; set; }

public DateTime? DateCreated { get; set; }

public DateTime? DateModified { get; set; }

[Required]
public bool Featured { get; set; }

public virtual string ImagePath { get; set; }

public virtual Category Category { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<OrderLine> OrderLines { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Sku> SKU_Table { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<XREF_CatalogProduct> XREF_CatalogProduct { get; set; }

public virtual ICollection<ProductImage> ProductImages { get; set; }
}
}

Answer

You have to setup a M: M relationship with the Wishlist : Product.Code first will create a Junction table for you if you use DataAnnotation.

Using DataAnnotation :

[Table("Wishlist")]
public class Wishlist
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [ScaffoldColumn(false)]
    public int ID { get; set; }

    [StringLength(100)]
    public string Name { get; set; }

    public int CustomerID { get; set; }

    [Required]
    public Customer Customer { get; set; }

    [DisplayFormat(DataFormatString = "{0:f}")]
    public DateTime CreateDate { get; set; }


    [DisplayFormat(DataFormatString = "{0:f}")]
    public DateTime LastModifiedDate { get; set; }

    public virtual ICollection<Product> Products { get; set; }

}

And

    [Table("Product")]
    public partial class Product
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Product()
        {
            OrderLines = new HashSet<OrderLine>();
            SKU_Table = new HashSet<Sku>();
            XREF_CatalogProduct = new HashSet<XREF_CatalogProduct>();
            ProductImages = new List<ProductImage>();
            this.Wishlists = new HashSet<Wishlist>();

        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        [NotMapped]
        public string FormattedPrice { get { return this.Price.ToString("C"); } }

        [Required]
        [MaxLength]
        public string PageURL { get; set; }

        [Required]
        [StringLength(250)]
        public string Name { get; set; }

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

        public string Description { get; set; }

        public int CategoryID { get; set; }

        [Column(TypeName = "money")]
        [DisplayFormat(DataFormatString = "${0:#,0}", ApplyFormatInEditMode = true)]
        public decimal Price { get; set; }

        public DateTime? DateCreated { get; set; }

        public DateTime? DateModified { get; set; }

        [Required]        
        public bool Featured { get; set; }

        public virtual string ImagePath { get; set; }

        public virtual Category Category { get; set; }        

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<OrderLine> OrderLines { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Sku> SKU_Table { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<XREF_CatalogProduct> XREF_CatalogProduct { get; set; }

        public virtual ICollection<ProductImage> ProductImages { get; set; }

        public virtual ICollection<Wishlist> Wishlists { get; set; }


    }

EF Query : to retrieve wishlist according to the product Id

var prod_id=1; // your product id

var query= from wishlist in db.Wishlists
           where wishlist.Products.Any(c=>c.Product_ID== prod_id)
           select wishlist;

Using Fluent Api :

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Wishlist>()
                    .HasMany<Product>(s => s.Products)
                    .WithMany(c => c.Wishlists)
                    .Map(cs =>
                            {
                                cs.MapLeftKey("WishlistRefId");
                                cs.MapRightKey("ProductRefId");
                                cs.ToTable("WishlistProduct");
                            });

    }

EF Query : to retrieve wishlist according to the product Id

var prod_id=1; // your product id

var query= from wishlist in db.Wishlists
           where wishlist.Products.Any(c=>c.ProductRefId == prod_id)
           select wishlist;
Comments