Efcore: Set operations need to consider nullability of columns on both sides

Created on 30 Sep 2019  ·  11Comments  ·  Source: dotnet/efcore

I wrote an Iqueryable assistant, which can correctly generate SQL statements in EF6. The statements generated in EF Core2.2 full join are separate but correct. In EF Core 3 full join, the generated SQL statements are ok, but only report errors.

EF Core3
Message:Data is Null. This method or property cannot be called on Null values.
Stack:
at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
at Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
at Microsoft.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace AbpEfCore
{
    public class JoinResult<TLeft, TRight>
    {
        public TLeft Left { get; set; }

        public TRight Right { get; set; }
    }

    public static class QueryLinqHelper
    {
        public static IQueryable<TResult> InnerJoin<TOuter, TInner, TKey, TResult>(
            this IQueryable<TOuter> outer,
            IQueryable<TInner> inner,
            Expression<Func<TOuter, TKey>> outerKeySelector,
            Expression<Func<TInner, TKey>> innerKeySelector,
            Expression<Func<TOuter, TInner, TResult>> resultSelector)

        {
            return outer.Join(inner, outerKeySelector, innerKeySelector, resultSelector);
        }

        public static IQueryable<JoinResult<TOuter, TInner>> InnerJoin<TOuter, TInner, TKey>(
            this IQueryable<TOuter> outer,
            IQueryable<TInner> inner,
            Expression<Func<TOuter, TKey>> outerKeySelector,
            Expression<Func<TInner, TKey>> innerKeySelector
            )
        {
            return outer.Join(inner, outerKeySelector, innerKeySelector, (x, y) => new JoinResult<TOuter, TInner> { Left = x, Right = y });
        }


        public static IQueryable<JoinResult<TLeft, TRight>> LeftJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )

        {
            return leftItems.GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg })
                .SelectMany(r => r.rightg.DefaultIfEmpty()
                , (x, y) => new JoinResult<TLeft, TRight> { Left = x.left, Right = y });
        }

        public static IQueryable<JoinResult<TLeft, TRight>> LeftExcludingJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )

        {
            //EF6: SELECT
            //         [Extent1].[Id] AS[Id], 
            //        [Extent1].[forkey] AS[forkey], 
            //        [Extent1].[a] AS[a], 
            //        [Extent1].[a1] AS[a1], 
            //        [Extent2].[Id] AS[Id1], 
            //        [Extent2].[forkey] AS[forkey1], 
            //        [Extent2].[b] AS[b], 
            //        [Extent2].[b1]
            //            AS[b1]
            //    FROM[dbo].[A]
            //            AS[Extent1]
            //    LEFT OUTER JOIN[dbo].[B] AS[Extent2] ON([Extent1].[forkey] = [Extent2].[forkey]) OR(([Extent1].[forkey] IS NULL) AND([Extent2].[forkey] IS NULL))
            //        WHERE[Extent2].[Id]
            //            IS NULL

            //EF CORE 2.2 AND 3.0:
            //SELECT[x].[Id], [x].[a], [x].[a1], [x].[forkey], [y].[Id], [y].[b], [y].[b1], [y].[forkey]
            //        FROM[A] AS[x]
            //LEFT JOIN[B] AS[y] ON[x].[forkey] = [y].[forkey]
            //        WHERE[y].[Id]
            //        IS NULL



            return leftItems.GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg })
                .SelectMany(r => r.rightg.DefaultIfEmpty()
                , (x, y) => new JoinResult<TLeft, TRight> { Left = x.left, Right = y }).Where(z => z.Right.Equals(null));
        }
        public static IQueryable<JoinResult<TLeft, TRight>> RightJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
            )
        {
            return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right })
                             .SelectMany(l => l.leftg.DefaultIfEmpty(),
                             (x, y) => new JoinResult<TLeft, TRight> { Left = y, Right = x.right });
        }


        public static IQueryable<JoinResult<TLeft, TRight>> RightExcludingJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
            )
        {
            //EF6: SELECT
            //         [Extent1].[Id] AS[Id], 
            //        [Extent2].[Id] AS[Id1], 
            //        [Extent2].[forkey] AS[forkey], 
            //        [Extent2].[a] AS[a], 
            //        [Extent2].[a1] AS[a1], 
            //        [Extent1].[forkey] AS[forkey1], 
            //        [Extent1].[b] AS[b], 
            //        [Extent1].[b1]
            //    AS[b1]
            //    FROM[dbo].[B]
            //    AS[Extent1]
            //    LEFT OUTER JOIN[dbo].[A] AS[Extent2] ON([Extent1].[forkey] = [Extent2].[forkey]) OR(([Extent1].[forkey] IS NULL) AND([Extent2].[forkey] IS NULL))
            //        WHERE[Extent2].[Id]
            //    IS NULL

            //EF CORE 2.2 AND 3.0:
            //SELECT[y].[Id], [y].[b], [y].[b1], [y].[forkey], [x].[Id], [x].[a], [x].[a1], [x].[forkey]
            //        FROM[B] AS[y]
            //LEFT JOIN[A] AS[x] ON[y].[forkey] = [x].[forkey]
            //        WHERE[x].[Id]
            //        IS NULL


            return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right })
                             .SelectMany(l => l.leftg.DefaultIfEmpty(),
                             (x, y) => new JoinResult<TLeft, TRight> { Left = y, Right = x.right })
                             .Where(z => z.Left.Equals(null))
                             ;
        }


        public static IQueryable<JoinResult<TLeft, TRight>> FullJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )
        {
            //EF6: SELECT
            //         [UnionAll1].[Id] AS[C1], 
            //        [UnionAll1].[Id1] AS[C2], 
            //        [UnionAll1].[forkey] AS[C3], 
            //        [UnionAll1].[a] AS[C4], 
            //        [UnionAll1].[a1] AS[C5], 
            //        [UnionAll1].[Id2] AS[C6], 
            //        [UnionAll1].[forkey1] AS[C7], 
            //        [UnionAll1].[b] AS[C8], 
            //        [UnionAll1].[b1]
            //    AS[C9]
            //    FROM(SELECT
            //    [Extent1].[Id] AS[Id],
            //    [Extent1].[Id] AS[Id1],
            //    [Extent1].[forkey] AS[forkey],
            //    [Extent1].[a] AS[a],
            //    [Extent1].[a1] AS[a1],
            //    [Extent2].[Id] AS[Id2],
            //    [Extent2].[forkey] AS[forkey1],
            //    [Extent2].[b] AS[b],
            //    [Extent2].[b1] AS [b1]
            //    FROM  [dbo].[A] AS [Extent1]
            //    LEFT OUTER JOIN [dbo].[B] AS[Extent2] ON ([Extent1].[forkey] = [Extent2].[forkey]) OR(([Extent1].[forkey] IS NULL) AND([Extent2].[forkey] IS NULL))
            //        UNION ALL
            //            SELECT
            //            [Extent3].[Id] AS[Id], 
            //            [Extent4].[Id] AS[Id1], 
            //            [Extent4].[forkey] AS[forkey], 
            //            [Extent4].[a] AS[a], 
            //            [Extent4].[a1] AS[a1], 
            //            [Extent3].[Id] AS[Id2], 
            //            [Extent3].[forkey] AS[forkey1], 
            //            [Extent3].[b] AS[b], 
            //            [Extent3].[b1]
            //    AS[b1]
            //    FROM[dbo].[B]
            //    AS[Extent3]
            //    LEFT OUTER JOIN[dbo].[A] AS[Extent4] ON([Extent3].[forkey] = [Extent4].[forkey]) OR(([Extent3].[forkey] IS NULL) AND([Extent4].[forkey] IS NULL))
            //            WHERE[Extent4].[Id] IS NULL) AS[UnionAll1]

            //EF CORE 2.2 :
            //SELECT[x1].[Id], [x1].[a], [x1].[a1], [x1].[forkey], [y1].[Id], [y1].[b], [y1].[b1], [y1].[forkey]
            //        FROM[A] AS[x1]
            //LEFT JOIN[B] AS[y1] ON[x1].[forkey] = [y1].[forkey]


            //AND

            //        SELECT[y2].[Id], [y2].[b], [y2].[b1], [y2].[forkey], [x2].[Id], [x2].[a], [x2].[a1], [x2].[forkey]
            //        FROM[B] AS[y2]
            //LEFT JOIN[A] AS[x2] ON[y2].[forkey] = [x2].[forkey]
            //        WHERE[x2].[Id]
            //        IS NULL

            //EF CORE 3.0:
            //SELECT[a].[Id], [a].[a], [a].[a1], [a].[forkey], [b].[Id] AS[Id0], [b].[b], [b].[b1], [b].[forkey]
            //        AS[forkey0]
            //FROM[A] AS[a]
            //LEFT JOIN[B] AS[b] ON[a].[forkey] = [b].[forkey]
            //        UNION ALL
            //SELECT[a0].[Id], [a0].[a], [a0].[a1], [a0].[forkey], [b0].[Id] AS[Id0], [b0].[b], [b0].[b1], [b0].[forkey]
            //        AS[forkey0]
            //FROM[B] AS[b0]
            //LEFT JOIN[A] AS[a0] ON[b0].[forkey] = [a0].[forkey]
            //        WHERE[a0].[Id]
            //        IS NULL


            return leftItems.LeftJoin(rightItems, leftKeySelector, rightKeySelector).Concat(leftItems.RightExcludingJoin(rightItems, leftKeySelector, rightKeySelector));
        }


    }
}
closed-fixed customer-reported type-bug
Source
picture LostAsk
👍1

