EntitySQL - left join problem

[bolek75]

I have this hypothetical scenario. An employee can be related with one or more departments. But in case of a relation only one department is his main department.
Three entities are involved 'Employee', 'Deparment' and 'EmplDepartment'.
At this point I have to use EntitySQL instead of LINQ To Entities, because my customers have different EntitySQL-statements which can be imported in my app.
I have define the following statement, which was translated to TSQL but Oracle throws an error 'invalid identifier' relating to the EmployeeID in a sub-select.

My EntitySQL:

string sql = "SELECT VALUE E FROM EmplEntities.Employees AS E WHERE E.EmployeeID=1";
var query = MyDbContext.CreateQuery(sql, new ObjectParameter[0]).
Select( empl => new {
EmplID = empl.EmployeeID,
EmplName = empl.EmployeeName,
MainDept = empl.EmployeeDepartments.Where(ed1 => ed1.IsMain ==true).Select(ed2 => ed.DepName).FirstOrDefault()
});
this.dataGridView1.DataSource = query.ToList();


Regards
bolek75

[AndreyR]

I have created the following tables:

Code: Select all

CREATE TABLE SCOTT.T1 (
  ID NUMBER,
  FIELD NUMBER,
  CONSTRAINT SYS_C0095628 PRIMARY KEY (ID));

CREATE TABLE SCOTT.T2 (
  ID NUMBER,
  FIELD NUMBER,
  CONSTRAINT SYS_C0095629 PRIMARY KEY (ID));

CREATE TABLE SCOTT.T1T2 (
  ID1 NUMBER,
  ID2 NUMBER,
  CONSTRAINT SYS_C0095630 PRIMARY KEY (ID1, ID2),
  CONSTRAINT SYS_C0095631 FOREIGN KEY (ID1)
    REFERENCES SCOTT.T1(ID),
  CONSTRAINT SYS_C0095632 FOREIGN KEY (ID2)
    REFERENCES SCOTT.T2(ID));

The query I was using:

Code: Select all

        var q = db.CreateQuery("select value t from t1 as t where t.id = 1").
          Select(t1 => new
          {
            T1 = t1.Id,
            T1Field = t1.Field,
            MainT2 = t1.T2.Where(t2 => t2.Id == 184).Select(t2 => t2.Field).FirstOrDefault()
          }
          );

I retreived the expected results.
Could you please modify my sample so that the problem can be reproduced. As an option, you can send me your own sample (support * devart * com).