CODEWITHSUNDEEP
c#asp.netlinqdatatable
Neha Gupta
Neha Gupta

Reputation: 45

Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type. on using Linq Query

I have two data tables named as dt1Cloned and dt2Cloned. I have implemented linq query for joining these two tables like :

var res = (from p in dt1Cloned.AsEnumerable()
               join t in dt2Cloned.AsEnumerable()
            on p.Field<Int64>("WorkId") equals t.Field<Int64>("WorkId")
             select new
               {
                   DivisionId = p.Field<Int64>("DivisionId"),
                   DistrictId = p.Field<Int64>("DistrictId"),
                   MCId = p.Field<Int64>("MCId"),
                   ACId = p.Field<Int64>("ACId"),
                   PCId = p.Field<Int64>("PCId"),
                   DivisionName = p.Field<string>("DivisionName"),
                   DistrictName = p.Field<string>("DistrictName"),
                   MCName = p.Field<string>("MCName"),
                   AssemblyCName = p.Field<string>("AssemblyCName"),
                   ParliamentaryCName = p.Field<string>("ParliamentaryCName"),
                   WorkId = p.Field<Int64>("WorkId"),
                   WorkDetail = p.Field<string>("WorkDetail"),
                   WorkName = p.Field<string>("WorkName"),
                   RoadCode = p.Field<string>("RoadCode"),
                   AdApp = p.Field<string>("AdApp"),
                   //!Convert.IsDBNull(dr.t1.Field<DateTime?>("Step Start Time")) ? dr.t1.Field<DateTime?>("Step Start Time") : DBNull.Value 
                   //AdminAppDate = !Convert.IsDBNull(p.Field<DateTime>("AdminAppDate")) ? p.Field<DateTime>("AdminAppDate") : DBNull.Value,
                   AdminAppDate = p.Field<DateTime>("AdminAppDate"),
                   TargetDate = p.Field<DateTime>("TargetDate"),
                   AppAmt = p.Field<Int64>("AppAmt"),
                   TApp = p.Field<string>("TApp"),
                   TechAppDate = p.Field<DateTime>("TechAppDate"),
                   TAppAmt = p.Field<Int64>("TAppAmt"),
                   SourceOfFunds = p.Field<string>("SourceOfFunds"),
                   AllocatedAmt = p.Field<Int64>("AllocatedAmt"),
                   ReleaseAmt = p.Field<Int64>("ReleaseAmt"),
                   WStatus = p.Field<string>("WStatus "),
                   Expenditure = p.Field<string>("Expenditure"),
                   ExpenditurePer = p.Field<Int64>("ExpenditurePer"),
                   ProgressPercentage = p.Field<Int64>("ProgressPercentage"),
                   Progress = p.Field<string>("Progress"),
                   UserTypeId = p.Field<Int64>("UserTypeId"),
                   UserWorkType = p.Field<string>("UserWorkType"),
                   SchemeId = p.Field<Int64>("SchemeId"),
                   SchemeName = p.Field<string>("SchemeName"),
                   AgencyId = p.Field<Int64>("AgencyId"),
                   AgencyName = p.Field<string>("AgencyName"),
                   TADate = p.Field<DateTime>("TADate"),
                   AADate = p.Field<DateTime>("AADate"),
                   StatusDate = p.Field<DateTime>("StatusDate"),
                   ProgressDate = p.Field<DateTime>("ProgressDate"),
                   ReleaseDate = p.Field<DateTime>("ReleaseDate"),
                   AllocationDate = p.Field<DateTime>("AllocationDate"),
                   ExpDate = p.Field<DateTime>("ExpDate"),
                   TimeLimit = p.Field<Int64>("TimeLimit"),
                   TimeSpent = p.Field<Int64>("TimeSpent"),
                   TimeSpentPer = p.Field<double>("TimeSpentPer"),
                   TimeDiff = p.Field<double>("TimeDiff"),
                   EarthWorkP = p.Field<double>("EarthWorkP"),
                   EarthWorkC = p.Field<double>("EarthWorkC"),
                   SolingP = p.Field<double>("SolingP"),
                   SolingC = p.Field<double>("SolingC"),
                   WearingP = p.Field<double>("WearingP"),
                   WearingC = p.Field<double>("WearingC"),
                   RaisingP = p.Field<double>("RaisingP"),
                   RaisingC = p.Field<double>("RaisingC"),
                   StrengtheningP = p.Field<double>("StrengtheningP"),
                   StrengtheningC = p.Field<double>("StrengtheningC"),
                   PCLaidP = p.Field<double>("PCLaidP"),
                   PCLaidC = p.Field<double>("PCLaidC"),
                   ModifyDate = p.Field<DateTime>("ModifyDate"),
                   VillageName = t.Field<string>("VillageName"),
               }).ToList();

On implimenting this above query, I am getting exception "Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type." . How can I resolve this and handle null values for all datatypes?

Upvotes: 1

Views: 5773

Answers (1)

RB.
RB.

Reputation: 37172

If your database field can be nullable, then you will also need to make the C# property that you are reading it into nullable.

Declare your property as a Nullable<DateTime>. There is a convenient shorthand for this: DateTime?.

public class Person
{
    // DateOfBirth is NOT nullable - everyone has a date of birth...
    public DateTime DateOfBirth { get; set; }

    // DateOfDeath IS nullable - not everyone is dead yet...
    public DateTime? DateOfDeath { get; set; }
}

Then, when you are projecting out your new object, you can use:

DateOfDeath = p.Field<DateTime?>("DateOfDeath"),

Upvotes: 2

Related Questions