none
Incorrect join if no primary/foreign keys exist? RRS feed

  • Question

  • Hi,

    I found this how to video that does almost exactly what I need: generate XML from SQL for customers orders.

    http://msdn.microsoft.com/en-us/vbasic/bb887660.aspx

    The problem I have is my ERP database is a poor design and has no primary and foreign keys. The relationship is OrderHeader.OrdNo = OrderDetail.OrdNo.  When the XML is created, all detail records are returned for each header, not just the detail for that specific order.

    I ran SQL profiler and see the join is: "From OrderHeader left outer join OrderDetail on 1=1 "

    This database is part of a larger ERP system so I am not at liberty to modify it. How can I work around this?

    A 2nd issue is the customer table uses a CusNo field that is a varchar(20) while the OrderHeader.CusNo field is char(20). Linq won't let me create a relationship when the datatypes are different. You can write SQL queries against these two table but Linq is more strict?

    Thanks,

     


    • Edited by RD_vb Monday, October 17, 2011 11:52 PM
    Monday, October 17, 2011 10:22 PM

Answers

  • Hi Re_vb,

    If there is not foreign keys between the tables, you can try to use Where condtion to recur.

    Module Module1
        Sub Main()
            Dim db As New DataClasses1DataContext
            Dim persons = <Persons>
                              <%= From person In db.Persons _
                                  Select <person>
                                             <ID><%= person.Id %></ID>
                                             <Name><%= person.Name %></Name>
                                             <Vacations>
                                                 <%= From vacation In db.Vacations Where vacation.EId = person.Id
                                                     Select <vaca>
                                                                <Days><%= vacation.Days %></Days>
                                                            </vaca>
                                                 %>
                                             </Vacations>
                                         </person>
                              %>
                          </Persons>
            persons.Save("d:\p.xml")
            Process.Start("d:\p.xml")
        End Sub
    End Module
    

    For different Type, we can convert to appropriate types to compare:

    From vacation In db.Vacations Where vacation.EId =CType(person.Name, Long)

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by RD_vb Wednesday, October 19, 2011 7:17 PM
    Wednesday, October 19, 2011 2:57 AM
    Moderator

All replies

  • Hi Re_vb,

    Welcome!
    We will do some more pending research  about your problem and come back as soon as possible, Thanks for understanding.
    Have a nice day.

     

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, October 18, 2011 9:17 AM
    Moderator
  • Hi Re_vb,

    If there is not foreign keys between the tables, you can try to use Where condtion to recur.

    Module Module1
        Sub Main()
            Dim db As New DataClasses1DataContext
            Dim persons = <Persons>
                              <%= From person In db.Persons _
                                  Select <person>
                                             <ID><%= person.Id %></ID>
                                             <Name><%= person.Name %></Name>
                                             <Vacations>
                                                 <%= From vacation In db.Vacations Where vacation.EId = person.Id
                                                     Select <vaca>
                                                                <Days><%= vacation.Days %></Days>
                                                            </vaca>
                                                 %>
                                             </Vacations>
                                         </person>
                              %>
                          </Persons>
            persons.Save("d:\p.xml")
            Process.Start("d:\p.xml")
        End Sub
    End Module
    

    For different Type, we can convert to appropriate types to compare:

    From vacation In db.Vacations Where vacation.EId =CType(person.Name, Long)

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by RD_vb Wednesday, October 19, 2011 7:17 PM
    Wednesday, October 19, 2011 2:57 AM
    Moderator