LLBLGen Pro. Version: 2.6 Final (May 15th, 2009)

Hi,

I have a predicate that tries to reference another table during the prefetch and it doesn't seem to work.

This is what I want:

SELECT Supervisor.Name
FROM Employees 
INNER JOIN UserBase ON UserBase.UserId = Employees.UserId
LEFT JOIN Departments ON Departments.DepartmentId = Employees.DepartmentId
LEFT JOIN Supervisors ON Departments.DepartmentId = Supervisors.DepartmentId
WHERE Supervisors.Effective_Date IN (SELECT MAX(Supervisors.Effective_Date)
                    FROM Supervisors Sup2
                    WHERE Supervisors.DepartmentId = Sup2.DepartmentId
                    AND Sup2.Effective_Date <= UserBase.Created_Date)
    

The problem is when the subquery cannot reference related main query columns in prefetch. When Supervisors sub query is ran for prefetch it is ran as an individual query with no referrence to UserBase. It's working fine for relations and predicates because there are joins there, but I want to get the data populated into my return entity.

I have written something like:

IPredicateExpression subQuery = new PredicateExpression();

subQuery.Add(SupervisorField.DepartmentId.SetObjectAlias("e") == SupervisorField.DepartmentId);
subQuery.AddWithAnd(SupervisorFields.EffectiveDate.SetObjectAlias("e") <= UserBaseFields.CreatedDate);

IEntityField2 effectiveDateField1 = SupervisorFields.EffectiveDate;
IEntityField2 effectiveDateField2 = SupervisorFields.EffectiveDate;
effectiveDateField2.ObjectAlias = "e";
effectiveDateField2.AggregateFunctionToApply = AggregateFunction.Max;

Predicate predicate = new FieldCompareSetPredicate(effectiveDateField1, null, effectiveDateField2, null, SetOperator.In, subQuery);


IRelationPredicateBucket relationBucket;
relationBucket = new RelationPredicateBucket();
relationBucket.Relations.Add(EmployeeEntity.Relations.DepartmentEntityUsingDepartmentId, JoinHint.Left);
relationBucket.Relations.Add(DepartmentEntity.Relations.SupervisorEntityUsingDepartmentId, JoinHint.Left);

IPrefetchPath2 prefetch;
prefetch.PrefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
prefetch.PrefetchPath.Add(EmployeeEntity.PrefetchPathDepartments).SubPath.Add(DepartmentEntity.PrefetchPathSupervisors, 1, new PredicateExpression(predicate), relationBucket);

Thought the prefetch would use the relation but it didn't. I think I'm missing something really simple but can't figure it out.

Thanks!

Just to clarify are you trying to fetch

Employees (filtered by effective date and department id) | |------------>Related Departments | |------------>Related Supervisors

If so the predicate which filters on the effective date and department id applies to the query which fetches the employees, not the related supervisors themselves. At the moment in the line

prefetch.PrefetchPath.Add(EmployeeEntity.PrefetchPathDepartments).SubPath.Add(DepartmentEntity.PrefetchPathSupervisors, 1, new PredicateExpression(predicate), relationBucket);

you are adding it to the prefetch for the supervisors.

To simply this, think about how you would fetch just the employees, filtered by the relevent items, and get this working first. The prefetch paths can then be added afterwards.

Matt

I'm actually trying to fetch

Employees (All employees) | |------------>Related Departments | |------------>Related Supervisor (Filtered by Employees creation date and Department ID)

This way I can find the all the supervisors of the employee at the time he joined the company. I put the predicate in Supervisors so there will only be one entry returned per department (The current acting supervisor at the time).

Ok, in that case you code looks essentially correct. What SQL do you actually get generated ?

Using the Visual Studio debugger and setting ORMPersistenceExecution to verbose I'm just getting back a bunch of regular single table prefetch path queries for the non-predicate prefetches. When it hits the Supervisor prefetch with the predicate it throws an error saying

"The multi-part identifier "database1.dbo.UserBase.Create_Date" could not be bound."

and no SQL is generated. Is there another way to see a more whole SQL output?

SQL Profiler is the best way to see the SQL actually hitting the database.

From your error - it looks like you need to add another relation to your bucket (the relation between Department and UserBase)

The way to think about this one is that although you initially listed one SQL statement, what you are actually generating with your LLBLGen query is 3 completly seperate SQL queries, one to fetch Employees, one to fetch Departments (which happen to be related to the Employees) and one to fetch Supervisors (which happen to be related to the Departments). If you need additional filtering on one of these queries (Supervisors in your case) then this query needs all of the additional filter and join (relation) information to be provided to it - it has no knowledge of any of any joins specified for the other queries.

