I've got no idea how to write this correlated subquery

Posts   
 
    
Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 17:43:46   

I'm simply at a loss for how to do this with LLBLGenPro (Adapter)

Here's the query:

SELECT e.EmployeesId, e.FirstName, e.LastName, EmployeeToDepartments.EffectiveDate, Departments.Department

FROM Employees e LEFT OUTER JOIN EmployeeToDepartments ON e.EmployeesID = EmployeeToDepartments.EmployeesID LEFT OUTER JOIN Departments ON EmployeeToDepartments.DepartmentsID = Departments.DepartmentsID

WHERE EffectiveDate IN ( SELECT TOP(1) EmployeeToDepartments.EffectiveDate FROM EmployeeToDepartments WHERE EmployeeToDepartments.EffectiveDate <= GetDate() AND EmployeeToDepartments.EmployeesID = e.EmployeesID ORDER BY EmployeeToDepartments.EffectiveDate DESC )

ORDER BY LastName

This is extremely frustrating... I kicked this out in less than five minutes in SQL. I've spent hours trying to rewrite it in LLBL. I'm trying to populate a typedList and I just have no clue what to do.

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 21-Dec-2007 19:41:42   

does this sql statement give you the same results?

declare @date datetime
select
     e.EmployeeId,
     e.FirstName,
     e.LastName,
     (
             select max(ed.EffectiveDate)
             form EmployeeToDepartments ed
             where ed.EffectiveDate <= @date 
                and  ed.EmployeesID = e.EmployeesID
     ) as EffectiveDate
     (
             select top 1 d.Department
             form EmployeeToDepartments ed inner join Department d on ed.DepartmentId = d.DeparmentId
             where ed.EffectiveDate <= @date 
                and  ed.EmployeesID = e.EmployeesID
             order by ed.EffectiveDate desc
     ) as Department
from Employee e
order by e.LastName

if so you could use EntityField2 and IExpressions to build the sub queries. I used this heavily in my last project.

however I would think this solutions is much simpler. depending on the number of fields and there types the preformance hit may be unnoticable.

EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();

IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.Employee);
IPrefetchElement2 element = prefetch.Add(EmployeeEntity.PrefetchEmployeeToDeparment);
element.SubPath.Add(EmployeeToDeparmentEntity.PrefetchDepartment);
element.MaxRecordsToReturn = 1;
element.SortOperator.Add(EmployeeToDepartmentFields.EffectiveDate | SortOperator.Descending);
element.Filter.Add(EmployeeToDepartmentFields.EffectiveDate <= DateTime.Now);

adapter.FetchEntityCollection(employees, null, prefetch);

then simply map the collection to a data table

DataTable table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("EffectiveDate", typeof(DateTime));
table.Columns.Add("Department", typeof(string));

foreach(EmployeeEntity employee in employees)
{
       DataRow row = table.NewRow();
       row["Id"] = employee.Id;
       row["FirstName"] = employee.FirstName;
       row["LastName"] = employee.LastName;
       row["EffectiveDate"] = employee.EmployeeToDeparment[0].EffectiveDate;
       row["Department"] = employee.EmployeeToDeparment[0].Deparement.Department;
       table.Rows.Add(row);
}
return table

Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 19:45:43   

I'm just going to keep asking questions until I get answers I guess.

(1) How do I alias the "root" entity in this query. I need to alias Employees, but when I do this:


IRelationPredicateBucket filter = new RelationPredicateBucket();       filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, ,"e","f",JoinHint.Left);

I get an error of the type: "Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?" I don't really want to alias it this way (I don't need the "f" as far as I can tell from my SQL, but I can't figure out any other way).

I have looked at the documentation for aliases and all of the examples show how to alias the right-hand-side of a join like this; none show how to alias the root entity.

(2) The subquery syntax doesn't make any sense to me. I have this:

