CODEWITHSUNDEEP
c#entity-frameworklinq
kyorilys
kyorilys

Reputation: 832

c# entities many to many nested entities count is zero

I want to get merchant from the merchant type where the relationship between is many to many.

I got an id which is the merchant type id and I want to get all the merchant type that have this ID.

The merchant type should have the collection of Merchant that is inside the merchant type.

Let say merchant "a" and "b" has merchant type "g" with id =2

 var result=db.MerchantTypes.Where(p => p.ID == id)
                            .Include(c => c.Merchants).FirstOrDefault();

The code above should return me the result of merchant type and inside got a list of the merchant.

 public partial class MerchantType
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public MerchantType()
            {
                Merchants = new HashSet<Merchant>();
            }


            public int ID { get; set; }

            public string Type { get; set; }
            public string Description { get; set; }

            public bool IsDeleted { get; set; }
            public DateTime LastUpdatedDate { get; set; }
            public string LastUpdatedBy { get; set; }

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

public partial class Merchant
    {

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Merchant()
        {
            MerchantTypes = new HashSet<MerchantType>();
        }


        public int ID { get; set; }
        public string Name { get; set; }
        public string LogoImgPath { get; set; } 
        public string SliderImgPath { get; set; }
        public string SliderLink { get; set; }
        public bool IsDeleted { get; set; }
        public DateTime LastUpdatedDate { get; set; }
        public string LastUpdatedBy { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertieShouldBeReadOnly")]
        public virtual ICollection<MerchantType> MerchantTypes { get; set; }
    }

This is the relationship between merchant type and merchant. Which is merchant can have many types and type inside can have many merchants. (many to many relationships). 

 modelBuilder.Entity<Merchant>()
                .HasMany(e => e.MerchantTypes)
                .WithMany(e => e.Merchants)
                .Map(m => m.ToTable("MerchantTypeMerchants")
                .MapLeftKey("MerchantType_ID")
                .MapRightKey("Merchant_ID"));

Anyone know what is the problems why I cant get the merchant inside the merchant type and how to solve it?

Updated on 28.3.2017

{SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[Type] AS [Type], 
    [Project1].[Description] AS [Description], 
    [Project1].[IsDeleted] AS [IsDeleted], 
    [Project1].[LastUpdatedDate] AS [LastUpdatedDate], 
    [Project1].[LastUpdatedBy] AS [LastUpdatedBy], 
    [Project1].[C1] AS [C1], 
    [Project1].[ID1] AS [ID1], 
    [Project1].[Name] AS [Name], 
    [Project1].[LogoImgPath] AS [LogoImgPath], 
    [Project1].[SliderImgPath] AS [SliderImgPath], 
    [Project1].[SliderLink] AS [SliderLink], 
    [Project1].[IsDeleted1] AS [IsDeleted1], 
    [Project1].[LastUpdatedDate1] AS [LastUpdatedDate1], 
    [Project1].[LastUpdatedBy1] AS [LastUpdatedBy1]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Type] AS [Type], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[LastUpdatedDate] AS [LastUpdatedDate], 
        [Extent1].[LastUpdatedBy] AS [LastUpdatedBy], 
        [Join1].[ID] AS [ID1], 
        [Join1].[Name] AS [Name], 
        [Join1].[LogoImgPath] AS [LogoImgPath], 
        [Join1].[SliderImgPath] AS [SliderImgPath], 
        [Join1].[SliderLink] AS [SliderLink], 
        [Join1].[IsDeleted] AS [IsDeleted1], 
        [Join1].[LastUpdatedDate] AS [LastUpdatedDate1], 
        [Join1].[LastUpdatedBy] AS [LastUpdatedBy1], 
        CASE WHEN ([Join1].[Merchant_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[MerchantTypes] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[Merchant_ID] AS [Merchant_ID], [Extent3].[ID] AS [ID], [Extent3].[Name] AS [Name], [Extent3].[LogoImgPath] AS [LogoImgPath], [Extent3].[SliderImgPath] AS [SliderImgPath], [Extent3].[SliderLink] AS [SliderLink], [Extent3].[IsDeleted] AS [IsDeleted], [Extent3].[LastUpdatedDate] AS [LastUpdatedDate], [Extent3].[LastUpdatedBy] AS [LastUpdatedBy]
            FROM  [dbo].[MerchantTypeMerchants] AS [Extent2]
            INNER JOIN [dbo].[Merchants] AS [Extent3] ON [Extent3].[ID] = [Extent2].[MerchantType_ID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[Merchant_ID]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC}

Upvotes: 0

Views: 103

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109099

Your have to swap the key column names:

modelBuilder.Entity<Merchant>().HasMany(e => e.MerchantTypes)
            //      Left                       Right
            .WithMany(e => e.Merchants)
            .Map(m => m.ToTable("MerchantTypeMerchants")
            .MapLeftKey("Merchant_ID")          // was: MerchantType_ID
            .MapRightKey("MerchantType_ID"));   // was: Merchant_ID

Upvotes: 1

Related Questions