Looking through the posts from other users it appears that llbl doesn't support unions. I am sure there is a good reason for this so I am looking for another way to solve my problem. In my last post I learned a great deal about aliases and custom filters, so I think I can write the llbl code to support the query, I just don't know how to write the query, or rather how to merge two queries I've already written into one... here is the problem:

PROBLEM: Perform a search for all users based upon their student first or last name who either a) are enrolled in a class that is taught by the default faculty member [faculty_id = 1], or b) has a membership to a role that is owned by the default faculty member [faculty_id = 1]. Here is the db schema for the included tables:

SCHEMA: user_log - user_log_id - user_name - password - email

student - student_id - first_name - last_name

course_period_enrollment_tank - course_period_enrollment_tank_id - student_id - course_period_tank_id

course_period_tank - course_period_tank_id - course_id - course_period_id

faculty - faculty_id - first_name - last_name

course_period_faculty_tank - course_period_faculty_tank_id - faculty_id - course_period_tank_id

faculty_user_log_tank - faculty_user_log_tank_id - faculty_id - user_log_id

faculty_membership_tank - faculty_membership_tank_id - faculty_user_log_tank_id - role_id - role_type_id - is_faculty - is_student

role - role_id - role_name

student_membership_tank - student_membership_tank_id - role_id

EXPLANATION: Faculty members are permitted to have both memberships to and ownership of generic roles which are further defined by permissions (which has been excluded from the schema). If the faculty member owns a role, the role_type_id = 2 and the ownership further defines if it is a role meant for students to have membership to or other faculty members to have membership to--as defined by is_faculty and is_student bit fields. Otherwise, if role_type_id = 1, that faculty member has a membership to a role which is owned by some other faculty member. Students can also have a membership to a faculty owned role, but their membership is tracked in student_membership_tank. It should be possible to query a search for all students who are either enrolled in a course that is taught by a faculty member or has a membership to a role owned by the same faculty member, such that a student that exists in either scenario would be returned, the same student who exists in both would be returned once (DISTINCT), and a student who exists in neither would be excluded.

QUERIES: The first query below gets students enrolled. The second query below gets students who have membership.

QUERY #1
SELECT DISTINCT user_log.user_log_id, user_log.email
FROM user_log
INNER JOIN student_user_log_tank sult1 ON user_log.user_log_id = sult1.user_log_id
INNER JOIN student s1 ON sult1.student_id = s1.student_id
INNER JOIN course_period_enrollment_tank ON s1.student_id = course_period_enrollment_tank.student_id
INNER JOIN course_period_tank ON course_period_enrollment_tank.course_period_tank_id = course_period_tank.course_period_tank_id
INNER JOIN course_period_faculty_tank cpft ON course_period_tank.course_period_tank_id = cpft.course_period_tank_id
AND cpft.faculty_id = 1
AND s1.first_name LIKE '%sally%'

QUERY #2
SELECT DISTINCT user_log.user_log_id, user_log.email
FROM user_log
INNER JOIN student_user_log_tank sult2 ON user_log.user_log_id = sult2.user_log_id
INNER JOIN student_membership_tank ON student_membership_tank.student_user_log_tank_id = sult2.student_user_log_tank_id
INNER JOIN role ON role.role_id = student_membership_tank.role_id
INNER JOIN faculty_membership_tank M2 ON role.role_id = M2.role_id
AND M2.role_type_id = 2
INNER JOIN faculty_user_log_tank F2 ON M2.faculty_user_log_tank_id = F2.faculty_user_log_tank_id
AND F2.faculty_id = 1
INNER JOIN student s2 ON sult2.student_id = s2.student_id
AND s2.first_name LIKE '%sally%'

I am not certain where or how to write the above two queries as one except that UNION in t-SQL does the job. Is there some other way I could write this that might allow for re-expressing in llbl?

Thanks again!

Otis wrote:

LLBLGen Pro doesnt support UNION queries, you have to call a fill twice.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5331&HighLight=1

There are seveal other hits on "union" on the forum Search.

Thank you for this link, but I have already read it along with others about union. Knowing that Union isn't supported, I am looking for any tips on an alternative way to construct my query. If this is not possible, I'll get two collections and weed out any duplicates.

Thank you.

Isz

You should not have to use two entity collections.

Set the DoNotPerformAddIfPresent flag = true on the entity collection object and call GetMutli (or FetchEntityCollection) twice with two different buckets. It won't clear the existing collection of objects unless you explicitly call the Clear method.

That "shoujld" automatically eliminate duplicates. Try it and let us know if it works for you.

I put my union query in a stored procedured, then just called it from LLBLGen. This will of course put the results in a datatable which was fine in my case cause all i wanted was a distinct array of string values

Oh cool... these are good ideas.. I'll try them and let you know.

I saw the intellisense that says performance is reduced when using DoNotPerformAddIfPresent = true;

Is the performance reduction the same for both statements below?

Statement #1

UserLogCollection userLogCollection = new UserLogCollection();
userLogCollection.DoNotPerformAddIfPresent = true;
... all my relations 
userLogCollection.GetMulti(null, relationsForFirstQuery);
userLogCollection.GetMulti(null, relationsForSecondQuery);

Statement #2

UserLogCollection userLogCollection = new UserLogCollection();
... all my relations 
userLogCollection.GetMulti(null, relationsForFirstQuery);
userLogCollection.DoNotPerformAddIfPresent = true;
userLogCollection.GetMulti(null, relationsForSecondQuery);

Maybe statement #2 would be faster due to the execution order y/n?

Nevermind... I think you have to do it like this...

                    userLogCollection1.GetMulti(null, cpft_studentRelations);
                    userLogCollection2.GetMulti(smt_studentFilters, smt_studentRelations);
                    userLogCollection2.DoNotPerformAddIfPresent = true;
                    foreach(UserLogEntity userLogEntity in userLogCollection1)
                        userLogCollection2.Add(userLogEntity);

Thanks!