如何在Entity Framework Core中为几个多对一相关实体建立几个左联接查询 [英] How to build several left join query in Entity Framework Core for several many to one related entities

问题描述

定义的模型和类

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; }
    }

数据库填充如下:

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

问题是，如何使用EF内核在一次查询中使用nodeLinks和rootLinks查询节点?

Thr question is how can I query nodes with it nodeLinks and rootLinks in one query using EF core?

在普通sql中，它看起来像这样:

In plain sql it will looks like this:

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我最终得到以下查询变体:

Using ef i ended up with following variants of query:

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

两者都会生成几个查询.

Both generates several queries.

推荐答案

此答案基于事实，即

如果索引正确，则大多数情况下EXISTS会执行 与JOIN相同.异常非常复杂 子查询，通常使用EXISTS更快.

If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

如果未索引您的JOIN键，则使用EXISTS可能会更快，但是 您将需要针对您的具体情况进行测试.

If your JOIN key is not indexed, it may be quicker to use EXISTS but you will need to test for your specific circumstance.

所以，我假设，带有EXISTS的子句将是JOIN的可接受替代.

So, I'm assuming that the clause with EXISTS would be an acceptable replacement of the JOIN.

我已经在WithOne中使用 lambda 定义了模型，读取为

I've defined the model with a lambda in the WithOne, reading this unit test for customer vs orders.

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(我还添加了一些不相关的名称字段)

The SQL (I've also added some irrelevant name fields)

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"

这篇关于如何在Entity Framework Core中为几个多对一相关实体建立几个左联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！