LINQ for SQL Aggregates

[Shimon Sim]

I am having hard time figuring out how to reproduce following SQL in LINQ

SELECT State.Name, City.Name, COUNT(Person.PersonId)
FROM State INNER JOIN City ON State.StateId=City.StateId
INNER JOIN Person Person.CityId=City.CityId
GROUP BY State.Name, City.Name

I have few quires similar to this on with HAVING, SUM and so on.

Can some one help me with LINQ expression for this sample?
Thanks,
Shimon.

 

[Jon Skeet [C# MVP]]

I am having hard time figuring out how to reproduce following SQL in LINQ

SELECT State.Name, City.Name, COUNT(Person.PersonId)
FROM State INNER JOIN City ON State.StateId=City.StateId
INNER JOIN Person Person.CityId=City.CityId
GROUP BY State.Name, City.Name

I have few quires similar to this on with HAVING, SUM and so on.

Can some one help me with LINQ expression for this sample?

Completely untested, but:

from person in db.Person
group person by
new { City=person.City.Name, State=person.City.State.Name }
into people
select new { City=people.Key.City, State=people.Key.State,
Count=people.Count() };

Can you let me know if that works? I'd at least be interested to know
what SQL comes out of it

(At some point I'll create a simple database to test this kind of
thing, but I don't have time right now...)

There are other ways of doing it, of course...

 

[Michel Walsh]

Here, with a join, a where clause and a should be having clause:

class Program
{
static void Main(string[] args)
{
static void Main(string[] args)
{
NorthwindExtendedDataContext db
= new NorthwindExtendedDataContext();

var custs = from s in db.Suppliers
join c in db.Customers
on s.City equals c.City
where s.SupplierID > 10000
group s by s.Country into g
where g.Count() <2
select new
{
Country= g.Key, howMany =g.Count()
};

db.Log = Console.Out;
foreach (var cust in custs)
{
Console.WriteLine("{0}- {1}", cust.Country, cust.howMany);
}
Console.ReadLine();
}
}


The generated SQL translates the HAVING clause into a
where-over-a-sub-query:



--------------------------------
SELECT [t2].[Country], [t2].[value2] AS [howMany]
FROM (
SELECT COUNT(*) AS [value1], COUNT(*) AS [value2], [t0].[Country]
FROM [dbo].[Suppliers] AS [t0]
INNER JOIN [dbo].[Customers] AS [t1] ON [t0].[City]=[t1].[City]
WHERE [t0].[SupplierID] > @p0
GROUP BY [t0].[Country]
)
WHERE [t2].[value] < @p1
-----------------------------------




There is maybe an even better way to write the JOIN clause than the one I
used.

The NorthwindExtended database is available from Joseph C. Rattz, from his
"Pro LINQ Language Integrated Query in C# 2008" book, which, the database,
you can download from the APress site.

Vanderghast, Access MVP

 

[Shimon Sim]

I tried it and it is on target.
Thanks

Completely untested, but:

from person in db.Person
group person by
new { City=person.City.Name, State=person.City.State.Name }
into people
select new { City=people.Key.City, State=people.Key.State,
Count=people.Count() };

Can you let me know if that works? I'd at least be interested to know
what SQL comes out of it

(At some point I'll create a simple database to test this kind of
thing, but I don't have time right now...)

There are other ways of doing it, of course...

 

[Shimon Sim]

Thanks for detailed sample.
It helped me a lot.

Here, with a join, a where clause and a should be having clause:

class Program
{
static void Main(string[] args)
{
static void Main(string[] args)
{
NorthwindExtendedDataContext db
= new NorthwindExtendedDataContext();

var custs = from s in db.Suppliers
join c in db.Customers
on s.City equals c.City
where s.SupplierID > 10000
group s by s.Country into g
where g.Count() <2
select new
{
Country= g.Key, howMany =g.Count()
};

db.Log = Console.Out;
foreach (var cust in custs)
{
Console.WriteLine("{0}- {1}", cust.Country, cust.howMany);
}
Console.ReadLine();
}
}


The generated SQL translates the HAVING clause into a
where-over-a-sub-query:



--------------------------------
SELECT [t2].[Country], [t2].[value2] AS [howMany]
FROM (
SELECT COUNT(*) AS [value1], COUNT(*) AS [value2], [t0].[Country]
FROM [dbo].[Suppliers] AS [t0]
INNER JOIN [dbo].[Customers] AS [t1] ON [t0].[City]=[t1].[City]
WHERE [t0].[SupplierID] > @p0
GROUP BY [t0].[Country]
)
WHERE [t2].[value] < @p1
-----------------------------------




There is maybe an even better way to write the JOIN clause than the one I
used.

The NorthwindExtended database is available from Joseph C. Rattz, from his
"Pro LINQ Language Integrated Query in C# 2008" book, which, the database,
you can download from the APress site.

Vanderghast, Access MVP

I am having hard time figuring out how to reproduce following SQL in LINQ

SELECT State.Name, City.Name, COUNT(Person.PersonId)
FROM State INNER JOIN City ON State.StateId=City.StateId
INNER JOIN Person Person.CityId=City.CityId
GROUP BY State.Name, City.Name

I have few quires similar to this on with HAVING, SUM and so on.

Can some one help me with LINQ expression for this sample?
Thanks,
Shimon.