如何在实体框架核心中为多个多对一相关实体构建多个左连接查询

本文关键字：实体 构建 查询 连接 多对一 核心 框架 | 更新日期: 2023-09-27 18:01:49

已定义的模型和类

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<RootLink>()
                .HasOne(link => link.Node)
                .WithMany(node => node.RootLinks)
                .HasForeignKey(link => link.NodeId);
            modelBuilder.Entity<RootLink>()
                .HasOne(link => link.RootNode)
                .WithMany()
                .HasForeignKey(rootLink => rootLink.RootNodeId)
                .OnDelete(DeleteBehavior.Restrict);
            modelBuilder.Entity<NodeLink>()
                .HasOne(link => link.Node)
                .WithMany(node => node.NodeLinks)
                .HasForeignKey(link => link.NodeId);
            modelBuilder.Entity<NodeLink>()
                .HasOne(l => l.LinkedNode)
                .WithMany()
                .HasForeignKey(l => l.LinkedNodeId)
                .OnDelete(DeleteBehavior.Restrict);
        }

…

 public class Node
    {
        public long Id { get; set; }
        public ICollection<RootLink> RootLinks { get; set; }
        public ICollection<NodeLink> NodeLinks { get; set; }
        public int Value { get; set; }
    }
    public class NodeLink
    {
        public long Id { get; set; }
        public long NodeId { get; set; }
        public Node Node { get; set; }
        public long LinkedNodeId { get; set; }
        public Node LinkedNode { get; set; }
    }
    public class RootLink
    {
        public long Id { get; set; }
        public long NodeId { get; set; }
        public Node Node { get; set; }
        public long RootNodeId { get; set; }
        public Node RootNode { get; set; }
    }

DB的填充方式如下:

var node1 = new Node();
            var node2 = new Node();
            var node3 = new Node();
            node1.NodeLinks = new List<NodeLink>()
            {
                new NodeLink
                {
                    Node = node1,
                    LinkedNode = node2
                },
                new NodeLink
                {
                    Node = node3,
                    LinkedNode = node3
                }
            };
            node1.RootLinks = new List<RootLink>
            {
                new RootLink {RootNode = node1},
                new RootLink {RootNode = node3}
            };
            ctx.Nodes.AddRange(node1, node2, node3);

他们的问题是我如何用它查询节点nodellinks和rootLinks在一个查询使用EF核心?

在普通sql中，它看起来像这样: 
SELECT [node].[Id], [node].[Value], [rootLink].[Id], [rootLink].[NodeId], [rootLink].[RootNodeId]
FROM [Nodes] AS [node]
LEFT JOIN [RootLinks] AS [rootLink] ON [node].[Id] = [rootLink].[NodeId]
LEFT JOIN [NodeLinks] AS [nodeLink] ON [node].[Id] = [rootLink].[NodeId]
WHERE [node].[Id] in (NODE_ID_LIST)
ORDER BY [node].[Id]

使用ef i最终得到以下查询变量:

    public static IEnumerable<Node> FindVariant1(TestDbContext ctx, params long[] nodeIdList)
    {
        return ctx.Nodes
            .Include(node => node.NodeLinks)
            .Include(node => node.RootLinks)
            .Where(node => nodeIdList.Contains(node.Id)).ToList();
    }
    public static IEnumerable<Node> FindVariant2(TestDbContext ctx, params long[] nodeIdList)
    {
        return ctx.Nodes
            .GroupJoin(ctx.RootLinks, node => node.Id, rootLink => rootLink.NodeId,
                (node, rootLinks) => new {node, rootLinks})
            .SelectMany(info => info.rootLinks.DefaultIfEmpty(), (info, rootLink) => new {info.node, rootLink})
            .GroupJoin(ctx.NodeLinks, node => node.node.Id, nodeLink => nodeLink.NodeId,
                (info, nodeLinks) => new {info.node, info.rootLink, nodeLinks})
            .SelectMany(info => info.nodeLinks.DefaultIfEmpty(),
                (info, nodeLink) => new {info.node, info.rootLink, nodeLink})
            .Where(node => nodeIdList.Contains(node.node.Id)).ToList()
            .Select(r => r.node);
    }

都生成几个查询。

如何在实体框架核心中为多个多对一相关实体构建多个左连接查询

这个答案是基于

如果您有合适的索引，大多数情况下EXISTS都可以执行与JOIN相同。例外情况非常复杂子查询，通常使用EXISTS会更快。

如果JOIN键没有被索引，使用EXISTS可能会更快您需要针对您的具体情况进行测试。

所以，我假设带有EXISTS的条款将是可接受的替代JOIN。

我在WithOne中使用lambda定义了模型，读取客户与订单的单元测试。

modelBuilder.Entity<RootLink>().ToTable("RootLinks");
modelBuilder.Entity<NodeLink>().ToTable("NodeLinks");
modelBuilder.Entity<Node>().HasKey(r => r.NodeId);
modelBuilder.Entity<Node>()
    .HasMany(link => link.NodeLinks)
    .WithOne(
    l => l.Node
    ).HasForeignKey(l => l.NodeId);

modelBuilder.Entity<Node>()
    .HasMany(link => link.RootLinks)
    .WithOne(
    l => l.Node
    ).HasForeignKey(l => l.NodeId);
我查询

var test = ctx.Nodes
    .Where(n => new long[] { 1, 2 }.Contains( n.NodeId))
    .Include(c => c.NodeLinks)
    .Include(c => c.RootLinks);
 var myRes = test.ToList();

SQL(我还添加了一些无关的名称字段)

SELECT "n"."NodeId", "n"."Value"
FROM "Nodes" AS "n"
WHERE "n"."NodeId" IN (1, 2)
ORDER BY "n"."NodeId"Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "r"."RootLinkId", "r"."NodeId", "r"."RootName"
FROM "RootLinks" AS "r"
WHERE EXISTS (
    SELECT 1
    FROM "Nodes" AS "n"
    WHERE "n"."NodeId" IN (1, 2) AND ("r"."NodeId" = "n"."NodeId"))
ORDER BY "r"."NodeId"Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "n0"."NodeLinkId", "n0"."LinkName", "n0"."NodeId"
FROM "NodeLinks" AS "n0"
WHERE EXISTS (
    SELECT 1
    FROM "Nodes" AS "n"
    WHERE "n"."NodeId" IN (1, 2) AND ("n0"."NodeId" = "n"."NodeId"))
ORDER BY "n0"
相关文章：