user2184608 user2184608 - 7 months ago 27
Vb.net Question

Querying table through association using LINQ to SQL vb.net

I have 3 table

CustNameTbl

_________________
| ID | CustName |
| 1 | Jel Farm |
| 2 | TSL. TRD.|
| 3 | YAZAKI |
| 4 | TAILIN |


ItemNameTbl

_________________
| ID | ItemName |
| 1 | HSC |
| 2 | Pad |
| 3 |Partition |
| 4 | B002001 |
| 5 |Box for B3|
| 6 |High Speed|


ItemInfoTbl

__________________________________________________
| ID | CustId | ItemId | Qty | Price | Remarks |
| 1 | 1 | 1 | 50 | 2 | |
| 2 | 1 | 2 | 200 | 1 | Set A |
| 3 | 1 | 3 | 10 | 5 | Set B |
| 4 | 4 | 5 | 15 | 2 | |
| 5 | 4 | 6 | 10 | 1 | |
| 6 | 2 | 4 | 150 | 3 | |


I have two ASSOCIATION


  1. CustNameTbl as Parent ItemInfoTbl as Child

  2. ItemNameTbl as Parent ItemInfoTbl as Child



Using Linq to Sql, How to query ItemInfoTbl using CustNameTbl as WHERE CLAUSE and having a result where the value of CustId and ItemId in ItemInfoTbl will get there data in the other table (CustNameTbl,ItemNameTbl)

Like This:

____________________________________________________
| ID | CustId | ItemId | Qty | Price | Remarks |
| 4 | TAILIN |Box for B3| 15 | 2 | |
| 5 | TAILIN |High Speed| 10 | 1 | |


But my code didn't get what I want

Dim CustName As String = "TAILIN"
Using CustItem As New CustItemDataContext
Dim resultCustItem = From result In CustItem.ItemInfoTbls
Where result.CustNameTbl.CustName = CustName
DataGridView1.DataSource = resultCustItem
End Using


Any idea or link to get me started. Thank you, and sorry for my English.

Answer

Did not test but can give you an idea.

Dim result = from t in  YourDatabase.ItemInfoTbl
                        join p in YourDatabase.ItemNameTbl on t.ItemID equals p.ID 
                        join c in YourDatabase.CustNameTbl on t.CustID  equals c.ID            
Where c.CustName = CustName
                        select new
                        {
                            t.ID,
                            t.CustId,
                            t.ItemID,
                            t.Qty,
                            t.Price
                         };

Helpful source LINQ join multi table