TypedDataSet Linq query fails with dbnull error

Hi I am having problems figuring out the correct LINQ syntax.

I have the following dataset, where DtHistory.NotesId can be null if there are no notes associatd with it.

TypedDataset Schema
I am using the following code to query the dataset;

Dim HistoryQuery = From o In ds.DtHistory
                           Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
                           Join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
                           Order By o.DateAccessed Descending
                           Select New With { _
                                  o.DtHistory_Id, _
                                  ol.PayrollRef, _
                                  ol.Name, _
                                  o.DateAccessed,
                                  nt.Note _
                                    }

For Each res In HistoryQuery

            Dim lvi As ListViewItem = ListView1.Items.Add(res.PayrollRef)
            lvi.SubItems.Add(res.Name)
            lvi.SubItems.Add(res.DateAccessed)
            
Next

Open in new window


But it throws the exception;  "The value for column 'NotesId' in table 'DtHistory' is DBNull.".

I have read various suggestions like using ".DefaultIfEmpty()"  and the generated property ".IsNoteNull" but I cannot get them to work in my Linq query.

All i want to do is retrieve all items from the history tab with the associated values from the other two tables (if not dbnull)

Anyone able to help?
LVL 1
ITPOLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Does this work:
Dim HistoryQuery = From o In ds.DtHistory
                           Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
                           LEFT join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
                           Order By o.DateAccessed Descending
                           Select New With { _
                                  o.DtHistory_Id, _
                                  ol.PayrollRef, _
                                  ol.Name, _
                                  o.DateAccessed,
                                  nt.Note _
                                    }
ITPOLAuthor Commented:
Just tried it, but no.

"Left" doesn't seem to be a linq keyword
AndyAinscowFreelance programmer / ConsultantCommented:
Ah well, worth a try.  It is how one handles that relationship in SQL.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Fernando SotoRetiredCommented:
Hi ITPOL;

Seeming that the value of Note can be null/Nothing test it for Nothing and assign it a value if it is Nothing otherwise use the value. Try the code below.
Dim HistoryQuery = From o In ds.DtHistory
                   Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
                   Join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
                   Order By o.DateAccessed Descending
                   Select New With { _
                          o.DtHistory_Id, _
                          ol.PayrollRef, _
                          ol.Name, _
                          o.DateAccessed,
                          .Note = If(nt.Note Is Nothing, " ", nt.Note) _
                   }

Open in new window

ITPOLAuthor Commented:
That was one of the things I tried actually.  Unfortunately it still throws the exception.

From what I've read its to do with the way typed datasets are generated.

StrongTypingException
ITPOLAuthor Commented:
Same problem here; but I cant see how to replicate this into this version of linq


http://stackoverflow.com/questions/881225/linq-to-dataset-dbnull-problem-null-reference-exception

ITPOLAuthor Commented:
and here
Fernando SotoRetiredCommented:
Try changing this line

.Note = If(nt.Note Is Nothing, " ", nt.Note) _

to this

.Note = If(nt.Note Is Nothing, 0, nt.Note) _
Fernando SotoRetiredCommented:
What I think may be happening is that the object itself is null so try this if the other did not work.

.Net = If(nt Is Nothing, " ", nt.Note) _
ITPOLAuthor Commented:
Thanks Fernando,

What I think may be happening is that the object itself is null so try this if the other did not work.

.Net = If(nt Is Nothing, " ", nt.Note) _

Afraid this didn't work either.  I also tried replacing the blank with 0.
ITPOLAuthor Commented:
Its double-bank holiday here in the UK, so I wont look at this again until tuesday.  Thanks for your help so far.
ArkCommented:
Where o.NotesId.HasValue = True
ITPOLAuthor Commented:
Where o.NotesId.HasValue = True

Won't this filter my results to only rows that have a value?  in any case there was no "hasvalue" property on NotesId
AndyAinscowFreelance programmer / ConsultantCommented:
In your code snippet you never actually use the nt.Note - so do you need the DtNotes table in the query?  If not then leave that out and see if that makes a difference.
ITPOLAuthor Commented:
haha, Well spotted but yes, I do.  

I want to show it on the form if there is a note associated with the record.
AndyAinscowFreelance programmer / ConsultantCommented:
OK, thought that might be the case.

At which line of code do you actually get the exception with the DBNull and when?
ITPOLAuthor Commented:
Here;

For Each res In HistoryQuery
AndyAinscowFreelance programmer / ConsultantCommented:
The first time it meets it or at a later point when looping.  (It might not be relevant but, then again, it might help in localising the problem).
ITPOLAuthor Commented:
Sorry I should have said.  It's as soon as it starts. i.e. the very first loop.
CodeCruiserCommented:
Is NotesID set to be nullable in your dataset designer?
ITPOLAuthor Commented:
Yes it was.  I don't like it, but I've got around the issue by having a blank note, that the history will point to if there is no real note item for the history to point to.

Surprised there wasn't a simple solution to this by hey-ho.

thanks for all help on this

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITPOLAuthor Commented:
Solution was how I got round the issue, unfortunately couldn't find an actual answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.