Most helpful comment

Full repro:
```C#
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var db = new MyContext())
{
// Recreate database
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

            // Seed database
            db.Add(new Blog
            {
                Post = new Post()
            });
            db.Add(new Blog());
            db.Add(new Post());


            db.SaveChanges();
        }

        using (var db = new MyContext())
        {
            // Run queries
            var query = db.Blogs
                .GroupJoin(db.Posts, b => b.Id, p => p.BlogId, (b, ps) => new { b, ps })
                .SelectMany(g => g.ps.DefaultIfEmpty(), (g, p) => new { g.b, p })
                .Concat(db.Posts.GroupJoin(db.Blogs, p => p.BlogId, b => b.Id, (p, bs) => new { p, bs })
                    .SelectMany(g => g.bs.DefaultIfEmpty(), (g, b) => new { b, g.p })
                    .Where(e => e.b.Equals(null)))
                .Select(e => e.b.Id)
                .ToList();
        }
        Console.WriteLine("Program finished.");
    }
}


public class MyContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b =>
        {
            b
            .AddConsole()
            .AddFilter("", LogLevel.Debug);
        });

    // Declare DBSets
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Select 1 provider
        optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
            //.UseSqlite("filename=_modelApp.db")
            //.UseInMemoryDatabase(databaseName: "_modelApp")
            //.UseCosmos("https://localhost:8081", @"C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "_ModelApp")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure model
    }
}

public class Blog
{
    public int Id { get; set; }
    public Post Post { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public int? BlogId { get; set; }
    public Blog Blog { get; set; }
}

}


Generated SQL
```SQL
SELECT [t].[Id]
      FROM (
          SELECT [b].[Id], [p].[Id] AS [Id0], [p].[BlogId]
          FROM [Blogs] AS [b]
          LEFT JOIN [Posts] AS [p] ON [b].[Id] = [p].[BlogId]
          UNION ALL
          SELECT [b0].[Id], [p0].[Id] AS [Id0], [p0].[BlogId]
          FROM [Posts] AS [p0]
          LEFT JOIN [Blogs] AS [b0] ON [p0].[BlogId] = [b0].[Id]
          WHERE [b0].[Id] IS NULL
      ) AS [t]

