none
Problem with NULL values in LINQ Group Join RRS feed

  • Question

  • Hi,

    I'm building a dataset based application using vb.net. I have a dataset which has many datatables. After all the datatables have been filled when the form load, I have a LINQ statement to retrieve data from those datatables as a Group Join Query and fill to another datatable inside my dataset.

    This is my query:

      Dim query = From B In Books _
                Group Join  RI in RecIn On RI.BookID Equals B.BookID Into BRI = Group _
                From w In BRI.DefaultIfEmpty _
                Group Join BO In BillOut On BO.BookID Equals B.BookID Into BOI = Group _
                From x In BOI.DefaultIfEmpty _
                Group Join BR_I In InOut On BR_I.BookID Equals B.BookID Into BR_I = Group _
                From y In BR_I.DefaultIfEmpty _
                Group Join BO_I In OutIn On BO_I.BookID Equals B.BookID Into BO_I= GROUP _
                From z In BO_I.DefaultIfEmpty _
                Select BookID = B.BookID, BookTitle = B.BookTitle, QTYOUT = x.QTYOUT, QTY_OUT=z.QTY_OUT, QTYIN = w.QTYIN, QTY_IN=y.QTY_IN , Price=b.SellingPrice, Cost=B.OriginalPrice

    My problem is that I cannot handle the null value in my query when any of my joined datatable has no data.

    Is there anyone can give me a trick to replace a null value with something else?

    Here is my sample query result

     


    BookID BookTitle QTYOUT QTY_OUT QTYIN QTY_IN Price Cost

    F002

    New Interchange Intro WB

    6

    5

    31

    null

    7000.0000

    5000.0000

    F003

    Side by Side

    5

    4

    1

    null

    9000.0000

    7000.0000

    F005

    New Interchange 1 WB

    null

    3

    1

    null

    6500.0000

    5000.0000

    F006

    Listen To Me!

    null

    1

    20

    null

    7000.0000

    5600.0000

    F009

    New Interchange 2 WB

    null

    6

    1

    null

    6500.0000

    5000.0000

    F010

    In the Real World

    6

    11

    1

    null

    8000.0000

    6800.0000

    F011

    Thoughts & Notions

    1

    6

    1

    null

    8000.0000

    5000.0000

    F013

    New Interchange 3 WB

    11

    16

    1

    null

    6500.0000

    5000.0000

    F015

    Cause & Effect

    null null

    1

    null

    8000.0000

    5800.0000

    F017

    New Interchange 4 WB

    null null

    1

    null

    6500.0000

    5000.0000

    F018

    Vocabulary 1

    null null

    1

    null

    60000.5000

    62000.0000

    F023

    Vocabulary 2

    null

    1

    1

    null

    6000.0000

    5000.0000

    F025

    New Interchange Intro SB

    null null null null

    26000.0000

    24000.0000

    H025

    Survival English 1&2

    null null null null

    12000.0000

    10592.0000

    • Moved by Liliane Teng Wednesday, February 9, 2011 3:30 AM (From:Visual Basic General)
    Monday, February 7, 2011 1:20 PM

Answers

All replies

  • I'm assuming you want to hand these nulls with something else such as a default value of 0

     Dim query = From B In Books _
          Group Join RI in RecIn On RI.BookID Equals B.BookID Into BRI = Group _
          From w In BRI.DefaultIfEmpty _
          Group Join BO In BillOut On BO.BookID Equals B.BookID Into BOI = Group _
          From x In BOI.DefaultIfEmpty _
          Group Join BR_I In InOut On BR_I.BookID Equals B.BookID Into BR_I = Group _
          From y In BR_I.DefaultIfEmpty _
          Group Join BO_I In OutIn On BO_I.BookID Equals B.BookID Into BO_I= GROUP _
          From z In BO_I.DefaultIfEmpty _
          Select BookID = B.BookID, BookTitle = B.BookTitle, QTYOUT = If(x.QTYOUT,0), QTY_OUT=If(z.QTY_OUT,0), QTYIN = If(w.QTYIN,0), QTY_IN=If(y.QTY_IN,0) , Price=b.SellingPrice, Cost=B.OriginalPrice
    
    
    

    This is using the VB equivalent of the Null coelescing functionality.  This should help if its returning Null, see how that works out.

     

    Monday, February 7, 2011 8:00 PM
  • Hi,

    I still don't any good results. There is an error message (First operand in a binary 'If' expression must be nullable or a reference type.)

    In my For Each Loop I can get some of the first records that aren't Null value but when it loops on a Null record there is an error like below.

    System.NullReferenceException: Object reference not set to an instance of an object.
       at BSMS.MovementInventoryReport._Lambda$__19(VB$AnonymousType_7`2 $VB$It) in E:\Visual Studio 2008\Projects\BSMS1\BSMS\Forms\MovementInventoryReport.vb:line 52
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
       at BSMS.MovementInventoryReport.btnPreview_Click(Object sender, EventArgs e) in E:\Visual Studio 2008\Projects\BSMS1\BSMS\Forms\MovementInventoryReport.vb:line 78

    Tuesday, February 8, 2011 12:31 PM
  • Sovantha,

    Why are you using the dataset at all in a join.

    But first: try to avoid the word null in messages, it is one of the most confusing used words in Microsoft programming because every Microsoft team seems to have its own explanation around it. 

    In C# (and Linq) it can be:

    a database value type which can be unused (this is something else than empy or blankspace) and is special related to databases
    a non referenced instance
    an empty nullable type value type
    an empty value (although that gives direct a warning).

    Luckily is that beside the nullable type which has introduced again that awfull word in vb, for us in VB VBNull.Value (the database type) and Nothing which means an non instanced class or a default value type. 

    However, 

    Try to use the Linq to SQL instead of the dataset which is created before linq. 

    To do this do

    Project -> Add new Item -> Linq to SQL (don't forget to give it a name before you enter)

    Follow the wizard which is quite alike as getting the dataset

    Now a datacontext is generated which is likewise a DataSet but already completely initialized to be used with Linq

     


    Success
    Cor
    Tuesday, February 8, 2011 1:40 PM
  • Thank you very much.
    • Marked as answer by sovantha Wednesday, February 9, 2011 1:47 PM
    Wednesday, February 9, 2011 1:47 PM