CODEWITHSUNDEEP
c#vb.netlinqlinq-to-sql
Tset
Tset

Reputation: 11

LINQ to SQL count table with relationship

C# or VB.NET is fine.

I'd like to count number of people for each country from the following tables with LINQ to SQL

I have two tables, one is Countries and other is People. It's one to many relatoionship from Countries to People.

Countries table column:CountryId,CountryName
People:PeopleId,FullName,CountryId (a primary key from Country table and allowed null)

I want to count how many people each country has and get something like the following result that shows only the country that has people. Because there are some countries that have no people in the People table.

and so on..

Thank you.

Upvotes: 1

Views: 2120

Answers (2)

casper123
casper123

Reputation: 1766

Well its kind of tricky.. you have to use relationship using LINQ, try using this query

    var count = from pl in dc.People join ci in dc.Countries on pl.CountryId equals ci.CountryId groupby ci.CountryId select PeopleId.count();

there are many other ways to write LINQ queries but its the simplest way.. For more on LINQ to SQL, you can check out my blog : LinqtoSQL

Upvotes: 0

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

(from c in Countries
select new { c.CountryName, Count = c.People.Count()}).Where(r => r.Count > 0)

or

from c in Countries.Where(r => r.People.Any())
select new { c.CountryName, Count = c.People.Count()}

Upvotes: 2

Related Questions