Hi all,

I'm trying to use an EntityCollection to retrieve a set of entities (Terms, below) that are not not explicitly related to a different set of entities (Sections, below) by joining on an expression. With the result, I need to know which of the related entities (Section) caused the results to be included -- so basically I need the collection to contain multiple copies of the same Term record with some indication of which Section is was related to.

I have the join-on-epxression working in code and it generates something like the first code snippet below. If each of the three Sections exist in the same one Term, only that one Term will be included in the EntityCollection.

SELECT DISTINCT     
    TERM.ID AS TermId, 
    TERM.NAME AS Name, 
    TERM.YEAR AS Year, 
    TERM.TYPE AS Type, 
    TERM.START_DATE AS TermStartDate, 
    TERM.END_DATE AS TermEndDate, 
FROM COURSE INNER JOIN
    SECTION ON COURSE.COURSE_ID = SECTION.COURSE_ID INNER JOIN
    TERM ON (SECTION.START_DATE >= TERM.START_DATE AND 
        SECTION.START_DATE < TERM.END_DATE OR
        SECTION.END_DATE > TERM.START_DATE AND 
        SECTION.END_DATE <= TERM.END_DATE OR
        SECTION.START_DATE <= TERM.START_DATE AND 
        SECTION.END_DATE >= TERM.END_DATE) 
        AND 
        COURSE.YEAR = TERM.YEAR AND 
        COURSE.TYPE = TERM.TYPE
WHERE (SECTION.SECTION_ID IN (42, 43, 44, ...))

but what I really need is something like this next snippet, where I would end up getting three Term entities with some indication of which Section each one belongs to. (Note that only the SECOND LINE was added.)

SELECT DISTINCT     
    SECTION.SECTION_ID AS SomeTagProperty,
    TERM.ID AS TermId, 
    TERM.NAME AS Name, 
    TERM.YEAR AS Year, 
    TERM.TYPE AS Type, 
    TERM.START_DATE AS TermStartDate, 
    TERM.END_DATE AS TermEndDate, 
FROM COURSE INNER JOIN
    SECTION ON COURSE.COURSE_ID = SECTION.COURSE_ID INNER JOIN
    TERM ON (SECTION.START_DATE >= TERM.START_DATE AND 
        SECTION.START_DATE < TERM.END_DATE OR
        SECTION.END_DATE > TERM.START_DATE AND 
        SECTION.END_DATE <= TERM.END_DATE OR
        SECTION.START_DATE <= TERM.START_DATE AND 
        SECTION.END_DATE >= TERM.END_DATE) 
        AND 
        COURSE.YEAR = TERM.YEAR AND 
        COURSE.TYPE = TERM.TYPE
WHERE (SECTION.SECTION_ID IN (42, 43, 44, ...))

...or even if I got back three references to the same object, that would work, too, but the upper level business logic needs to be able to figure out the Section(s) that caused the Term match without having to hit the database with this query once for each Section.

I have a sense that I'm either missing something really simple here, or I'm totally out in left field. Ideas?

Thanks for your help! -Alex

Please provide Tables structures and code snippets to fully understand the case. Thanks

Thanks for the quick reply. These are the guts of the tables I'm working with here:

 TERM
-------
ID (PK)
NAME
YEAR
TYPE
START_DATE
END_DATE

SECTION
-----------
SECTION_ID (PK)
CORUSE_ID (FK)
NAME
START_DATE
END_DATE

COURSE
----------
COURSE_ID (PK)
YEAR
TYPE

The goal is to get the TERMs for each of a number of SECTIONs, based on the SECTION and TERM start/end dates and the YEAR and TYPE of the SECTION's COURSE.

This is essentially the code I use to create the RelationPredicateBucket that generates the first SQL statement in my original post. I don't know how to make it generate the second SQL statement. This bucket, by the way, is provided by a higher level Term class to a business object collection manager that handles interaction more generically with the DAL.)

IPredicateExpression StartsInTerm = new PredicateExpression();
StartsInTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.StartDate,
    ComparisonOperator.GreaterEqual,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.StartDate ) ) ) );
StartsInTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.StartDate,
    ComparisonOperator.LesserThan,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.EndDate ) ) ) );

IPredicateExpression EndsInTerm = new PredicateExpression();
EndsInTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.EndDate,
    ComparisonOperator.GreaterThan,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.StartDate ) ) ) );
EndsInTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.EndDate,
    ComparisonOperator.LessEqual,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.EndDate ) ) ) );

IPredicateExpression IncludesTerm = new PredicateExpression();
IncludesTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.StartDate,
    ComparisonOperator.LessEqual,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.StartDate ) ) ) );
IncludesTerm.AddWithAnd( PredicateFactory.CompareExpression(
    SectionFieldIndex.EndDate,
    ComparisonOperator.GreaterEqual,
    new Expression( EntityFieldFactory.Create( TermFieldIndex.EndDate ) ) ) );

IPredicateExpression compareDates = new PredicateExpression();
compareDates.Add( StartsInTerm );
compareDates.AddWithOr( EndsInTerm );
compareDates.AddWithOr( IncludesTerm ); 

IPredicateExpression joinFilter = new PredicateExpression();
joinFilter.Add( compareDates );
joinFilter.AddWithAnd( PredicateFactory.CompareExpression( CourseFieldIndex.Year, ComparisonOperator.Equal, new Expression( EntityFieldFactory.Create( TermFieldIndex.Year ) ) ) ); 
joinFilter.AddWithAnd( PredicateFactory.CompareExpression( CourseFieldIndex.Type, ComparisonOperator.Equal, new Expression( EntityFieldFactory.Create( TermFieldIndex.Type ) ) ) ); 

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add( SectionEntity.Relations.CourseEntityUsingCourseId );
IEntityRelation r = bucket.Relations.Add( new EntityRelation( RelationType.OneToOne ) );
r.AddEntityFieldPair(
    EntityFieldFactory.Create( TermFieldIndex.TermId ),
    EntityFieldFactory.Create( SectionFieldIndex.SectionId ) );
r.CustomFilterReplacesOnClause = true;
r.CustomFilter = joinFilter;

Again, I want to get back a number of Term entities with some information about which SectionId they matched up with. Because it's basically a many-many, the Terms would need to be repeated.

If you are using an EntityCollection to return your collection, then only Entities fields will be available to you, and since there is no physical relation between The TermEntity & the SectionEntity, then I can't see a way you can attach a SectionEntity Field to the retreived collection.

You should use a Typed List instead.

P.S. to minimize your overlapping dates expressions check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4406