asked on

How to handle NULL value in LINQ

I have to read the data from file and populate the data to the DB.
Some of the data I read from the data source is NULL, the corresponding field in the DB is nullable, too. the field type is int.

I receive the error when I assign the null value to the property od anonymous object. Her eis the code:

var teams = from dirRecord in dt.AsEnumerable()
//where r.Field(0) == "Jeff"
//select new { FirstName = r["DirFName"], LastName = r["DirLName"] };
//let dirRecord = ((string)r[0]).Split('|')
select new
{
DirFName = dirRecord[0],
DirLName = dirRecord[1],
DirMultiple = Convert.ToInt32(dirRecord[2]),
DirAge = (dirRecord[3] == null) ? null : Convert.ToInt32(dirRecord[3]),
DirTenure = Convert.ToInt32(dirRecord[4]),
DirSince = Convert.ToDateTime(dirRecord[5]),
}

The compilation error:
Cannot assign <null> to anonymous type property

If I do not handle the error, I receive the runtime error for same reason.

What should I do?

RyanUpton

I think the database null is different from the C# null
have you tried database null instead?

Ryan

chuang4630

ASKER

I think the key is how to make the property of anonymous object nullable.

RyanUpton

What happens when you comment out
DirAge = (dirRecord[3] == null) ? null : Convert.ToInt32(dirRecord[3]),

chuang4630

ASKER

It compiles.

RyanUpton

Defenity using C# null when you should be using System.DbNull.Value

chuang4630

ASKER

Then I receive another compile error:

Type of conditional expression cannot be determined because there is no implicit conversion between '<null>' and 'int'

RyanUpton

Does the table column allow nulls? Is is a primary index. What happens if you use 0 instead?

chuang4630

ASKER

non-indexed. nullable int

UriS

Just make sure you cast to int? and you'll be fine...
See example hereafter...

System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("FName");
            dt.Columns.Add("LName");
            dt.Columns.Add("Age");

            System.Data.DataRow row;

            row = dt.NewRow();
            row["FName"] = "Billy";
            row["LName"] = "Joel";
            row["Age"] = 60;
            dt.Rows.Add(row);

            row = dt.NewRow();
            row["FName"] = "Tracy";
            row["LName"] = "Chapman";
            row["Age"] = DBNull.Value;
            dt.Rows.Add(row);

            var a = from r in dt.AsEnumerable()
                    select new
                    {
                        FirstName = (string)r[0],
                        LastName = (string)r[1],
                        Age = (r[2] == DBNull.Value) ? null : (int?)Convert.ToInt32(r[2])
                    };
            a.ToList();

Open in new window

ASKER CERTIFIED SOLUTION