Entity Framework Error - The cast to value type 'System.Int64' failed because the materialized value is null

Question

The below method fails when Execting ToListAsync() with the following error

The cast to value type 'System.Int64' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. 

--

 private IQueryable<MaterialSearchResult> MaterialSearchQuery(string searchTerm)
            {    
                return from m in this.context.GB_Material
                       from v in this.context.WF_VideoVersion
                           .Where(
                               v =>
                                   v.MaterialID == m.MaterialID)
                           .DefaultIfEmpty()
                       select new MaterialSearchResult
                       {
                           MaterialId = v.MaterialID,
                           MaterialName = v.GB_Material.MaterialName,
                           MaterialTitle = v.GB_Material.MaterialTitle,
                           CreatedDate = v.GB_Material.CreatedDate,
                           NearestTxDate = v.NearestTXDate,
                           Channel = v.ScheduleName
                       };
    }

Calling method

public Task<List<MaterialSearchResult>> SearchMaterials(string searchTerm, string sort, string direction, int pageSize = 20, int skip = 0)
    {
        var data = MaterialSearchQuery(searchTerm)
            .SortMaterials(sort,direction)
            .Skip(skip)
            .Take(pageSize);

        return data.ToListAsync();
    }

public class MaterialSearchResult
{
    public long MaterialId { get; set; }
    public string MaterialName { get; set; }
    public string MaterialTitle { get; set; }
    public DateTime? NearestTxDate { get; set; }
    public string Channel { get; set; }
    public DateTime CreatedDate { get; set; }
}

 public class GB_Material()
    {
       public long MaterialID { get; set; }
    public string MaterialName { get; set; }
    public string MaterialTitle { get; set; }
    public System.DateTime CreatedDate { get; set; }

   public virtual ICollection<WF_VideoVersion> WF_VideoVersion { get; set; }
    }

     public class WF_VideoVersion()
    {
         public long VideoVersionID { get; set; }
         public long MaterialID { get; set; }
         public Nullable<System.DateTime> NearestTXDate { get; set; }
         public string ScheduleName { get; set; }

         public virtual GB_Material GB_Material { get; set; }

    }

EDIT:

I'm also getting the same error on the NearestTxDate property

The cast to value type 'System.DateTime' failed because the materialized value is null. 

Answer 1

For some insane reason i had written the select statement like this:

                  select new MaterialSearchResult
                   {
                       MaterialId = v.MaterialID,
                       MaterialName = v.GB_Material.MaterialName,
                       MaterialTitle = v.GB_Material.MaterialTitle,
                       CreatedDate = v.GB_Material.CreatedDate,
                       NearestTxDate = v.NearestTXDate,
                       Channel = v.ScheduleName
                   }

WHen it should have been this

 select new MaterialSearchResult
                       {
                           MaterialId = mMaterialID,
                           MaterialName = m.MaterialName,
                           MaterialTitle = m.MaterialTitle,
                           CreatedDate = m.CreatedDate,
                           NearestTxDate = v.NearestTXDate,
                           Channel = v.ScheduleName
                       }

Given that for some results the child would be null, then obviously going through the child to get the parents properties is not going to work.

I'm goin to blame it on a pre-lunch brain fart.

Answer 2

Try to rewrite your query:

return from m in this.context.GB_Material
                       from v in this.context.WF_VideoVersion
                           .Where(
                               v =>
                                   v.MaterialID == m.MaterialID)
                           .DefaultIfEmpty()
                       select new MaterialSearchResult
                       {
                           MaterialId = m.MaterialID,
                           MaterialName = m.MaterialName,
                           MaterialTitle = m.MaterialTitle,
                           CreatedDate = m.CreatedDate,
                           NearestTxDate = v.NearestTXDate,
                           Channel = v.ScheduleName
                       };