DefaultIfEmpty issues with linq statement

Question

Can someone please explain to me what is going wrong here. I am using Linq to Sql to create a pretty nasty query.

The problem that I have is I need to left join on some of the tables (using DefaultIfEmpty) I need to return an specific object from the DefaultIfEmpty(new SomeObject(){...}) so that i can do a join on it a bit later again.

My problem is that the DefaultIfEmpty always returns null. So i checked with some dummy data as a POC and it works

Example

I can't seem to find the link that I used to get the sample code below, I just altered it a bit to add an extra join to OrderConfirmed So when running it, it works as expected.

List<Book> bookList = new List<Book>
{
  new Book{BookID=1, BookNm="DevCurry.com Developer Tips"},
  new Book{BookID=2, BookNm=".NET and COM for Newbies"},
  new Book{BookID=3, BookNm="51 jQuery ASP.NET Recipes"},
  new Book{BookID=4, BookNm="Motivational Gurus"},
  new Book{BookID=5, BookNm="Spiritual Gurus"}
};

List<Order> bookOrders = new List<Order>
{
    new Order{OrderID=1, BookID=1, PaymentMode="Cheque"},
    new Order{OrderID=2, BookID=5, PaymentMode="Credit"},
    new Order{OrderID=3, BookID=1, PaymentMode="Cash"},
    new Order{OrderID=4, BookID=3, PaymentMode="Cheque"},
    new Order{OrderID=5, BookID=5, PaymentMode="Cheque"},
    new Order{OrderID=6, BookID=4, PaymentMode="Cash"}
};

List<OrderConfirmed> orderConfirmed = new List<OrderConfirmed>();


var orderForBooks = from bl in bookList
                    join ordr in bookOrders on bl.BookID equals ordr.BookID into a
                        from ordr in a.DefaultIfEmpty(new Order {BookID = -1, OrderID = 12, PaymentMode = "Cash Test" }) // return default value
                    join confirmed in orderConfirmed on ordr.OrderID equals confirmed.OrderID into c
                        from confirmed in c.DefaultIfEmpty(new OrderConfirmed() { OrderID = 12, Description = "Is this working"}) // no values in orderconfirmed so returning new object
                    select new
                               {
                                   bl,
                                   ordr,
                                   confirmed
                    };

So knowing that what i am trying todo should work, in my actual code the DefaultIfEmpty is always null. Am i right in saying that this should work? Maybe something to note that i am using EFCORE

var formData = 
 await (from ccf in this.context.Set<BureauCountryCustomForm>()
          join ccfa in this.context.Set<EmployeeCustomFormAttributeHeader>() on ccf.BureauCountryFormId equals ccfa.BureauCountryFormId into ccfaJoin
          from ccfa in ccfaJoin.DefaultIfEmpty(new EmployeeCustomFormAttributeHeader()
                                                   {
                                                       HeaderId = -1,
                                                       BureauCountryFormId = 3,
                                                       CompanyFormId = -1,
                                                       EmployeeId = 1
                                                   })
          join cca in this.context.Set<BureauCountryCustomFormAttribute>() on ccf.BureauCountryFormId equals cca.BureauCountryFormId
          join edv in this.context.Set<EmployeeCustomFormValue>() on
              new { AttributeId = (int?)cca.BureauCountryFormAttributeId, HeaderId = (int?)ccfa.HeaderId } equals
              new { AttributeId = (int?)edv.FormAttributeId, HeaderId = (int?)edv.HeaderId } into edvJoin
          from edv in edvJoin.DefaultIfEmpty(new EmployeeCustomFormValue()
                                                 {
                                                     HeaderId = -1,
                                                     FormAttributeId = -1
                                                 })
          where ccf.CountryId == countryId && ccf.EmployeeLevel == true && ccfa.EmployeeId == employeeId
          select new { ccf, ccfa, cca, edv }).GroupBy(g => new { g.ccf.BureauCountryFormId, g.ccfa.HeaderId })
.Select(
_ => new CustomFormData
{
    Keys = new[] { _.Key.BureauCountryFormId, _.Key.HeaderId },
    FormName = _.First().ccf.FormName,
    EffectiveDate = _.First().ccfa.EffectiveDate,
    FormDataFields = _.Select(
                 f => new CustomFormDataField
                 {
                     AttributeId = f.cca.BureauCountryFormAttributeId,
                     FieldLabel = f.cca.FieldLabel,
                     IsMandatory = f.cca.IsMandatory,
                     FieldValue = f.edv.FieldValue,
                     ControlTypeId = (long)f.cca.ControlTypeId,
                     DropdownValues = f.cca.DropdownValues,
                     Order = f.cca.AttOrderNo,
                     ValidationRule = f.cca.ValidationRule,
                     ValidationExpression = f.cca.ValidationExpression
                 }).ToList()
}).ToListAsync();

Answer 1

The problem is in:

from ccfa in ccfaJoin.DefaultIfEmpty(new EmployeeCustomFormAttributeHeader())

Such a query construction should be used only with collections like db sets or queries not DefaultIfEmpty which however is IEnumerable but EF Core treats it as single value (which really in sql query result is null if there is no match) wherever it is used.

You should change it to:

let ccfa = ccfaJoin.DefaultIfEmpty(new EmployeeCustomFormAttributeHeader())

And it should work as expected.