tags:

views:

53

answers:

1

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?

+2  A: 

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
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 objectsrow 1: array of 710 same objectsand so on... How do I do to return only ONE object per row?
Fernando
@Fernando: By using the projection I've got at the end - select the key of the group, and the count.
Jon Skeet