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'
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/]
