Integration

I have many-to-many relationship between Employee and Project; so,
a Employee can have many Projects, and a Project can be owned by many Employees.
I have three tables in the database:

Employee(id int, name varchar(32)),
Project(id int, name varchar(32)), and
Employee_Project(employee_id int, project_id int), which is the join-table between Employee and Project.

Now, I want to find out for each employee, how many projects does the employee has.
The sql query that achieves what I want would look like this:

select e.id, count(*) as numProjects
from employee e, employee_project ep
where e.id = ep.employee_id
group by e.id

Just for information, currently I am using a named ReadAllQuery and I write my own sql in
the Workbench rather than using the ExpressionBuilder.

Now, my two questions are :
1. Since there is a "group by e.id" on the query, only e.id can appear in the select clause.
This prevent me from returning the full Employee pojo using ReadAllQuery.
I can change the query to a nested query like this

select e.eid, e.name, emp.cnt as numProjects
from employee e,
(select e_inner.id, count(*) as cnt
from employee e_inner, employee_project ep_inner
where e_inner.id = ep_inner.employee_id
group by e_inner.id) emp
where e.id = emp.id

but, I don't like the complication of having extra join because of the nested query. Is there a
better way of doing something like this?

2. The second question is what is the best way of returning the count(*) or the numProjects.
What I did right now is that I have a ReadAllQuery that returns a List<Employee>; then for
each returned Employee pojo, I call a method getNumProjects() to get the count(*) information.
I had an extra column "numProjects" in the Employee table and in the Employee descriptor, and
I set this attribute to be "ReadOnly" on the Workbench; (the value for this dummy "numProjects"
column in the database is always 0). So far this works ok. However, since the numProjects is
transient, I need to set the query to refreshIdentityMapResult() or otherwise the Employee object
in the cache could contain stale numProjects information. What I worry is that refreshIdentityMapResult()
will cause the query to always hit the database and beat the purpose of having a cache. Also, if
there are multiple concurrent queries to the database, I worry that there will be a race condition
of updating this transient "numProjects" attribute. What are the better way of returning this kind
of transient information such as count(*)? Can I have the query to return something like a tuple
containing the Employee pojo and an int for the count(*), rather than just a Employee pojo with the
transient int inside the pojo? Please advise.

I greatly appreciate any help.

Thanks,

Frans