Set operations need to consider nullability of columns on both sides

[LostAsk]

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


    }
}

[DUWENINK]

Mark

[smitpatel]

Full repro:

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

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

[LostAsk]

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

[roji]

@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:

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

[LostAsk]

OK，Thank you

[LostAsk]

@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<JoinResult<TLeft, TRight>> FullJoin<TLeft, TRight, TKey>(
this IQueryable leftItems,
IQueryable rightItems,
Expression<Func<TLeft, TKey>> leftKeySelector,
Expression<Func<TRight, TKey>> 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

[roji]

@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.

[LostAsk]

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

[roji]

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

[LostAsk]

@roji
I need your help！
See to :issues# 19253
#19253 (comment)

[roji]

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