C# System.Data.StrongTypingException: Value for column '<columnName>' in '<tableName>' table is DBNull in LINQ query

Question

I need to manage 3 databases with many tables and NULL values that can be almost everywhere. For some cases I've found a solution, but now I need help.

The code so far:

  public DataTable queryResults()
    {

    stockDataSet stockDS = new stockDataSet();
    extensionStockDataSet extensionStockDS = new extensionStockDataSet();      administrationDataSet administrationDS = new administrationDataSet();

    stockDataSetTableAdapters.sparePartsTableAdapter sparePartsAdapter = new stockDataSet TableAdapters.sparePartsTableAdapter();
    sparePartsAdapter.Fill(stockDS.spareParts);

    administrationDataSetTableAdapters.usersTableAdapter usersAdapter = new administrationDataSetTableAdapters.usersTableAdapter();
    usersAdapter.Fill(administrationDS.users);

    extensionStockDataSetTableAdapters.dataPartsTableAdapter dataPartsAdapter = new extensionStockDataSetTableAdapters.dataPartsTableAdapter();
    dataPartsAdapter.Fill(extensionStockDS.dataParts);


    var query = from sp in stockDS.spareParts

                join ad in administrationDS.users
                on sp.idUser equals ad.idUser

                join ext in extensionStockDS.dataParts
                on sp.idStock equals ext.idStock

                into joined 
                from j in joined.DefaultIfEmpty()


                where sp.idTypePart != 7

                orderby sp.idUser

                select new { namePart = sp.namePart,
                             idPartInExtStock = (j == null ? 0 : j.idPart),
                             notes = sp.notes,
                             parNo = ad.parno,
                             descr = ma.description,
                             idBrand = (j == null || j.idBrand == null ? "NOT AVAILABLE”: j.idBrand),
                             idModel = (j == null || j.idModel == null ? " NOT AVAILABLE " : j.idModel),
                             idVersion = (j == null || j.idVersion == null ? "NOT AVAILABLE" : j.idVersion)

                             };


    DataTable results = new DataTable();

    results = LINQToDataTable(query); //converts LINQ results to DataTable with System.Reflection

    return results;

    }

Going double click on every xsd DataSet file in Explore Soluzion panel in Visual Studio -> select a field of any table -> Properties panel -> NullValue -> (Null) I can allow the Null value for every String, that's what I've done for

notes = sp.notes
parNo = ad.parno
descr = ma.description

This can't be done every data type, e.g. for System.Int32 and System.DateTime types: if you try to set for them NullValue -> (Null) or NullValue -> (Empty), it returns the message:

Property value not valid

So, for idPartInExtStock that is a System.Int32 type, I've found this solution:

idPartInExtStock = (j == null ? 0 : j.idPart)

that is working, and I've implemented it (just for try) also for String fields

 idBrand = (j == null || j.idBrand == null ? "NOT AVAILABLE”: j.idBrand),
 idModel = (j == null || j.idModel == null ? " NOT AVAILABLE " : j.idModel),
 idVersion = (j == null || j.idVersion == null ? "NOT AVAILABLE" : j.idVersion)

Now I'm getting another Null problem with the line:

where sp.idTypePart != 7

it returns at runtime the folliwing error from the stockDataSet.Designer.cs :

System.Data.StrongTypingException: Value for column idTypePart in spareParts table is DBNull

Unfortunately I can't change values in the databases, I need to manage them as they are.

My questions:

1. how can I solve this System.Data.StrongTypingException: Value of column idTypePart in spareParts table is DBNull, considering that sp.idTypePart is System.Int32 type, the cast on it doesn't work and is not possible to set the (Null) or (Empty) value for it?

So far I've tried:

1A. (int?)sp.idTypePart != 7 ==> it says 'The cast is redundant', so this is useless

1B. sp?.idTypePart != 7 ==> compiles, runs, but returns again System.Data.StrongTypingException: Value for column idTypePart in spareParts table is DBNull

1C. where (sp.idTypePart != 7 && sp.idTypePart != null) ==> returns the warning 'expression value is always true', so this is useless

2. Isn't there some method to avoid all the definitions of these exeptions for manage Null values in every field and for every data type?

Answer 1

Ok, for now the problem seems solved. I mean the "read data" part, I can extract the information correctly and success my query.

What I've done is simply going:

double click on xsd stockDataSet in Explore Solution panel -> select idTypePart field in spareParts table Properties panel ->

so

(1.) : Datatype -> System.String (instead of the default System.Int32 preset)

and then

(2.) : NullValue -> (Null)

I've done this even before yesterday, but for some reason that I can't figure out this wouldn't work at time.

Anyway now the reading problem is solved, and this answer is for that question.

I wonder about what will happen when I'll write data, since this field in the database is an int, while in the dataset is a string.