LINQ join issue, returns all null value

Question

I am new in EF and LINQ and I am facing a strange issue. When I check null value in my select new block, all values from child table is coming null. Below is the LINQ query.

My Linq Code

var linqResult = from pd in entities.tblpackagedetails
         join ps in entities.tblpackageselecteds
         on pd.PackageDetailsID equals ps.PackageDetailsID 
         into tabJoin
         from tj in tabJoin.Where(ps => ps.UserID == userID 
         && ps.IsActive == true).DefaultIfEmpty()
         select new
         {
             IsComplete = (tj == null) ? false : tj.IsComplete,
             IsActive = (tj == null) ? false : tj.IsActive,
             UserID = (tj == null) ? 0 : tj.UserID,
             IsMandatory = pd.IsMandatory,
             PackageSelectedID = (tj == null) ? 0 : tj.PackageSelectedID,
             IsSelected = (tj == null ? false : tj.IsSelected),
             pd.Amount,
             pd.Code,
             pd.Description,
             pd.Points,
             pd.PackageDetailsID
         };

        foreach (var result in linqResult)
        {
            packagesSelected.Add(new PackageDetailDataModel()
            {
                Amount = result.Amount,
                Code = result.Code,
                Description = result.Description,
                IsComplete = result.IsComplete,
                IsMandatory = result.IsMandatory,
                PackageDetailsID = result.PackageDetailsID,
                PackageSelectedID = result.PackageSelectedID,
                Points = result.Points,
                IsActive = result.IsActive,
                UserID = result.UserID,
                IsSelected = result.IsSelected
            });
        }

SQL generated by Visualizer

    SELECT
    `Extent1`.`PackageDetailsID`, 
    `Extent2`.`IsComplete`, 
    `Extent2`.`IsActive`, 
    `Extent2`.`UserID`, 
    `Extent1`.`IsMandatory`, 
    `Extent2`.`PackageSelectedID`, 
    `Extent2`.`IsSelected`, 
    `Extent1`.`Amount`, 
    `Extent1`.`Code`, 
    `Extent1`.`Description`, 
    `Extent1`.`Points`
    FROM `tblpackagedetails` AS `Extent1` 
    LEFT OUTER JOIN `tblpackageselected` AS `Extent2` 
    ON (`Extent1`.`PackageDetailsID` = `Extent2`.`PackageDetailsID`) 
    AND ((`Extent2`.`UserID` = @linq_0) AND (1 = `Extent2`.`IsActive`))

When I ran above sql in MySQL workbench I got below output (repalcing @linq_0 with userID).

My Parent Table Structure

Child Table Structure

Output I want

But the values for IsComplete, IsActive, UserID, PackageSelectedID and IsSelected null as a result condition checking in select new block assign false or 0.

If I remove null checking, I get value for first 3 rows and in fourth iteration I get below exception. The cast to value type 'Boolean' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

Please help... :(

Working code block

        packagesSelected = new List<PackageDetailDataModel>();
    var linqResult = from pd in entities.tblpackagedetails
                     join ps in entities.tblpackageselecteds
                     on pd.PackageDetailsID equals ps.PackageDetailsID
                     into tabJoin
                     from tj in tabJoin.Where(ps => ps.UserID == userID
                     && ps.IsActive == true).DefaultIfEmpty()
                     select new
                     {
                         IsComplete = (bool?)tj.IsComplete,
                         IsActive = (bool?)tj.IsActive,
                         UserID = (int?)tj.UserID,
                         IsMandatory = pd.IsMandatory,
                         PackageSelectedID = (int?)tj.PackageSelectedID,
                         IsSelected = (bool?)tj.IsSelected,
                         pd.Amount,
                         pd.Code,
                         pd.Description,
                         pd.Points,
                         pd.PackageDetailsID
                     };

    foreach (var result in linqResult)
    {
        packagesSelected.Add(new PackageDetailDataModel()
        {
            Amount = result.Amount,
            Code = result.Code,
            Description = result.Description,
            IsComplete = (result.IsComplete ?? false),
            IsMandatory = result.IsMandatory,
            PackageDetailsID = result.PackageDetailsID,
            PackageSelectedID = (result.PackageSelectedID ?? 0),
            Points = result.Points,
            IsActive = (result.IsActive ?? false),
            UserID = (result.UserID ?? 0),
            IsSelected = (result.IsSelected ?? false)
        });
    }

Thanks to 2Kay :)

Answer 1

When tj is null, EF consieders all properties of tj as null. It's ok, but when EF trying to materialize them into value-types it fails. So the solution is to use nullable types..

Try this query:

var linqResult = from pd in entities.tblpackagedetails
     join ps in entities.tblpackageselecteds
     on pd.PackageDetailsID equals ps.PackageDetailsID 
     into tabJoin
     from tj in tabJoin.Where(ps => ps.UserID == userID 
     && ps.IsActive == true).DefaultIfEmpty()
     select new
     {
         IsComplete = (bool?) tj.IsComplete,
         IsActive = (bool?) tj.IsActive,
         UserID = (int?) tj.UserID,
         IsMandatory = pd.IsMandatory,
         PackageSelectedID = (int?) tj.PackageSelectedID,
         IsSelected = (bool?) tj.IsSelected,
         pd.Amount,
         pd.Code,
         pd.Description,
         pd.Points,
         pd.PackageDetailsID
     };