EntitySQL - left join problem

EntitySQL - left join problem

Postby bolek75 » Wed 04 Aug 2010 11:00

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
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » Wed 04 Aug 2010 16:23

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).
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle