如何在同一个表上创建数据或实体关系，并在第一个联接表上联接另一个表

Question

我在与同一个表建立数据或实体关系并将另一个表连接到第一个内部连接表时遇到问题。

我想通过以下方式获得查询结果：

SELECT [n].[ISCInstanceId], [n].[NavPointId], [n].[ParentNPId], [n].[DataPointId], [n].[NodeOrder], [n].[RefNumber], [n].[TreeTitle], [n].[IsArchived], [n].[IsInNavbar], [i].[Name]
FROM [NavPoints] AS [n]
INNER JOIN [NavPoints] AS [n0] ON [n].[ParentNPId] = [n0].[NavPointId]
INNER JOIN [ISCInstances] AS [i] ON [n0].[ISCInstanceId] = [i].[ISCInstanceId]
WHERE (([n].[IsArchived] = CAST(0 AS bit)) AND ([n].[IsInNavbar] = CAST(1 AS bit))) AND ([n0].[ISCInstanceId] = @__IscinstanceId_0)
ORDER BY [n].[ParentNPId], [n].[NodeOrder]

但我得到了这个：

SELECT [n].[ISCInstanceId], [n].[NavPointId], [n].[ParentNPId], [n].[DataPointId], [n].[NodeOrder], [n].[RefNumber], [n].[TreeTitle], [n].[IsArchived], [n].[IsInNavbar], [i].[Name]
FROM [NavPoints] AS [n]
INNER JOIN [NavPoints] AS [n0] ON [n].[ParentNPId] = [n0].[NavPointId]
INNER JOIN [ISCInstances] AS [i] ON [n].[ISCInstanceId] = [i].[ISCInstanceId]
WHERE (([n].[IsArchived] = CAST(0 AS bit)) AND ([n].[IsInNavbar] = CAST(1 AS bit))) AND ([n0].[ISCInstanceId] = @__IscinstanceId_0)
ORDER BY [n].[ParentNPId], [n].[NodeOrder]

问题是部分或行INNER JOIN[ISIInstances]AS[i]ON[n]。[IISCInstanceId]=[i]。[IISCInstanceId]开关是我得到的查询，它没有加入第一个内部联接表，我希望它是这样的inner JOIN[IISCInstances]AS[I]ON[n0]。[IISCInstanceId]=[i]。[IISCInstanceId]

DB上下文

modelBuilder.Entity<NavPoint>(entity =>
{
...
entity.HasOne(d => d.Iscinstances)
.WithMany(p => p.NavPoints)
.HasForeignKey(d => d.IscinstanceId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_NavPoints_Iscinstances");
entity.HasOne(d => d.NavPointsParent)
.WithMany(p => p.NavPoints)
.HasForeignKey(d => d.ParentNpid)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_NavPoints_NavPoints");
});

类模型

// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>
#nullable disable
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
namespace Odysseus_API.Models
{
public partial class NavPoint
{
public NavPoint()
{
NavPoints = new HashSet<NavPoint>();
}
[key]
public int NavPointId { get; set; }
public int ParentNpid { get; set; }
...
[ForeignKey("ParentNpid")]
public virtual NavPoint NavPointsParent { get; set; }
public virtual Iscinstance Iscinstances { get; set; }
[ForeignKey("NavPointId")]
public virtual ICollection<NavPoint> NavPoints { get; set; }
}
}

控制器

[HttpGet]
public async Task<ActionResult<NavPoint>> GetNavPoint(int IscinstanceId)
{
if (_context.Users == null)
{
return NotFound();
}
var navpoint = await _context.NavPoints
.Where(c => c.IsArchived == false  && c.IsInNavbar == true && c.NavPointsParent.IscinstanceId == IscinstanceId)
.Include(c => c.NavPointsParent).Include(c => c.Iscinstances)
.OrderBy(c => c.ParentNpid).ThenBy(c => c.NodeOrder)
.Select(c => new NavPoint
{
...
})
.ToListAsync();
if (navpoint == null)
{
return NotFound();
}
return Ok(navpoint);
}

Answer 1

基本上你拥有的是：

var navpoint = await _context.NavPoints
.Include(c => c.NavPointsParent)
.Include(c => c.Iscinstances)

Include始终应用于查询的根，因此包含_context.NavPoints的Iscinstances。这将产生相同的效果：

var navpoint = await _context.NavPoints
.Include(c => c.Iscinstances)
.Include(c => c.NavPointsParent)

要IncludeNavPointsParent的Iscinstances，请使用：

var navpoint = await _context.NavPoints
.Include(c => c.NavPointsParent)
.ThenInclude(c => c.Iscinstances)

CCD_ 7扩展先前的CCD_。

在您的评论之后：这里的要点是，即使不使用Include，您也需要导航属性c.NavPointsParent.Iscinstance。