locked
how to do this in linq RRS feed

  • Question

  • Thank you in advance for any help with this matter.

    Using northwind as an example, I’m trying to get the number of customers that have more than 10 orders, and I need to do this in Linq.

    Here’s the SQL I’ve got that works:

    select count(distinct customerID)

    from Orders

    where customerID in (

                                  select customerID

                                  from Orders

                                  group by customerID

                                  having(Count(customerID) > 10)

                                  )

     

    Does anyone know how to do this in Linq, or an equivalent?

    Note, I’m not trying to get a list of the customers that have more than 10 orders, just one single integer returned that indicates the number that do.

    Thanks much everyone!

     


    Daniel Sprague
    Tuesday, March 22, 2011 6:31 PM

Answers

  • Hi Me Dan;

    The following C# Linq query will do what you need.

    DataClasses1DataContext ctx = new DataClasses1DataContext( );
    
    var query = ( from o in ctx.Orders
           group o by o.CustomerID into orderGroup
           where orderGroup.Count() > 10
           select orderGroup ).Count();
    
    


    And this is the SQL sent to the server.

    SELECT COUNT(*) AS [value]
    FROM (
      SELECT COUNT(*) AS [value]
      FROM [dbo].[Orders] AS [t0]
      GROUP BY [t0].[CustomerID]
      ) AS [t1]
    WHERE [t1].[value] > @p0
    -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [10]
    


    If the programming language is not the one you are using please let me know and I will translate.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Alan_chen Wednesday, March 23, 2011 7:11 AM
    • Marked as answer by Me Dan Wednesday, March 23, 2011 3:59 PM
    Tuesday, March 22, 2011 9:28 PM

All replies

  • Hi Me Dan;

    The following C# Linq query will do what you need.

    DataClasses1DataContext ctx = new DataClasses1DataContext( );
    
    var query = ( from o in ctx.Orders
           group o by o.CustomerID into orderGroup
           where orderGroup.Count() > 10
           select orderGroup ).Count();
    
    


    And this is the SQL sent to the server.

    SELECT COUNT(*) AS [value]
    FROM (
      SELECT COUNT(*) AS [value]
      FROM [dbo].[Orders] AS [t0]
      GROUP BY [t0].[CustomerID]
      ) AS [t1]
    WHERE [t1].[value] > @p0
    -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [10]
    


    If the programming language is not the one you are using please let me know and I will translate.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Alan_chen Wednesday, March 23, 2011 7:11 AM
    • Marked as answer by Me Dan Wednesday, March 23, 2011 3:59 PM
    Tuesday, March 22, 2011 9:28 PM
  • Works like a charm, Fernando!

    Thanks much!!!!


    Daniel Sprague
    Wednesday, March 23, 2011 4:00 PM
  • Not a problem Daniel; glad I was able to help.
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, March 24, 2011 2:38 AM