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();
精彩评论