none
Linq query is wrong RRS feed

  • Question

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


    I have the query like below but when i check the sql query is like wrong .
    var merchant = db.Merchants.Where(x => x.MerchantTypes.Any(r=>r.ID=id));

    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[LogoImgPath] AS [LogoImgPath], 
        [Extent1].[SliderImgPath] AS [SliderImgPath], 
        [Extent1].[SliderLink] AS [SliderLink], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[LastUpdatedDate] AS [LastUpdatedDate], 
        [Extent1].[LastUpdatedBy] AS [LastUpdatedBy]
        FROM [dbo].[Merchants] AS [Extent1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[MerchantTypeMerchants] AS [Extent2]
            WHERE ([Extent1].[ID] = [Extent2].[MerchantType_ID]) AND ([Extent2].[Merchant_ID] =@linq_xxx )
        ) 

    any idea I can get the SQL like below instead of the above 1

    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[LogoImgPath] AS [LogoImgPath], 
        [Extent1].[SliderImgPath] AS [SliderImgPath], 
        [Extent1].[SliderLink] AS [SliderLink], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[LastUpdatedDate] AS [LastUpdatedDate], 
        [Extent1].[LastUpdatedBy] AS [LastUpdatedBy]
        FROM [dbo].[Merchants] AS [Extent1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[MerchantTypeMerchants] AS [Extent2]
            WHERE ([Extent1].[ID] = [Extent2].[Merchant_ID]) AND ([Extent2].[MerchantType_ID] =@linq_xxx )
        ) 

    Tuesday, March 28, 2017 2:12 AM

All replies

  • Hi kyorilys,

    I create a demo and reproduce your issue on my side, Because use a wrong Fluent API, please modify it like below and change you table named MerchantTypeMerchants with right FK.

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

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 28, 2017 8:28 AM
    Moderator