sexta-feira, 12 de agosto de 2016

Distance Calc in SQLServer + Linq GroupBy-innerJoin

create table #mensagem
(
 rownumber int identity(1,1),
  men_latitude decimal(12,9),
  men_longitude decimal(12,9),
  men_data_expiracao datetime,
  distance numeric
)

update a
 set distance = geography::Point(isnull(a.men_latitude,0), isnull(a.men_longitude,0),    4326).STDistance(geography::Point( isnull(b.men_latitude,0), isnull(b.men_longitude,0), 4326))
from #mensagem a
left outer join #mensagem b
on a.rownumber = b.rownumber + 1


var tsUnique = (from x in myList
group x by x.id into g
let f = g.FirstOrDefault()
where f != null
orderby f.id
select new DemoPark() { id = f.id, dataStatus = g.Max(c =>c.dataStatus) }
).ToList() ;


var result = from x in myList
join y in tsUnique on new { x.id, x.dataStatus } equals new { y.id, y.dataStatus }
orderby x.id
select x;


foreach (DemoPark park in result)
{
strFinal.Append(virgula + "{ \"id\": \"" + park.id + "\", \"nome\": \"" + park.nome + "\", \"resultado\": \"" + park.resultado + "\", \"data\": \"" + park.dataStatus + "\"}");
virgula = ",";

}

return strFinal.ToString();
Postar um comentário