locked
getting array of ID fields in many to many relationship in Entity framework RRS feed

  • Question

  • User-1556678718 posted

    Hi,

    I have in the database table Deal having DealId and table Product having ProductId. I have table ProductDeal having ProductId and DealId for the many to many relation between the tables product and deal. Now in the models I have 

       [Table("Deal")]
        public class Deal
        {
            [Key]
            public int DealId { get; set; }
            public string Title { get; set; }
            public Dealproduct[] DealProducts { get; set; }
    
        }
    
    
    
        public class Dealproduct
        {
            public int ProductId { get; set; }
        }
    
     [Table("Product")]
        public class Product
        {
            [Key]
            public int ProductID { get; set; }
            public string ProductTitle { get; set; }
           
        }
    

    Now in the Context I have 

            public DbSet<Product> Prioducts { get; set; }
            public DbSet<Deal> Deals { get; set; }
            public DbSet<ProductDeal> ProductDeals { get; set; }

    when I'm loading the deals:

                var deals = new List<Deal>();
                try
                {
                    using (var context = new myContext(connString))
                    {
                        var query = context.Deals;
                        deals = query.ToList();
                    }
    
                }
                catch (Exception exc)
                {}

    but in this way I'm not getting the list of products for a deal because it is saved in the productDeal table. How do I load the list of products for a deal? I need only list of Ids, not complete list of products.

    Thanks

    Monday, November 11, 2019 6:44 AM

Answers

  • User665608656 posted

    Hi RioDD,

    According to your description, are you using code first to create a many to many relationship between deal and product tables?

    If so, there are some issues with the class structure you create. You don't need to create productdeal class actively.

    When you create the relationship between deal and the product class, then execute the updatebase statement, the corresponding productdeal table will be created automatically.

        [Table("Product")]
        public class Product
        {
            public Product()
            {
                this.Deals = new HashSet<Deal>();
            }
    
            [Key]
            public int ProductID { get; set; }
            public string ProductTitle { get; set; }
            public virtual ICollection<Deal> Deals { get; set; }
        }
       [Table("Deal")]
        public class Deal
        {
            public Deal()
            {
                this.Products = new HashSet<Product>();
            }
    
            [Key]
            public int DealId { get; set; }
            public string Title { get; set; }
            public virtual ICollection<Product> Products { get; set; }
    
        }
            public DbSet<Deal> Deals { get; set; }
            public DbSet<Product> Products { get; set; }

    You can use codel to store data:

                MyContext context = new MyContext();
                var  dealClass = new Deal {  Title = "deal1" };
                Product pc1 = new Product {  ProductTitle = "pc1" };
                Product pc2 = new Product { ProductTitle = "pc2" };
                dealClass.Products.Add(pc1);
                dealClass.Products.Add(pc2);
                context.Deals.Add(dealClass);
                context.SaveChanges();
    

    After I run this code, my three tables of database are as follows:

    Deal table:

    Product table:

    ProductDeal table:

    More details about how to create many to many relationships by using code first, you can refer to this link:

    Configure Many-to-Many Relationships in Code-First

    After the above premise is completed, you can use linq to realize the realization of multiple products under one deal data and only display the content of ProductID.

      var query = (from e in context.Deals
                         select new
                         {
                             DealId = e.DealId,
                             ProductID = e.Products.Select(c => c.ProductID) 
                         }).ToList();

    Here is the result of this query:

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 12, 2019 5:37 AM