EntitySQL: Ordering a collection in the Select

Question

Imagine there's an entitySet FirstEntitySet that has a reference 1..N to another (AnotherEntities). And you want to get the following ESQL:

SELECT



 it.AnotherEntities, it.Name
FROM



 ctx.FirstEntitySet as



 it

in other words you want some fields from the FirstEntitySet and a field that would be a collection of objects.

The question is: how to make that collection ordered? What to write in ORDER BY ?
this question is answered, however another one raised lower in the thread...

Answer 1

Hi,

You should be able to write it as

SELECT (SELECT VALUE A

        FROM it.AnotherEntities AS A

        ORDER BY A.OrderingProperty), it.Name

FROM ctx.FirstEntitySet as it

 

I hope that helps.

Thanks,

Kati

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

select value ! i thought i was missing something obvious :)

Answer 3

well yeah, the output isn't actually ordered. looks like a bug, maybe in EF, maybe in data provider's methods to parse out native sql...
----------------------------------------------------------------
the reason it's more complicated than one related set of entities. it's two! Thus there's a Union All in native sql

adding the ordering adds this to native SQL:

,
NULL AS `C10`

(in the first part of union all)

,
`Extent4`.`R_ID` AS `R_ID1`

(in the second part of union all)

,
`C10` ASC 

(in the order by part of union all)

however! there's nothing in the select part of union all actually there is a C10, which is a completely different field ! just adding a

,
       UnionAll1.C10           AS C16

(in the select part of union all)

and changing the ordering part to

,

`C16` ASC 

would solve the problem.

so that's a bug. question is where and if there could be a workaround?

Answer 4

Hello Duke,

Your description of the issue has become difficult to follow. Would it be possible for you to provide the following:

1. Full eSQL version of the actual query
2. Whole native SQL translation?
3. Also, what data provider are you using?

Not sure this will be sufficient information, but it would help us start.

Thanks,
Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 5

1)

SELECT 
(SELECT value a FROM c1.founders as a
 order by a.FOUNDER_ID) as data_old,
(SELECT value b FROM c1.na.na_founders as b
 order by b.FOUNDER_ID) as data_new
FROM Entities.mainSet as c1
WHERE c1.main_id = @main_id

2)

SELECT
`UnionAll1`.`MAIN_ID` AS `C1`, 
`UnionAll1`.`MAIN_ID1` AS `C2`, 
`UnionAll1`.`C2` AS `C3`, 
`UnionAll1`.`C1` AS `C4`, 
`UnionAll1`.`C3` AS `C5`, 
`UnionAll1`.`FOUNDER_ID` AS `C6`, 
`UnionAll1`.`C4` AS `C7`, 
`UnionAll1`.`FOUNDER_NAME` AS `C8`, 
`UnionAll1`.`MAIN_ID2` AS `C9`, 
`UnionAll1`.`FOUNDER_ID1` AS `C10`, 
`UnionAll1`.`C5` AS `C11`, 
`UnionAll1`.`C6` AS `C12`, 
`UnionAll1`.`C7` AS `C13`, 
`UnionAll1`.`C8` AS `C14`, 
`UnionAll1`.`C9` AS `C15`
FROM ((SELECT
CASE WHEN (`Extent2`.`FOUNDER_ID` IS  NULL) THEN (NULL)  ELSE (1) END AS `C1`, 
`Extent1`.`MAIN_ID`, 
`Extent1`.`MAIN_ID` AS `MAIN_ID1`, 
1 AS `C2`, 
CASE WHEN (`Extent2`.`FOUNDER_ID` IS  NULL) THEN (NULL)  ELSE (1) END AS `C3`, 
`Extent2`.`FOUNDER_ID`, 
CASE WHEN (`Extent2`.`FOUNDER_ID` IS  NULL) THEN (NULL)  ELSE (1) END AS `C4`, 
`Extent2`.`FOUNDER_NAME`, 
`Extent2`.`MAIN_ID` AS `MAIN_ID2`, 
`Extent2`.`FOUNDER_ID` AS `FOUNDER_ID1`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`
FROM `main` AS `Extent1` LEFT OUTER JOIN `founders` AS `Extent2` ON `Extent1`.`MAIN_ID` = `Extent2`.`MAIN_ID`
 WHERE `Extent1`.`MAIN_ID` = 11111) UNION ALL (SELECT
2 AS `C1`, 
`Extent3`.`MAIN_ID`, 
`Extent3`.`MAIN_ID` AS `MAIN_ID1`, 
1 AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
1 AS `C9`, 
`Extent4`.`FOUNDER_ID`, 
1 AS `C10`, 
`Extent4`.`FOUNDER_NAME`, 
`Extent4`.`MAIN_ID` AS `MAIN_ID2`, 
`Extent4`.`FOUNDER_ID` AS `FOUNDER_ID1`
FROM `main` AS `Extent3` INNER JOIN (`na_founders` AS `Extent4` INNER JOIN (`na` AS `Extent5` LEFT OUTER JOIN `main` AS `Extent6` ON `Extent5`.`MAIN_ID` = `Extent6`.`MAIN_ID`) ON `Extent4`.`MAIN_ID` = `Extent5`.`MAIN_ID`) ON `Extent3`.`MAIN_ID` = `Extent5`.`MAIN_ID`
 WHERE `Extent3`.`MAIN_ID` = 11111)) AS `UnionAll1`
 ORDER BY 
`MAIN_ID` ASC, 
`MAIN_ID1` ASC, 
`C1` ASC, 
`FOUNDER_ID1` ASC, 
`C10` ASC 

3) the only official mySQL provider that works with EF

4) here's the difference that would make it work correctly that i was trying to ambigiously describe in previous message: 1 2

Answer 6

Hi,

This seems to be a provider issue. I tried a similar query over SQL Server using our provider and it produces the correct results. Also the output Command Tree based on which a provider generates the SQL looks correct too.

And yes, I agree with your observation.   Another option is to prefix to order keys with `UnionAll1` as we do in our SQL Generation in SQL Client.

 

Thanks,
Kati

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 7

I see. Thank You.