I have a query like this:
SELECT count(1) as COUNT, t.[STATE], t.ASSIGNED from (
SELECT d.[STATE],
CASE
WHEN d.ASSIGNED_TO is NULL THEN 0
ELSE 1
END
as ASSIGNED
from [DOC] d
where d.YEAR=2010
) t
group by t.[STATE], t.ASSIGNED;
And here is my attempt to write this in Linq for nHibernate:
from d in docs
where d.Year == 2010
select new {
State = d.State,
Assigned = (
d.AssignedTo == null? 0:1
)
}
)
group t by new {t.State, t.Assigned} into g
select new {Count = g.Count(), State =
g.Key.State, Assigned = g.Key.Assigned}
but this ended up with nHibernate producing a SQL like:
SELECT cast(count(*) AS INT) AS col_0_0_
, spdoc0_.State AS col_1_0_
, spdoc0_.ASSIGNED_TO AS col_2_0_
, spdoc0_.State AS col_3_0_
, spdoc0_.ASSIGNED_TO AS col_4_0_
FROM
[DOC] spdoc0_
WHERE
spdoc0_.YEAR = 2010
GROUP BY
spdoc0_.State
, cast(CASE
WHEN spdoc0_.ASSIGNED_TO IS NULL THEN
@p1
ELSE
@p2
END AS INT);
This is on a SQL Server 2005 dialect.
My attempt to do the subquery seems to be totally ignored by the linq
provider. Why does nHibernate put the @p01,@p2 in the CASE statement and
what happened to my subquery!
Thanks,
Sam
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/rgWeiJttmxgJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
