views:

319

answers:

4

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.

+1  A: 

John,

Check out a product named Linqer (I have no association with this product). It takes a SQL statement and converts it to LINQ. It's able to convert almost any query you throw at it. It's been very useful to me. It's also taught me a lot about how LINQ works.

Randy

Randy Minder
Thanks for the suggestion Randy, Linqer gave me the answer I needed.
John Maloney
+1  A: 

What you really want is a group join.

C# Example:

var query = from c in data.Customers
            join ds in data.DocumentStatistics
            on c.CustomerID equals ds.CustomerID into stats
            select new
            {
                Customer = c,
                LastStatCheck = stats.Min(s => s.StatDateTime)
            };
Aaronaught
+3  A: 

You need to use the Group Join function to achieve this, something like the following in C#:

var result = from customer in data.Customers
             join docStat in data.DocumentStatistics
                    on customer.CustomerID equals docStat.CustomerID into stats
             where stats.Count() > 0
             select new
             {
                 Customer = customer,
                 LastStatCheck = stats.Min(res => res.StatDateTime)
             };

and in VB.Net

Dim result = From customer In data.Customers _
             Group Join docStat In data.DocumentStatistics _
                  On customer.CustomerID Equals docStat.CustomerID Into stats = Group _
             Where stats.Count() > 0 _
             Select New With _
             { _
                 .Customer = customer _
                 .LastStatCheck = stats.Min(Function(res) res.StatDateTime) _
             }
Simon Fox
Thanks for the response, Simon. There seems to be an error in the initialization of the queryable name "stats". Tried to instantiate it somewhere else but am unsure of the correct procedure to create it. Do you have any suggestions on how to initialize "stats"?
John Maloney
Hi John, I missed the '= Group' on the end of the Group Join. The above should now work as required...
Simon Fox
A: 

Linqer gave me the following results from my original SQL statement:

From customer In data.Customers _
                    Select _
                    Customer = customer, _
                    LastStatCheck = (CType((Aggregate lastDateChecked In _
                    (From docStat In data.DocumentStatistics _
                    Where docStat.CustomerID = customer.CustomerID _
                    Select docStat) Into Min(lastDateChecked.StatDateTime)), DateTime?))

The helpful thing about Linqer was that I can put the t-SQL statement in and run the query next to the LINQ statement.

John Maloney
I hadn't seen the Aggregate keyword before so did some searching and found this http://weblogs.asp.net/fbouma/archive/2008/05/21/vb-net-beware-of-the-aggregate-keyword.aspx May want to be careful with that, I have updated my answer and it should now do what you want without using the Aggregate keyword...
Simon Fox
After reading this article I agree. It appears that there is a risk when using the Aggregate keyword that you will introduce a performance bug. Although this risk seems minimal, if I can accomplish the task by not taking the risk it is that much better. Thanks for the research.
John Maloney