0

How do I write this query in linq VB.NET?

select top 15 count(1), A.Latitude, A.Longitude
from Bairro A
inner join Empresa B on B.BairroID = A.BairroID
where A.CidadeID = 4810
group by A.Latitude, A.Longitude
order by COUNT(1) desc

I reached this code:

Dim TopBairros = (From A In Bairros _
                  Join B In New BO.Empresa().Select On B.BairroID Equals A.BairroID Group A By A.Latitude, A.Longitude Into g = Group _
                  Select g Distinct _
                  Order By g.Count Descending).Take(15)

Each row has a array collection containing repeatly the same objects with the count number. Example:

row 0: array of 874 same objects row 1: array of 710 same objects

and so on... How do I do to return only ONE object per row?

ekad
  • 14,436
  • 26
  • 44
  • 46
Fernando
  • 2,123
  • 4
  • 17
  • 21

1 Answers1

3

Try this:

var query = from a in context.Bairro
            where a.CidadeID == 4810
            join b in context.Empresa on a.BairroID equals b.BairroID
            group a by new { a.Latitude, a.Longitude } into grouped
            orderby grouped.Count() descending
            select new { grouped.Key.Latitude,
                         grouped.Key.Longitude,
                         Count = grouped.Count() };
var top15 = query.Take(15);
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • I'm using VB.NET... I reached this code: Dim TopBairros = (From A In Bairros _ Join B In New BO.Empresa().Select On B.BairroID Equals A.BairroID Group A By A.Latitude, A.Longitude Into g = Group _ Select g Distinct _ Order By g.Count Descending).Take(15) Each row has a array collection containing repeatly the same objects with the count number. Example: row 0: array of 874 same objects row 1: array of 710 same objects and so on... How do I do to return only ONE object per row? – Fernando Dec 30 '09 at 23:51
  • @Fernando: By using the projection I've got at the end - select the key of the group, and the count. – Jon Skeet Dec 31 '09 at 00:49