How to build several left join query in Entity Framework Core for several many to one related entities

Question

Defined model and classes

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 populated as followed:

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

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

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]

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.

Answer 1

This answer is based on the fact that

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.

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

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

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

My query

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

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"