DevForce Classic: SQL to OQL

Print Page | Close Window

SQL to OQL

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=978
Printed Date: 10-Jun-2026 at 7:09pm

Topic: SQL to OQL

Posted By: sagi6
Subject: SQL to OQL
Date Posted: 28-Oct-2008 at 4:46pm

Hi,
i'm lerning OQL, can You help me with this example?

for this scheme:

i have two queries in SQL:

1)
SELECT e.name FROM (Emp e INNER JOIN Dept d ON e.workIn=d.d#) WHERE d.boss=3;

2)
SELECT empWork.name FROM (emp JOIN Dept ON (emp.worksIn=Dept.d#)
JOIN emp empBoss ON (Dept.boss=empBoss.e#)) empWork WHERE empBoss.name="Smith";

and i want to write it in OQL. can You help me?
is it good place for that ask?

Replies:

Posted By: jeffdoolittle
Date Posted: 28-Oct-2008 at 7:43pm

Here are some examples. Keep in mind that with OQL the persistence manager returns entities, not single values.

        public IEnumerable<Employee> GetEmployeesManagedBy(Employee manager) {
            var query = new EntityQuery(typeof(Employee));
            query.AddSubquery(EntityRelations.Department_Employee_Works_In.ToParent);
            query.AddClause(Department.ManagerEmployeeIdEntityColumn, EntityQueryOp.EQ, manager.EmployeeId);
            return persistenceManager.GetEntities<Employee>(query);
        }

        public IEnumerable<Employee> GetEmployeesManagedBy(long managerId) {
            var query = new EntityQuery(typeof(Employee));
            query.AddSubquery(EntityRelations.Department_Employee_Works_In.ToParent);
            query.AddClause(Department.ManagerEmployeeIdEntityColumn, EntityQueryOp.EQ, managerId);
            return persistenceManager.GetEntities<Employee>(query);
        }

        public IEnumerable<Employee> GetEmployeesManagedBy(string managerName) {
            var names = managerName.Split(' ');
            var query = new EntityQuery(typeof(Employee));
            query.AddSubquery(EntityRelations.Department_Employee_Works_In.ToParent);
            query.AddSubquery(EntityRelations.Employee_Manager_For_Department.ToChild);
            query.AddClause(Employee.FirstNameEntityColumn, EntityQueryOp.EQ, names[0]);
            query.AddClause(Employee.LastNameEntityColumn, EntityQueryOp.EQ, names[1]);
            return persistenceManager.GetEntities<Employee>(query);
        }

Posted By: sagi6
Date Posted: 29-Oct-2008 at 6:58am

can you post scheme for this queries? :)

Posted By: davidklitzke
Date Posted: 29-Oct-2008 at 9:17am

Notice that DevForce returns a collection of Employees and not the result of a join. If you want your result to contain columns from related tables, you should review the material on Dynamic Entities. However, in most circumstances, we prefer that you avoid the use of Dynamic Entities as they are more complicated to use and do not work well in environments which require modification, deletion, or insertion.

Print Page | Close Window