LINQ query fails for NULL values

var query = from t1 in Table1
            join t2 in Table2
                on new { t1.Id }
                equals new { t2.Id}
            select new
            {
                t1.Id,
                t1.FirstName,
                t1开发者_如何转开发.MiddleName,//allows null values in the database
                t1.LastName,
                t1.phone //allows null values in the database

            };
if(query.Count()>0)//fails here"The value for column MiddleName in table'Table1' is DBNULL"
{
}

Is there a way in which I can get all the rows including null values for middleName and Phone in my LINQ query?

If you are using linq-to-datasets you must manually convert nullable columns to null because their value in DataRow is DBNull.Value. In strongly typed DataSet you should be able to do something like:

var query = from t1 in Table1
        join t2 in Table2
            on new { t1.Id }
            equals new { t2.Id}
        select new
        {
            t1.Id,
            t1.FirstName,
            t1.IsMiddleNameNull ? null : t1.MiddleName,
            t1.LastName,
            t1.IsPhoneNull ? null : t1.Phone 
        };

In untyped DataSet you will call something like t1.IsNull("MiddleName") ? null : t1["MiddleName"]

It sounds like the metadata is out of sync with your DB schema. It seems as if when the classes were generated for your schema MiddleName was not nullable, but now it is. If that's the case, you need to refresh your EDMX if you're using Entity Framework or refresh your classes if you're using LINQ to SQL.

Could you please give this a shot

var query = from t1 in Table1
            join t2 in Table2
                on new { t1.Id }
                equals new { t2.Id}
            select new
            {
                Id = t1.Id,
                FirstName = t1.FirstName,
                MiddleName = t1.MiddleName,//allows null values in the database
                LastName = t1.LastName,
                Phone = t1.phone //allows null values in the database

            };
if(query.Count()>0)//fails here"The value for column MiddleName in table'Table1' is DBNULL"
{
}

The problem is that a new anonymous object has its properties defined on-the-fly with types inferred from the values.

In such a line

MiddleName = t1.MiddleName,//allows null values in the database 

a new property called MiddleName is created whose type is t1.MiddleName's type. But if t1.MiddleName is null, what is the type ??? Null has no type.

To prevent any ambiguousity simply put

MiddleName = (string)t1.MiddleName,

to let the compiler know that anyway it's a string, even if not provided.