Linq查询错误 [英] Linq query is wrong

问题描述

modelBuilder.Entity< Merchant>（）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .HasMany（e => e.MerchantTypes）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .WithMany（e => e.Merchants）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .Map（m => m.ToTable（" MerchantTypeMerchants"））       .MapLeftKey（" MerchantType_ID"）

         .MapRightKey（" Merchant_ID"））;

我有如下查询，但是当我检查sql查询时是错误的。

var merchant = db.Merchants.Where（x => x.MerchantTypes.Any（r => r.ID = id））;

SELECT 

&NBSP; &NBSP; [Extent1]。[ID] AS [ID]， 

  &NBSP; [Extent1]。[名称] AS [姓名]， 

  &NBSP; [Extent1]。[LogoImgPath] AS [LogoImgPath]， 

  &NBSP; [Extent1]。[SliderImgPath] AS [SliderImgPath]， 

  &NBSP; [Extent1]。[SliderLink] AS [SliderLink]， 

  &NBSP; [Extent1]。[IsDeleted] AS [IsDeleted]， 

  &NBSP; [Extent1]。[LastUpdatedDate] AS [LastUpdatedDate]， 

  &NBSP; [Extent1]。[LastUpdatedBy] AS [LastUpdatedBy]

  &NBSP;来自[dbo]。[商家] AS [Extent1]

  &NBSP;在哪里  EXISTS（选择 

     1 AS [C1]

     FROM [dbo]。[ MerchantTypeMerchants] AS [Extent2]

     WHERE（[Extent1]。[ID] = [Extent2]。[MerchantType_ID]）AND（[Extent2]。[Merchant_ID] = @linq_xxx）

  ） 

任何想法我都可以获得如下的SQL而不是上面的

SELECT 

  &NBSP; [Extent1]。[ID] AS [ID]， 

  &NBSP; [Extent1]。[名称] AS [姓名]， 

  &NBSP; [Extent1]。[LogoImgPath] AS [LogoImgPath]， 

  &NBSP; [Extent1]。[SliderImgPath] AS [SliderImgPath]， 

  &NBSP; [Extent1]。[SliderLink] AS [SliderLink]， 

  &NBSP; [Extent1]。[IsDeleted] AS [IsDeleted]， 

  &NBSP; [Extent1]。[LastUpdatedDate] AS [LastUpdatedDate]， 

  &NBSP; [Extent1]。[LastUpdatedBy] AS [LastUpdatedBy]

  &NBSP;来自[dbo]。[商家] AS [Extent1]

  &NBSP;在哪里  EXISTS（选择 

     1 AS [C1]

     FROM [dbo]。[ MerchantTypeMerchants] AS [Extent2]

     WHERE（[Extent1]。[ID] = [Extent2]。[Merchant_ID]）AND（[Extent2]。[MerchantType_ID] = @linq_xxx）

  ） 

解决方案

嗨kyorilys，

我创建了一个演示并在我身边重现你的问题，因为使用了错误的Fluent API，请按照下面的说明进行修改，并使用正确的FK更改名为MerchantTypeMerchants的表格。

 modelBuilder.Entity< Merchant>（）
 .HasMany（e => e.MerchantTypes）
 .WithMany（e => e.Merchants）
 .Map（m => m.ToTable（" MerchantTypeMerchants"）
 .MapLeftKey（" Merchant_ID"）
 .MapRightKey（" Merchant） Type_ID"））; 

祝你好运，

Cole Wu

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 )
    ) 

解决方案

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

这篇关于Linq查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！