subQuery.Add(EmployeeToDepartmentsFields.EffectiveDate <= DateTime.Now);
            subQuery.AddWithAnd(new FieldCompareValuePredicate(EmployeeToDepartmentsFields.EmployeeId, null, ComparisonOperator.Equal, EmployeesFields.EmployeeId, "e"));

And this (that I got from another thread here)


            IEntityField2 field1 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
            IEntityField2 field2 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
            field2.AggregateFunctionToApply = AggregateFunction.Max;
            
            IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);
            filter.PredicateExpression.Add(predicate1);

This doesn't make any sense. My subquery only needs to refer to "Employees e".

So far for my query I have this, and it doesn't work:

IPredicateExpression subQuery = new PredicateExpression();
            subQuery.Add(EmployeeToDepartmentsFields.EffectiveDate <= DateTime.Now);
            subQuery.AddWithAnd(new FieldCompareValuePredicate(EmployeeToDepartmentsFields.EmployeeId, null, ComparisonOperator.Equal, EmployeesFields.EmployeeId, "e"));

 IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, JoinHint.Left);
        filter.Relations.Add(EmployeeToDepartmentsEntity.Relations.DepartmentsEntityUsingDepartmentId, JoinHint.Left);
 IEntityField2 field1 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
            IEntityField2 field2 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
            field2.AggregateFunctionToApply = AggregateFunction.Max;
            
            IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);
            filter.PredicateExpression.Add(predicate1);

There has to be a way to make this query work, right? If I can do it in SQL I ought to be able to do it with LLBL.

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 21-Dec-2007 19:54:42   

i can appreciate your frustration, I went through the same thing when i moved into complex queries. Yes it's possible, you just need to figure out the right order of operations simple_smile

I think this is the missing piece

IEntityRelation relation = 
EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId;
relation.Alias = "foo";

bucket.Relations.Add(relation, JoinHint.Right);
//repeat for other relationships
Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 19:56:41   

[quotenick="jmeckley"]does this sql statement give you the same results?

Nope rage

Thanks for trying though.

Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 19:59:08   

jmeckley wrote:

i can appreciate your frustration, I went through the same thing when i moved into complex queries. Yes it's possible, you just need to figure out the right order of operations simple_smile

I think this is the missing piece

IEntityRelation relation = 
EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId;
relation.Alias = "foo";

bucket.Relations.Add(relation, JoinHint.Right);
//repeat for other relationships

I can't do a relation.Alias = "foo". The object doesn't have that property.

It does have:

relation.AliasPKSide (not settable) relation.AliasFKSide (not settable) relation.SetAliases(pkAlias, fkAlias) which doesn't let you set just the one alias.

DvK
User
Posts: 323
Joined: 22-Mar-2006
# Posted on: 21-Dec-2007 20:05:45   

Set the alias(ses) in one of the overloads of adding relations to a relationpredicatebucket. You can specify a start and end relation alias here.

That works best.

Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 20:13:12   

DvK wrote:

Set the alias(ses) in one of the overloads of adding relations to a relationpredicatebucket. You can specify a start and end relation alias here.

That works best.

I keep getting an exception:

"Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?"

I've tried the overloads, none of them work:

filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, "e", JoinHint.Left);
filter.Relations.Add(EmployeesEntity.Relations.EmployeeToDepartmentsEntityUsingEmployeeId, "e","f", JoinHint.Left);

They just keep throwing that exception.

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 21-Dec-2007 20:16:33   

if i remember correctly you need to build the relationship manually. something like this

IEntityRelation relation = new EntityRelaionship(...)

then use one of the overloaded constructors to set the pk and fk tables, columns and aliases. use the aliases defined here in the sub query predicates.

thinking back, i'm pretty sure you cannot use the static relationship helpers (MyEnity.Relations.NameOfStaticHelper) for this type of query.

Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 21:24:08   

Per Franse's post here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8361&HighLight=1

I've rewritten the SQL query to only use alias in the subquery. Now, does this help anyone to give me a tip on how to translate this to LLBL?

