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
EntitySQL - left join problem
I have created the following tables:
The query I was using:
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).
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));
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()
}
);
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).