Full External Join - Linq To SQL

Question

I am trying to use Linq to SQL for a complete outer join. This worked for me for a basic example, but it did not work when each side of the full outer join was formed from another inner join. Below code. I understand that this could probably be due to smaller requests, but I would prefer to separate them to make them as readable as possible. Performance is not a problem in this case.

var productIds = db.OrderItemsIncoming.Select(i => i.ProductID)
    .Union(db.OrderItemsOutgoing.Select(o => o.ProductID))
    .Distinct();

var ordersIn =  from o in db.OrdersIncoming
                join i in db.OrderItemsIncoming on o.OrderNumber equals i.OrderNumber
                select new { o, i };

var ordersOut = from o in db.OrdersOutgoing
                join i in db.OrderItemsOutgoing on o.OrderNumber equals i.OrderNumber
                select new { o, i };

var fullOuterJoinResults =  from i in ordersIn
                            join o in ordersOut on i.i.ProductID equals o.i.ProductID into t
                            from o in t.DefaultIfEmpty()
                            where i == null ^ o == null
                            select new { i, o };

In my test, the results of ordersIn are empty, and the results of ordersOut have one row in it. So I want the final fullOuterJoinResults to have a string, but it's empty.

Answer 1

, , productID !!!: -/ , ...

[]

, , :

var ordersIn =  from o in db.OrdersIncoming
                join i in db.OrderItemsIncoming on o.OrderNumber equals i.OrderNumber
                select new { o, i };

var ordersOut = from o in db.OrdersOutgoing
                join i in db.OrderItemsOutgoing on o.OrderNumber equals i.OrderNumber
                select new { o, i };

var productIds = db.OrderItemsIncoming.Select(i => i.ProductID)
    .Union(db.OrderItemsOutgoing.Select(o => o.ProductID))
    .Distinct();

var fullOuterJoinResults =    from pid in productIDs
                        join i in ordersIn on pid equals i.i.ProductID into t1
                        from i in t1.DefaultIfEmpty()
                        join o in ordersOut on pid equals o.i.ProductID into t2
                        from o in t2.DefaultIfEmpty()
                        where i == null ^ o == null
                        select new { i, o };