Converting SQL Query to LINQ 2 SQL expression

Question

How can i rewrite the below SQL query to its equivalent LINQ 2 SQL expression (both in C# and VB.NET)

    SELECT t1.itemnmbr, t1.locncode,t1.bin,t2.Total
        FROM  IV00200 t1 (NOLOCK)
                     INNER JOIN
              IV00112 t2 (NOLOCK)
                 ON  t1.itemnmbr = t2.itemnmbr 
                 AND t1.bin = t2.bin
                 AND t1.bin = 'MU7I336A80'

Answer 1

EDIT: I noticed you asked for both C# and VB.Net, so I added a VB example.

C#

using (var txn = new TransactionScope(
    TransactionScopeOption.Required, 
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadUncommitted
    }
))
{
    var results = from t1 in db.IV00200 
                  join t2 in db.IV00112 
                    on new { t1.itemnmbr, t1.bin }
                    equals new { t2.itemnmbr, t2.bin }
                  where t1.bin == 'MU7I336A80'
                  select new {t1.itemnmbr, t1.locncode, t1.bin, t2.Total};

    // Do something with your results

}

VB.Net

Dim results
Dim transOptions = New TransactionOptions
transOptions.IsolationLevel = IsolationLevel.ReadUncommitted

Using txn As New TransactionScope(TransactionScopeOption.Required, transOptions)
    results = From t1 In db.IV00200 _
              Join t2 In db.IV00112 _
                On New With {.itemnmbr = t1.itemnmbr, .bin = t1.bin} _
                Equals New With {.itemnmbr = t2.itemnmbr, .bin = t2.bin} _
              Where t1.bin = "MU7I336A80" _
              Select New With {.itemnmbr = t1.itemnmbr, .locncode = t1.locncode, .bin = t1.bin, .Total = t2.Total}

    ' Do something with your results

End Using

You will need to add System.Transactions to your project's references. The TransactionScope block recreates the nolock conditions in your original query.

[TransactionScope / NoLock Source: http://madprops.org/blog/linq-to-sql-and-nolock-hints/]