SELECT
Employees.EmployeesId, Employees.FirstName, Employees.LastName, 
EmployeeToDepartments.EffectiveDate,
Departments.Department

FROM Employees
LEFT OUTER JOIN EmployeeToDepartments ON Employees.EmployeesID = EmployeeToDepartments.EmployeesID
LEFT OUTER JOIN Departments ON EmployeeToDepartments.DepartmentsID = Departments.DepartmentsID

WHERE EffectiveDate IN
(
    SELECT TOP(1) e.EffectiveDate 
    FROM EmployeeToDepartments e
    WHERE e.EffectiveDate <= GetDate() AND
    e.EmployeesID = Employees.EmployeesID
    ORDER BY e.EffectiveDate DESC
)

ORDER BY LastName 
jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 21-Dec-2007 21:36:31   
string subQueryAlias = "foo";

EntityField2 subQueryEffectiveDate = EmployeeToDepartmentFields.EffectiveDate.SetObjectAlias(subQueryAlias);
EntityField2 subQueryEmployeeId = EmployeeFields.Id.SetObjectAlias(subQueryAlias);

IPredicateExpression filter = new PredicateExpression();
filter.Add(subQueryEffectiveDate <= DateTime.Now);
filter.Add(subQueryEmployeeId == EmployeeFields.Id);

new FieldCompareSetPredicate(EmployeeToDepartmentFields.EffectiveDate, null, subQueryEffectiveDate , null, SetOperator.In, filter);

Shara
User
Posts: 19
Joined: 18-Jul-2005
# Posted on: 21-Dec-2007 21:48:02   

Solved it!

Here's the solution for future reference. The key was getting the SQL down to just an alias in the subquery; then things got easier. I had to rewrite the SQL ever so slightly:


SELECT
Employees.EmployeesId, Employees.FirstName, Employees.LastName, 
EmployeeToDepartments.EffectiveDate,
Departments.Department

FROM Employees
LEFT OUTER JOIN EmployeeToDepartments ON Employees.EmployeesID = EmployeeToDepartments.EmployeesID
LEFT OUTER JOIN Departments ON EmployeeToDepartments.DepartmentsID = Departments.DepartmentsID

WHERE EffectiveDate IN
(
    SELECT TOP(1) e.EffectiveDate 
    FROM EmployeeToDepartments e
    WHERE e.EffectiveDate <= GetDate() AND
    e.EmployeesID = EmployeeToDepartments.EmployeesID
    ORDER BY e.EffectiveDate DESC
)

I removed the ORDER BY Employee.LastName because it's frivolous to the whole problem.

The LLBLGenPro code was:


// SubQuery
IPredicateExpression subQuery = new PredicateExpression();
subQuery.Add(EmployeeToDepartmentsFields.EffectiveDate.SetObjectAlias("e") <= DateTime.Now);
subQuery.AddWithAnd(EmployeeToDepartmentsFields.EmployeeId.SetObjectAlias("e") == EmployeeToDepartmentsFields.EmployeeId);

IEntityField2 field1 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
IEntityField2 field2 = EntityFieldFactory.Create(EmployeeToDepartmentsFieldIndex.EffectiveDate);
field2.ObjectAlias = "e";
field2.AggregateFunctionToApply = AggregateFunction.Max;

IPredicateExpression filter2 = new PredicateExpression();
IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);
filter2.Add(predicate1);

// Prefetch Path
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeesEntity);
prefetchPath.Add(EmployeesEntity.PrefetchPathEmployeeToDepartments, 0, filter2).SubPath.Add(EmployeeToDepartmentsEntity.PrefetchPathDepartments);

EntityCollection<EmployeesEntity> collection = new EntityCollection<EmployeesEntity>();

adapter.FetchEntityCollection(collection, null, prefetchPath);

Thanks to everyone who participated. It's nice to know in my morning of frustration I wasn't totally alone.