Soci blog

Melyik a szimpatikusabb?

select bucid, score, countMatchedEcid, ROW_NUMBER()
OVER(ORDER BY score DESC) AS 'rank'
from (
select
b.ucid as bucid,
a.ucid as aucid,
sum(w.weight) as score,
count(*) as countMatchedEcid
from EcidNameValueSummary a,
genecidparts b,
weights w
where a.id=b.id
and a.value=b.value
and a.ucid!=b.ucid
and w.id=a.id
and w.allowed=1
and w.limit>=a.countEcids
and a.ucid=@ucid
group by b.ucid, a.ucid
) as hits

var matchQuery = from t in
    (from a in c.EcidNameValueSummaries
    join b in c.genecidparts
    on a.id equals b.id
    join w in c.Weights
    on a.id equals w.id
    where w.allowed == 1
    && w.limit >= a.countEcids
    && a.value == b.value
    && a.ucid != b.ucid
    && a.ucid == ucid
    select new
    {
        bucid = b.ucid,
        aucid = a.ucid,
        score = w.weight1
    })
group t by t.bucid into g
orderby g.Sum(e => e.score) descending
select new RankEntity
{
    Ucid = g.Key,
    Score = g.Sum(e => e.score) ?? 0,
    CountMatched = g.Count(),
    Rank = 0
};

BindingListCollection<RankEntity> ranks =
new BindingListCollection<RankEntity>();
int i = 1;
//It would be cool to do this with linq, but...
foreach (RankEntity e in matchQuery)
{
    e.Rank = i++;
    ranks.Add(e);
}
return ranks;

És emellett, a ROW_NUMBER-t át tudná nekem valaki fordítani LINQ-ra?

This entry was posted on Monday, February 11th, 2008 at 11:13 pm and is filed under .NET, ADO.NET, Adatbázisok, C#, Linq, SQL Server, SQL Server 2005, Szakmai élet. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.