LINQ to SQL in VB.NET

LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.

LINQ to SQL allows .NET developers to write “queries” in their .NET language of choice to retrieve and manipulate data from a SQL Server database.

Select one

Dim first = m_db.Customers.FirstOrDefault(Function(c) c.CustomerId = "001")

Where, null, contains & type

Dim regionList = New String() {"NY", "PA"}

Dim customers = From c In db.Customers _
                Where TypeOf c Is Customer AndAlso _
                      (c.Region Is Nothing OrElse _
                       regionList.Contains(c.Region)) Select c

Insert Parent / Child records

' Adding Records – 
' (1) Create a new object and sub-objects
' (2) Add them to the DataContext collection
' (3) Call SubmitChanges 

Dim m_db As New MyDataContext()
Dim oOrder As Order
Dim asList() As String

' (1)
' Create a parent record
oOrder = New Order

oOrder.UserId = piUserId
oOrder.UserName = piUserName

' Create child records and add to this order
Dim oOrderItem As OrderItem
Dim nIndex, nCount As Integer
Dim nProductId As Integer

nCount = asList.Length
For nIndex = 0 To nCount - 1
    nProductId = CType(asList(nIndex), Integer)
    oOrderItem = New OrderItem

    oOrderItem.ProductId = nProductId
    oOrderItem.ProductName = 
    (from p in db.Products 
     where p.ProductId = nProductId
     select p.ProductName)

    oOrder.OrderItems.Add(oOrderItem)
Next

' (2)
m_db.Orders.InsertOnSubmit(oOrder)

' (3)
m_db.SubmitChanges()