Here in Union All, [b].[Id] from left is non-null (PK & main table). But it is nullable on right side as it is coming from left join. When generating set operation, we inferred nullability only based on left.

cc: @roji

picture smitpatel on 30 Sep 2019
👍2

All 11 comments

Mark

DUWENINK picture DUWENINK on 30 Sep 2019

Full repro:
```C#
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var db = new MyContext())
{
// Recreate database
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

            // Seed database
            db.Add(new Blog
            {
                Post = new Post()
            });
            db.Add(new Blog());
            db.Add(new Post());


            db.SaveChanges();
        }

        using (var db = new MyContext())
        {
            // Run queries
            var query = db.Blogs
                .GroupJoin(db.Posts, b => b.Id, p => p.BlogId, (b, ps) => new { b, ps })
                .SelectMany(g => g.ps.DefaultIfEmpty(), (g, p) => new { g.b, p })
                .Concat(db.Posts.GroupJoin(db.Blogs, p => p.BlogId, b => b.Id, (p, bs) => new { p, bs })
                    .SelectMany(g => g.bs.DefaultIfEmpty(), (g, b) => new { b, g.p })
                    .Where(e => e.b.Equals(null)))
                .Select(e => e.b.Id)
                .ToList();
        }
        Console.WriteLine("Program finished.");
    }
}


public class MyContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b =>
        {
            b
            .AddConsole()
            .AddFilter("", LogLevel.Debug);
        });

    // Declare DBSets
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Select 1 provider
        optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
            //.UseSqlite("filename=_modelApp.db")
            //.UseInMemoryDatabase(databaseName: "_modelApp")
            //.UseCosmos("https://localhost:8081", @"C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "_ModelApp")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure model
    }
}

public class Blog
{
    public int Id { get; set; }
    public Post Post { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public int? BlogId { get; set; }
    public Blog Blog { get; set; }
}

}


Generated SQL
```SQL
SELECT [t].[Id]
      FROM (
          SELECT [b].[Id], [p].[Id] AS [Id0], [p].[BlogId]
          FROM [Blogs] AS [b]
          LEFT JOIN [Posts] AS [p] ON [b].[Id] = [p].[BlogId]
          UNION ALL
          SELECT [b0].[Id], [p0].[Id] AS [Id0], [p0].[BlogId]
          FROM [Posts] AS [p0]
          LEFT JOIN [Blogs] AS [b0] ON [p0].[BlogId] = [b0].[Id]
          WHERE [b0].[Id] IS NULL
      ) AS [t]

