I need to replicate a T-SQL statement that has an inner select, using LINQ:
SELECT *,
CustomerTypes.Description as CustomerType,
(Select Min(Distinct DocumentStatistics.StatDateTime) As LastStatCheck
From DocumentStatistics
Where DocumentStatistics.CustomerId = Customers.CustomerId) As LastStatCheck
FROM Customers
INNER JOIN CustomerTypes ON Customers.CustomerTypeID = CustomerTypes.CustomerTypeID
ORDER BY CustomerName
The following is the closest I was able to come, but it ends up returning all of the DocumentStatistics, 40,000 plus.
Dim docQuery = (From doc In data.DocumentStatistics _
Select doc)
Dim query = From customer In data.Customers _
Join docStat In docQuery _
On customer.CustomerID Equals docStat.CustomerID _
Select Customer = customer, LastStatCheck = _
(From doc In docQuery Where customer.CustomerID = doc.CustomerID _
Select doc.StatDateTime).Distinct().Min()
What am I missing? How do I replicate the initial SQL statement?
I can take VB or C# examples, and I am working with SQL Server database.