Select in Select BUG???
Posted: 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...
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...