Grouped Subquery with Linq

[sam]

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

[sam]

Sorry I missed a line when I pasted my Linq. It should have read:

                from t in (

[Gunnar Liljas]

There is no subquery, just an inline projection, and there's no reason for NH  to use any subquery to make it work. The parameters @p1 and @p2 are put there because that's just how NH (and most other Linq -> SQL providers) deal with literal values in a query. It's a good thing.

That said, the query should use the case switch in the final projection, so there's something not really jiving here.

/G

2011/8/12 sam <sam...@gmail.com>

--
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 nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[sam]

Well I was trying to group by counts where there is an assigned user and where there is not, and hence ended up with the construct. So basically this type of query is not possible with linq for nHibernate; but is it the case that no subqueries are allowed in linq?

Thanks,

[sam]

When I use the same Linq and pass it to Linq to SQL, the generated SQL is:

SELECT COUNT(*) AS [Count], [t1].[STATE] AS [State], [t1].[value] AS [Assigned]

FROM (

    SELECT [t0].[STATE], 

        (CASE 

            WHEN [t0].[ASSIGNED_TO] IS NULL THEN @p0

            ELSE @p1

         END) AS [value], [t0].[PROGRAM_YEAR]

    FROM [test_schema].[SP_DOC] AS [t0]

    ) AS [t1]

WHERE [t1].[PROGRAM_YEAR] = @p2

GROUP BY [t1].[STATE], [t1].[value]

which is very close to what my intention is and executes and gives the result that I wanted. So if this type of query cannot be parsed through nHibernate, is it possible to still use this Linq as "native query" in nHibernate just like other native queries? 

[sam]

Also, Entity Framework4 also emits the same query as linq to sql. I'd be interested to learn what limitations on nHibernate prevents it from generating the SQL in line with EF4? 

[sam]

I've looked around for a bit but I have not found a full list of limitations of the current Linq provider for nHibernate 3.2. Most of the material found on the net seems to be applicable to <3.x. How much of an effort would it be to implement the missing gaps to get it on par with EF4 linq provider. Does the current nHibernate linq implementation uses libraries like http://relinq.codeplex.com/?  relinq is supposed to make the linq provider easier to write, but not having much technical background on that topic, I cannot say; however not having on-par linq support makes nHibernate really a hard sell against the current version of EF4.2. 

[Patrick Earl]

HQL does not have subquery in from clause support. I've been consider
how to add this, but I think it's going to be a substantial effort.
It's coming, but unless somebody can provide more development time,
it's going to take while to implement.

Patrick Earl

> --
> 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/-/f9CruyA7PbcJ.