Page 1 of 1

Select in Select BUG???

Posted: Mon 08 Feb 2016 12:16
by bugmenot2
Hello Devart team ...
I'm new in EntityDAC...

1. Can you please explain me how to build Linq for SQL which containing other SQL in JOIN or in any other part ???

For example, I want receive EMP with SAL > 2000 for each DEPT:

SELECT
DEPT.DNAME,
EMP.ENAME
FROM
DEPT
LEFT JOIN EMP ON (EMP.EMPNO = (SELECT E1.EMPNO FROM EMP AS E1 WHERE ((E1.DEPTNO = DEPT.DEPTNO) AND (E1.SAL > 2000)) LIMIT 1))

MySQL + database same as in your Demo.
I'm trying following Linq:
E := Context.Emp;
E1:= Context.Emp;
D := Context.Dept;

Query := Linq.From(D)
.LeftJoin(E).On(E.Empno = Linq.From(E1).Where((E1.Deptno = D.Deptno) and (E1.Sal > 1000)).Select(E1.Empno).Take(1).Expression)
.Where(E.EmpNo > 0)
.Select([E.Ename]);

Result := Context.GetEntities(Query);

This code raise error:
[TSQLMemberResolver] Cannot resolve metadata member Demo.Dept
So the resolver don't understand (E1.Deptno = D.Deptno) and when I remove (E1.Deptno = D.Deptno) condition I have next error: #21000Operand should contain 1 column(s). That explained in my question 2 and make unusable Linq...Expression in other Linq.

-----
2. Using your Demo, I trying code like this:

E := Context.Emp;
Query := Linq.From(E)
.Select([E.Ename]);

Result := Context.GetEntities(Query);
and SQL for this code:
SELECT t1.ENAME Ename
FROM EMP t1
All is fine BUT when I want execute code:

E := Context.Emp;
Query := Linq.From(E)
.Where(E.EmpNo > 0)
.Select([E.Ename]);

Result := Context.GetEntities(Query);
So, I just add ANY Where and have SQL code like this:
SELECT t1.ENAME Ename, t1.EMPNO Empno, t1.JOB Job, t1.MGR Mgr, t1.HIREDATE Hiredate, t1.SAL Sal, t1.COMM Comm, t1.DEPTNO Deptno
FROM EMP t1
WHERE t1.EMPNO > :p0

Why I have all fields??? as I ask only Select([E.Ename])???

Thanks
Wait for your answer...

Re: Select in Select BUG???

Posted: Mon 08 Feb 2016 13:56
by AlexP
Currently, nested queries are not supported. We will try to add this functionality in one of the next versions..

We couldn't reproduce the case. The query returns only the fields specified in the Select statement. You can check this in the Select Complex demo.

Re: Select in Select BUG???

Posted: Mon 08 Feb 2016 14:21
by bugmenot2
Thanks for your answer ...

Any workarounds?
How I can use your EntityDAC with this query:???
SELECT
DEPT.DNAME,
EMP.ENAME
FROM
DEPT
LEFT JOIN EMP ON (EMP.EMPNO = (SELECT E1.EMPNO FROM EMP AS E1 WHERE ((E1.DEPTNO = DEPT.DEPTNO) AND (E1.SAL > 2000)) LIMIT 1))

Re: Select in Select BUG???

Posted: Tue 09 Feb 2016 08:37
by AlexP
As I wrote earlier, nested queries are not yet supported in Linq.