Select in Select BUG???

Select in Select BUG???

Postby bugmenot2 » Mon 08 Feb 2016 12:16

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...
bugmenot2
 
Posts: 9
Joined: Tue 19 Mar 2013 12:20

Re: Select in Select BUG???

Postby AlexP » Mon 08 Feb 2016 13:56

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.
AlexP
Devart Team
 
Posts: 5525
Joined: Tue 10 Aug 2010 11:35

Re: Select in Select BUG???

Postby bugmenot2 » Mon 08 Feb 2016 14:21

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))
bugmenot2
 
Posts: 9
Joined: Tue 19 Mar 2013 12:20

Re: Select in Select BUG???

Postby AlexP » Tue 09 Feb 2016 08:37

As I wrote earlier, nested queries are not yet supported in Linq.
AlexP
Devart Team
 
Posts: 5525
Joined: Tue 10 Aug 2010 11:35


Return to EntityDAC