none
A Problem in Linq to SQL: Group Join RRS feed

  • Question

  • Dear All,
    I have 2 tables, Item & Trnsactn,
    item has the following fields(id,Name,Class),
    trnsactn has (id,Employee_id,Item_id,TrDate,Qty,QtyOut),
    here the value of Qty,QtyOut determines the type of Trnsactn.
    in Linq to SQL i need to treat with Item table and additional field to it "Qty" witch is the Qty on hand.
    I did that this way:

    Dim QtySrc = From It In DB.Items _

    Group Join Tr In DB.Trnsactns On It.id Equals Tr.Item_id Into _

    Theresult = Group , _

    Qty = Sum(Tr.Qty - Tr.QtyOut) _

    Select Theresult, It.id, It.Name, It.Note, Qty

     

    ItemBindingSource.DataSource = QtySrc.ToList


    An Error appeared when compiling telling that Qty is a double value and can't be null,
    i tried to use if or nullable values but that was useless,would you please coordinate.
    Thanks in advance.
        Ayham
    Wednesday, October 14, 2009 6:34 AM

All replies

  • Hi AyhamMhd,

    Check to if 'Tr.Qty' or 'Tr.QtyOut' would fetch null value from the database.

    Generally, if the 'Tr.Qty'  and the 'tr.QtyOut' are set 'not null' in the DBML while they might fetch 'null' value from the database, such an error will occur.


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, October 15, 2009 7:12 AM
  • Hi Mr. Lee
    thanks for your kind reply,
    but really the reason was that i have items with no transactions,
    thus some items really don't have Sum(Tr.Qty - Tr.QtyOut)so the null value appeared.
    i hope i've illustrated more,would you please help. 
    Best Regards.


    Saturday, October 17, 2009 8:31 AM