Linq Query in VB

Question

Good Day,

I am querying my database using Linq and I have run into a problem, the query searched a column for a search phrase and based on if the column has the phrase, it then returns the results, The query is below,

 Dim pdb = New ProductDataContext()             
    Dim query = 
        From a In pdb.tblUSSeries
        Join b In pdb.tblSizes_ On a.Series Equals b.Series
        Where
        a.Series.ToString().Equals(searchString) Or                                             
        b.Description.Contains(searchString) Or Not b.Description.Contains(Nothing)
        Order By b.Series, b.OrderCode Ascending
        Select New CustomSearch With 
            {
                .Series = a.Series, 
                .SeriesDescription= a.Description,
                .Coolant = a.Coolant,
                .Material = a.Material,
                .Standard = a.Standard,
                .Surface = a.Surface,
                .Type = a.Type,
                .PointAngle = a.PointAngle,
                .DiaRange = a.DiaRange,
                .Shank = b.Shank,
                .Flutes = b.Flutes,
                .EDPNum = b.EDPNum, 
                .SizesDescription = b.Description,
                .OrderCode = b.OrderCode                             
            }             
    Return query

I think the problem is that, in the table certain rows are NULL, so when it is checking the column for the phrase and it encounters a row that is null it, breaks and returns this error,

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

I have ran this query against another column that has all the rows populated with data and it returns the results ok.

So my question is how can I write it in VB to query the db with the supplied searchstring and return the results, when some of the rows in the columns have null values.

Any help would be great.

Answer 1

The exception occurs when you make the projection (i.e. select new CustomSearch) And yes your trying to assign Null to some int property (Not sure which one of your properties that is)

one of 2 choices :

1) Use nullalbe types for your properties (or just that one property).

2) project with an inline If ( ?? in C#) , I don't know VB so don't catch me on the syntax.

Taking Series just as an example i don't know if it's an int or if that's the problematic property

   Select New CustomSearch With 
        {
            .Series =  If(a.Series Is Nothing,0, CInt(a.Series))  
        }

    In C# 

    Select new CustomSearch 
        {
            Series =  a.Series ?? 0; 
        }