Have run these 3 queries LLBLGen then builds the entity graph for you using the results.

Matt

I ran a trace on the database and got this query that is causing the problem

SELECT * (Shortened for easier reading)
FROM [database1].[dbo].[Supervisors]  
WHERE ( ( ( [database1].[dbo].[Supervisors].[DepartmentId] = 1)) 
AND ( ( [database1].[dbo].[Supervisors].[Effective_Date] IN 
          (SELECT MAX([e].[Effective_Date]) AS [EffectiveDate] 
          FROM [database1].[dbo].[Supervisors] [e]  
          WHERE ( [e].[DepartmentId] = [database1].[dbo].[Supervisors].[DepartmentId] AND [e].[Effective_Date] <= [database1].[dbo].[UserBase].[Created_Date])))))

The error comes from [database1].[dbo].[UserBase].[Created_Date] as expected.

I forgot to mention that UserBase is an inherited entity by Employee so I cannot explicitely join UserBase, because it is automatically referrenced by Employee I think (?). I tried to use EmployeeFields.CreatedDate as well and that doesn't seem to work.

Is there a way to introduce joins to these individual prefetch queries?

Now I'm confused enough Would you please atatch a screen shot of the schema of these 4 tables (Userbase, Employee, Departement & Supervisor). Or desctie these tables and their relations in text.

Sorry! The database was designed poorly and I'm just triyng to get this working with what I'm given

Here's the schema. I removed most unrelated columns from this.

UserBase Table

CREATE TABLE UserBase(
    UserBaseId          int            IDENTITY(1,1),
    Created_By          nvarchar(64)      DEFAULT CURRENT_USER NOT NULL,
    Created_Date          datetime        DEFAULT GETDATE() NOT NULL,
    Modified_By        nvarchar(64)   NULL,
    Modified_Date        datetime         NULL,
    CONSTRAINT PK_UserBase PRIMARY KEY NONCLUSTERED (UserBaseId)
)
go

Employees Table

CREATE TABLE Employees(
    UserBaseId           int             NOT NULL,  
    DepartmentId           int           NULL,
    CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (UserBaseId)
)
go

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_UserBase 
    FOREIGN KEY (UserBaseId)
    REFERENCES UserBase(UserBaseId)
go

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Departments 
    FOREIGN KEY (DepartmentId)
    REFERENCES Departments(DepartmentId)
go

Departments Table

CREATE TABLE Departments(
    DepartmentId           int           IDENTITY(1,1),
    DepartmentName       nvarchar(100)   NOT NULL,
    Created_By           nvarchar(64)   DEFAULT CURRENT_USER NOT NULL,
    Created_Date           datetime     DEFAULT GETDATE() NOT NULL,
    Modified_By         nvarchar(64)    NULL,
    Modified_Date         datetime      NULL,
    CONSTRAINT PK_Departments PRIMARY KEY NONCLUSTERED (DepartmentId)
)
go

Supervisors Table

CREATE TABLE Supervisors(
    SupervisorId         int              IDENTITY(1,1),
    DepartmentId         int              NOT NULL,
    Effective_Date     datetime      NULL,
    CONSTRAINT PK_Supervisors PRIMARY KEY NONCLUSTERED (SupervisorId)
)
go

ALTER TABLE Supervisors ADD CONSTRAINT FK_Supervisors_Departments 
    FOREIGN KEY (DepartmentId)
    REFERENCES Departments(DepartmentId)
go

Basically Employees Entity inherit from UserBase in LLBL, since all Employees are Users. Each Employee belongs to a department. Each entry in the Supervisor table indicates a supervisor belonging to a department.

It does sound confusing but I hope you can understand this.

OK. I have some coments on the database design, but this is not the time for it.

What do you want to fetch? All employees their Departements and the Departememt Supervisors at the time of joining. Correct?

This seems a bit complex.

The best thing I can think of is to fetch all supervisors and filter them at client side. By looping through the Employees and based on their creationDate, you should filter the related Deprtement's Supervisors collection.

Yes, that is the query that I would like to create. It works fine when filtering the entity because I can do joins using relations, but I guess you can't do anything with the seperate queries in prefetches.

The looping method was one that I thought of using as well but was looking for an LLBL way that goes well with my query. I guess there's no other way to do it.

As for database design I can see lots of poorly designed areas as well as conventions that were broken. I would change it if I was the dba but I'm not.