Here in Union All, [b].[Id] from left is non-null (PK & main table). But it is nullable on right side as it is coming from left join. When generating set operation, we inferred nullability only based on left.

cc: @roji

smitpatel picture smitpatel on 30 Sep 2019
👍2

how should i solve it in ef core 3?
class A {public int Id {get;set;} public string forkey {get;set;}}

class B {public int Id {get;set;} public string forkey {get;set;}}

LostAsk picture LostAsk on 1 Oct 2019

@LostAsk we'll work on fixing this for 3.1. The best way around this is in 3.0 is to perform the set operation via client evaluation getting the results of each query independently and then performing the set operation in-memory:

c# var x = context.Blogs.Where(....) .AsEnumerable(). Concat(context.Posts.Where(...) .AsEnumerable());

roji picture roji on 1 Oct 2019
👍1

OK，Thank you

LostAsk picture LostAsk on 1 Oct 2019

@roji 

 using (var context = new EntityContext())
 {
var full = context.A.FullJoin(context.B, x => x.forkey, y => y.forkey).ToList();
}

FullJoin Method is public static IQueryable> FullJoin(
this IQueryable leftItems,
IQueryable rightItems,
Expression> leftKeySelector,
Expression> rightKeySelector
)
Error：
System.InvalidOperationException:“Unable to track an entity of type 'A' because primary key property 'Id' is null.”

SQL Result：
Id a a1 forkey Id b b1 forkey
1 a0 a1 a 1 b0 b1 a
2 a1 a1 b 2 b1 b1 b
NULL NULL NULL NULL 3 b2 b1 c
4 a3 a1 d NULL NULL NULL NULL

LostAsk picture LostAsk on 9 Dec 2019

@LostAsk I don't see any operation in your code (Union, Concat, Intersection or Except), and the full definition of FullJoin seems to be missing. Please open a new issue with a full runnable code sample, a full stack trace, etc.

roji picture roji on 9 Dec 2019

@roji 

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
namespace AbpEfCore
{
    public class JoinResult<TLeft, TRight>
    {
        public TLeft Left { get; set; }

        public TRight Right { get; set; }
    }

    public static class QueryLinqHelper
    {
        public static IQueryable<TResult> InnerJoin<TOuter, TInner, TKey, TResult>(
            this IQueryable<TOuter> outer,
            IQueryable<TInner> inner,
            Expression<Func<TOuter, TKey>> outerKeySelector,
            Expression<Func<TInner, TKey>> innerKeySelector,
            Expression<Func<TOuter, TInner, TResult>> resultSelector)

        {
            return outer.Join(inner, outerKeySelector, innerKeySelector, resultSelector);
        }

