CODEWITHSUNDEEP
c#linq-to-sql
Ashok kumar
Ashok kumar

Reputation: 1611

LINQ Group by Clause with Where clause

I am learning LINQ. Can anybody please help me to solve the following problem!

From Northwind's Customers and Orders tables:

I want to "Find all Customers who have done more than 20 Orders"

The Query in SQL Server for the same is:

SELECT * FROM Customers WHERE CustomerID in (SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(CustomerID) > 20)

But, I could not able to write the equivalent in LINQ.

I have tried like below:

var customers = ctx.Customers.Where(o => o.CustomerID.Count() > 20).GroupBy(o => o.CustomerID);

I am geting the following error in Event Log:

Exception information: Exception type: HttpCompileException Exception message: e:\Ashok\WEB\VS2012\CSHWEB\CSHWEB\LINQ01.aspx(17): error CS0103: The name 'Orders' does not exist in the current context

The reason is that the dependent Orders table might not be loaded yet. But, I am not understanding how to make it work!

Upvotes: 1

Views: 1559

Answers (5)

mehrandvd
mehrandvd

Reputation: 9116

In order to write to such query, you need to use a database relation (Navigation Property). This relation should be declared in your context as Customer.Orders. After then, you can use Linq to query the context. Check this out for an example which describes what you need.http://msdn.microsoft.com/en-us/library/bb425822.aspx

However you can declare context like:

public partial class Northwind : DataContext
{
   public Table<Customer> Customers;
   public Table<Order> Orders;
   public Northwind(string connection): base(connection) {}
}

and you should define your Customer class like:

[Table(Name="Customers")]
public class Customer
{
   [Column(Id=true)]
   public string CustomerID;
   ...
   private EntitySet<Order> _Orders;
   [Association(Storage="_Orders", OtherKey="CustomerID")]
   public EntitySet<Order> Orders {
      get { return this._Orders; }
      set { this._Orders.Assign(value); }
   }
}

then you can query it like the other answers said.

Upvotes: 1

Grundy
Grundy

Reputation: 13381

try use join..into clause something like this

var customers = from c in ctx.Customers
                join o in ctx.Orders on c.CustomerID equals o.CustomerID into orders
                where orders.Count()>20
                select c;

Upvotes: 1

Anatolii Gabuza
Anatolii Gabuza

Reputation: 6260

Your relations between objects should be properly configured. If you're using Entity Framework just mark Orders property in Customers entity as virtual this will allow lazy loading. Then your query will be much simpler. Otherwise you can still start from Orders instead of Customers:

var customers = ctx.Orders.GroupBy(c => c.CustomerID)
                             .Where(x => x.Count() > 20)
                             .SelectMany(g => g.Select(o => o.Customer));

And for customers (with virtual Orders collection) pretty straightforward:

var customers = ctx.Customers.Where(c => c.Orders.Count() > 20);

Upvotes: 1

James
James

Reputation: 2201

You could do something like this:

var customers = from c in Customers where c.Orders.Count() > 20 select c;
customers.GroupBy(o => o.CustomerID);

Upvotes: 1

Ben
Ben

Reputation: 1963

If customers and orders are related (1:MANY) and you have set up your object model to reflect this you could do:

var customers = ctx.Customers.Where(c => c.Orders.Count() > 20);

If not then:

var customerIds = ctx.Orders.GroupBy(x => x.CustomerID)
.Where(x => x.Count() > 20);
.SelectMany(x => x.CustomerID);

var customers = ctx.Customers.Where(x => customerIds.Contains(x.CustomerID));

Upvotes: 1

Related Questions