开发者

Help with LINQ-SQL GroupBy

开发者 https://www.devze.com 2023-01-25 01:37 出处:网络
I\'m trying to convert this T-SQL to a LINQ-SQL query: -- top 3 pros for city select top 3 description, ispro, COUNT(*) as numberofvotes

I'm trying to convert this T-SQL to a LINQ-SQL query:

-- top 3 pros for city
select top 3 description, ispro, COUNT(*) as numberofvotes
from tblProCon
where IdPrimaryCity = @IdPrimaryCity
and IsPro = 1
group by IdPrimaryCity, IsPro, description

union

-- top 3 cons for city
select top 3 description, ispro, COUNT(*) as numberofvotes
from tblProCon
where IdPrimaryCity = @IdPrimaryCity
and IsPro = 0
group by IdPrimaryCity, IsPro, description

order by ispro, numberofvotes desc

Here's what i have so far:

// Construct base query
var query = (from p in db.tblProCons
             where p.IdPrimaryCity == idPrimaryCity
             group p by new { p.IdPrimaryCity, p.IsPro, p.De开发者_Python百科scription } into g
             select new { Description = g.Key, IsPro = g.Any(x => x.IsPro), NumberOfAgrees = g.Count() });

// Split queries based on pro/con, and apply TOP(3)
var pros = query.Where(x => x.IsPro).Take(3);
var cons = query.Where(x => !x.IsPro).Take(3);

result = pros
    .Union(cons) // Union pro/cons
    .OrderByDescending(x => x.IsPro) // Order #1 - Pro/Con
    .ThenByDescending(x => x.NumberOfAgrees) // Order #2 - Number of Agree's
    .Select(x => new ProCon // project into cut-down POCO
            {
                Description = x.Description,
                IsPro = x.IsPro
            }).ToList();

But she ain't working. :(

x.Description is complaining "Cannot convert source type {IdPrimaryCity:int, IsPro:bool, Description:string} to target type string".

All i want to end up with is a List<ProCon>, having the description (string), and flag indicating if it's a pro or con.

What am i doing wrong?


Nevermind, i got it, the "group" projection was all wrong.

Here's the working solution:

// Construct base query
var query = (from p in db.tblProCons
             where p.IdPrimaryCity == idPrimaryCity
             group p by new { p.IdPrimaryCity, p.IsPro, p.Description } into g
             select new { ProCon = g.Key, NumberOfAgrees = g.Count() });

// Split queries based on pro/con, and apply TOP(3)
var pros = query.Where(x => x.ProCon.IsPro).Take(3);
var cons = query.Where(x => !x.ProCon.IsPro).Take(3);

result = pros
    .Union(cons) // Union pro/cons
    .OrderByDescending(x => x.ProCon.IsPro) // Order #1 - Pro/Con
    .ThenByDescending(x => x.NumberOfAgrees) // Order #2 - Number of Agree's
    .Select(x => new ProCon // project into cut-down POCO
            {
                Description = x.ProCon.Description,
                IsPro = x.ProCon.IsPro
            }).ToList();
0

精彩评论

暂无评论...
验证码 换一张
取 消