        public static IQueryable<JoinResult<TOuter, TInner>> InnerJoin<TOuter, TInner, TKey>(
            this IQueryable<TOuter> outer,
            IQueryable<TInner> inner,
            Expression<Func<TOuter, TKey>> outerKeySelector,
            Expression<Func<TInner, TKey>> innerKeySelector
            )
        {
            return outer.Join(inner, outerKeySelector, innerKeySelector, (x, y) => new JoinResult<TOuter, TInner> { Left = x, Right = y });
        }


        public static IQueryable<JoinResult<TLeft, TRight>> LeftJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )

        {
            return leftItems.GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg })
                .SelectMany(r => r.rightg.DefaultIfEmpty()
                , (x, y) => new JoinResult<TLeft, TRight> { Left = x.left, Right = y });
        }

        public static IQueryable<JoinResult<TLeft, TRight>> LeftExcludingJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )

        {

            return leftItems.GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg })
                .SelectMany(r => r.rightg.DefaultIfEmpty()
                , (x, y) => new JoinResult<TLeft, TRight> { Left = x.left, Right = y }).Where(z => z.Right.Equals(null));
        }
        public static IQueryable<JoinResult<TLeft, TRight>> RightJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
            )
        {
            return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right })
                             .SelectMany(l => l.leftg.DefaultIfEmpty(),
                             (x, y) => new JoinResult<TLeft, TRight> { Left = y, Right = x.right });
        }


        public static IQueryable<JoinResult<TLeft, TRight>> RightExcludingJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
            )
        {
            return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right })
                             .SelectMany(l => l.leftg.DefaultIfEmpty(),
                             (x, y) => new JoinResult<TLeft, TRight> { Left = y, Right = x.right })
                             .Where(z => z.Left.Equals(null))
                             ;
        }


        public static IQueryable<JoinResult<TLeft, TRight>> FullJoin<TLeft, TRight, TKey>(
            this IQueryable<TLeft> leftItems,
            IQueryable<TRight> rightItems,
            Expression<Func<TLeft, TKey>> leftKeySelector,
            Expression<Func<TRight, TKey>> rightKeySelector
           )
        {
            return leftItems.LeftJoin(rightItems, leftKeySelector, rightKeySelector).Concat(leftItems.RightExcludingJoin(rightItems, leftKeySelector, rightKeySelector));
        }


    }


    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();
                var tmp_a=new A[]{
               new A{Id=1,a="a0",a1="a1",forkey="a"},
               new A{Id=3,a="a2",a1="a1",forkey="d"},
                };
                var tmp_b=new B[]{
               new B{Id=1,b="b0",b1="b1",forkey="a"},
               new B{Id=3,b="b2",b1="b1",forkey="c"},
                };
               db.A.AddRange(tmp_a);
               db.B.AddRange(tmp_b);
                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var query = db.A.FullJoin(db.B,x=>x.forkey,y=>y.forkey).ToList();
            }
            Console.WriteLine("Program finished.");
        }
    }

public class MyContext : DbContext
    {
        private static ILoggerFactory ContextLoggerFactory
            => LoggerFactory.Create(b =>
            {
                b
                .AddConsole()
                .AddFilter("", LogLevel.Debug);
            });

        public DbSet<A> A{ get; set; }
        public DbSet<B> B{ get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(ContextLoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public class A
    {
        public int Id { get; set; }
        public string a { get; set; }
        public string a1  { get; set; }
        public string forkey    {get;set;}

    }

    public class B
    {
        public int Id { get; set; }
        public string b { get; set; }
        public string b1  { get; set; }
        public string forkey    {get;set;}
    }
}
LostAsk picture LostAsk on 9 Dec 2019

@LostAsk can you please open a new issue with the full details - version of EF Core used, full exception including stack trace, etc.

roji picture roji on 9 Dec 2019
👍1

@roji
I need your help！
See to :issues# 19253
https://github.com/aspnet/EntityFrameworkCore/issues/19253#issuecomment-564356191

LostAsk picture LostAsk on 12 Dec 2019

@LostAsk no need to ping me here, I'm aware of #19253.

roji picture roji on 12 Dec 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

leak picture leak  ·  3Comments
iberodev picture iberodev  ·  3Comments
divega picture divega  ·  3Comments
TPJ11 picture TPJ11  ·  3Comments
HappyNomad picture HappyNomad  ·  3Comments