Hi ,

I have 3 tables all conencted to each other via an intermediate table .. please see the attached diagram for more information. thanks

However when i try to do something like this in my code

internal static MileStoneCollection GetMileStonesForProjectAndVersion(int projectId,int versionId)
        {
            //FieldCompareValuePredicate suiteFilter = (FieldCompareValuePredicate)(ProjectVersionFields.ProjectId == projectId);
            PredicateExpression expression = new PredicateExpression();
            expression.Add(ProjectFields.ProjectId == projectId);
            expression.AddWithAnd(VersionFields.Id == versionId);
            expression.AddWithAnd(MileStoneFields.IsDeleted == false);


            RelationCollection relations = new RelationCollection();
            relations.Add(ProjectEntity.Relations.ProjectVersionMileStoneEntityUsingProjectId);
            relations.Add(VersionEntity.Relations.ProjectVersionEntityUsingVersionId);
            relations.Add(MileStoneEntity.Relations.ProjectVersionMileStoneEntityUsingMileStoneId);
            return GetMileStones(expression, null, relations);
        }

I get the following exception

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

Anybody knows why?? Any help would be appreciated..

Thanks and Regards Gagan

You are adding ProjectVersionMileStone twice. In my opinion, this should be:

RelationCollection relations = new RelationCollection();
relations.Add(MileStoneEntity.Relations.ProjectVersionMileStoneEntityUsingMileStoneId);
realtions.Add(ProjectVersionMileStoneEntity.Relations.ProjectEntityUsingProjectId);
realtions.Add(ProjectVersionMileStoneEntity.Relations.VersionEntityUsingVersionId);

As a matter of fact, the fields you are filtering are all in the intermediate table ProjectVersionMileStone, so you just need that relation. Your could would ends like this:

internal static MileStoneCollection GetMileStonesForProjectAndVersion(int projectId,int versionId)
        {
            PredicateExpression expression = new PredicateExpression();
            expression.Add(ProjectVersionMileStoneFields.ProjectId == projectId);
            expression.AddWithAnd(ProjectVersionMileStoneFields.VersionId == versionId);
            expression.AddWithAnd(MileStoneFields.IsDeleted == false);

            RelationCollection relations = new RelationCollection();
            relations.Add(MileStoneEntity.Relations.ProjectVersionMileStoneEntityUsingMileStoneId);
            
            return GetMileStones(expression, null, relations);
        }

Hope helpful.