LINQ TO SQL Master Detail datagridview

martin05
martin05 used Ask the Experts™
on
Can anyone tell me how to link master detail using linq ?
Im not sure if it is possible or if this is completely the wrong way to do it , any comments on how to do it or a better way of doing it would be appreciated.

The question marks in the code below is where I am struggling.


Note.........NINumber is not the primary key on either table.
Dim db As New DataContext
            Dim query = From p In db.Customers _
                            Where p.NINumber = TextBox1.Text _
                            Order By p.LastName _
                            Select p.ID, p.FirstName, p.LastName, p.NINumber, p.PostCode, p.DOB
 
            DataGridView1.DataSource = query
            CustomerBindingSource.DataSource = query
 
 
        Dim dbdet As New DataContext
        Dim query2 = From q In dbdet.Enquiries _
                        Where q.NINumber = ???????????  _
                        Select q.OpenedDate, q.Description, q.Comments
        DataGridView2.DataSource = query2

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have to use 2 Linq queries since you are presenting them in 2 seperate DataGridViews. So, like the previous, replace the ????????????? with TextBox1.Text

Author

Commented:
doh.....!!!! ok how do i then get the datagrid 2 to display the details when the selected record in datagrid1 changes ?

the query from textbox 1 might return multiple rows.

Author

Commented:
sorry It doesnt work ,I forgot textbox1 is a multifield search so it isnt always the ninumber that is searched,but it will always return the ninumber in the first datagrid.
Retired
Distinguished Expert 2017
Commented:
Hi martin05;

The best way to do a Master/Detail with Linq to SQL is to let the IDE environment build the underneath plumbing for you. I will use the Microsoft Northwind database to show how to do it.

1. Add a Linq to SQL Class to your project.
2. Once the designer is displayed add the Orders and Order_Details table to the design surface.
3. Save and compile the project.
4. Add a DataSource object to your project By clicking on the Menu -> Data -> Add New DataSource.
5. In the DataSource Window select Object and click on the Next button.
6. Expand the Project Name node and then Expand the node with the Project Name again.
7. Now select the Master table name, in this case it is Orders.
8. Click on the Finish button.
9. Open the DataSource window if it is not already displayed.
10. Drag the Orders table on to the form. This will add one BindingSource component named OrdersBindingSource and a BindingNavigator called OrdersBindingNavigator as well as the Master DataGridView with all plumbing connected for you.
11. Now drag the Order_Details table on to the form from the DataSource window. This will add a BindingSource component called Order_DetailsBindingSource to the form and the Details DataGrridView.
12. Now query the data source as shown in the code snippet below and that is it.
13. Run the project.

Fernando
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim db As New NorthwindDataContext()
 
        OrdersBindingSource.DataSource = From order In db.Orders _
                                         Select order
 
    End Sub
 
End Class

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial