代码之家  ›  专栏  ›  技术社区  ›  Jose L. Garcia

无法理解此EF Core 2关系行为

  •  1
  • Jose L. Garcia  · 技术社区  · 8 年前

    首先使用EF core 2代码,我有4个实体:

    客户有一组联系人和地址。 客户有一个PaymentTerm,PaymentTerm有一个客户集合(不需要,因为我不想按PaymentTerm搜索)。

    I'want to retrieve all customers with Contacts and address and his PaymentTerm(我想检索所有具有联系人和地址及其付款期限的客户):

    var customers = await _context.Customers
                                  .Include(c => c.Contacts)
                                  .Include(c => c.Addresses)
                                  .Include(c => c.PaymentTerm)
                                  .ToListAsync();
    

    以下是dbcontext配置: 客户:

    x.HasMany(e => e.Contacts)
                    .WithOne(e => e.Customer)
                    .OnDelete(DeleteBehavior.Cascade);
    x.HasMany(e => e.Addresses)
                    .WithOne(e => e.Customer)
                    .OnDelete(DeleteBehavior.Cascade);
    

    付款期限:

    x.HasMany(e => e.Customers)
                    .WithOne(e => e.PaymentTerm)
                    .OnDelete(DeleteBehavior.Restrict);
    

    问题:查询未返回任何记录。这是生成的SQL查询:

    SELECT [c].[CustomerId], [c].[Cif], [c].[CommercialName], [c].[Created], [c].[CreatedBy], [c].[CustomerName], [c].[Deleted], [c].[DeletedBy], [c].[DeletedDate], [c].[Modified], [c].[ModifiedBy], [c].[PaymentTermId], [c].[TaxFree], [c].[Vat], [t].[PaymentTermId], [t].[Created], [t].[CreatedBy], [t].[Days], [t].[Deleted], [t].[DeletedBy], [t].[DeletedDate], [t].[Modified], [t].[ModifiedBy], [t].[PaymentTermName]
          FROM [Customers] AS [c]
          INNER JOIN (
              SELECT [p].[PaymentTermId], [p].[Created], [p].[CreatedBy], [p].[Days], [p].[Deleted], [p].[DeletedBy], [p].[DeletedDate], [p].[Modified], [p].[ModifiedBy], [p].[PaymentTermName]
              FROM [PaymentTerms] AS [p]
              WHERE [p].[Deleted] = 0
          ) AS [t] ON [c].[PaymentTermId] = [t].[PaymentTermId]
          WHERE [c].[Deleted] = 0
          ORDER BY [c].[CustomerId]
    

    没有添加带有联系人和地址且未返回记录的加入。

    如果我删除 .Include(c => c.PaymentTerm) ,具有联系人和地址的客户将被罚款。

    请你解释一下我做错了什么?

    1 回复  |  直到 8 年前
        1
  •  1
  •   Vivek Nuna Chetan sabhaya    8 年前

    您的查询正在进行内部联接,而必须使用左联接。您可以通过使用 DefaultIfEmpty