Hi,

We are having a business method to retrieve Person, Location and specialty details from the db. I had attached the DB diagram for your reference. Please find the method below

public List<PersonLocationInfo> GetPersonLocations(int[] personLocationIds) { EntityCollection<PersonLocationEntity> PersonLocation = new EntityCollection<PersonLocationEntity>(new PersonLocationEntityFactory()); IPrefetchPath2 prefetchPath = GetPersonLocationPrefetch(); IRelationPredicateBucket bucket = GetPersonLocationBucket(prefetchPath); FillPredicateExpressionForUnPlannedCall(bucket.PredicateExpression, personLocationIds);

        oData.FetchEntityCollection(PersonLocation, bucket, 0, null, prefetchPath);
        List<PersonLocationInfo> objPersonLocationInfo = new List<PersonLocationInfo>();


        if (PersonLocation.Count > 0)
        {
            foreach (PersonLocationEntity row in PersonLocation)
            {
                PersonLocationInfo rowNew = new PersonLocationInfo();
                rowNew.LocationId = row.Location.Id;
                rowNew.Location = row.Location.Name;
                rowNew.PersonId = row.Person.Id;
                rowNew.Person = row.Person.FirstName + " " + row.Person.LastName;

                if (row.PersonSpecialty.Count > 0)
                {
                    rowNew.SpecialtyId = row.PersonSpecialty[0].Specialty.Id;
                    if (row.PersonSpecialty[0].Specialty.LocationResource.LocationResourceDetail.Count > 0)
                    {
                        rowNew.Specialty = row.PersonSpecialty[0].Specialty.LocationResource.LocationResourceDetail[0].Value;
                        //rowNew.Specialty = row.PersonSpecialty[0].Specialty.Description;
                    }
                    else
                    {
                        rowNew.Specialty = string.Empty;
                    }
                }
                else
                {
                    rowNew.SpecialtyId = -1;
                    rowNew.Specialty = string.Empty;
                }
                objPersonLocationInfo.Add(rowNew);
            }
        }
        return objPersonLocationInfo;
    }

The prefetch method is given below

private IPrefetchPath2 GetPersonLocationPrefetch() { IPrefetchPath2 prefetchPath = null; try {

                prefetchPath = new PrefetchPath2((int)EntityType.PersonLocationEntity);
                prefetchPath.Add(PersonLocationEntity.PrefetchPathLocation);
                prefetchPath.Add(PersonLocationEntity.PrefetchPathPerson);

prefetchPath.Add(PersonLocationEntity.PrefetchPathPersonSpecialty).SubPath.Add( PersonSpecialtyEntity.PrefetchPathSpecialty).SubPath.Add( SpecialtyEntity.PrefetchPathLocationResource).SubPath.Add( LocationResourceEntity.PrefetchPathLocationResourceDetail);//.SubPath.Add( LocationResourceDetailEntity.PrefetchPathLanguage);

        }
        catch (Exception ex)
        {
            OptimaExceptionHandler.HandleException(ex);
        }
        return prefetchPath;
    }

private void FillPredicateExpressionForUnPlannedCall(IPredicateExpression predicateToFill, int[] personLocationIds) { if (personLocationIds.Length > 0) { predicateToFill.Add(new FieldCompareRangePredicate(PersonLocationFields.Id, null, personLocationIds)); predicateToFill.AddWithAnd(LocationResourceDetailFields.LanguageId == languageId); } }

Here i am referencing personlocation table as base in entitycollection and passing two parameters i.e personlocationid and languageid as an predicate. In the locationresourcedetail table i am having two rows with same resourceid but with unique languageid 's. While i try to retrieve the specialty description from locationresourcedetail i am getting two rows instead of expected one row since i am passing also the language id.

The following is the dynamic queries generated by sqlserver

This query is for personlocation table

exec sp_executesql N'SELECT DISTINCT [OptimaTigerSG].[Persons].[PersonLocation].[id] AS [Id], [OptimaTigerSG].[Persons].[PersonLocation].[personId] AS [PersonId], [OptimaTigerSG].[Persons].[PersonLocation].[locationId] AS [LocationId], [OptimaTigerSG].[Persons].[PersonLocation].[positionId] AS [PositionId], [OptimaTigerSG].[Persons].[PersonLocation].[averagePatientsId] AS [AveragepatientsId], [OptimaTigerSG].[Persons].[PersonLocation].[consultationFeeId] AS [ConsultationFeeId], [OptimaTigerSG].[Persons].[PersonLocation].[status] AS [Status], [OptimaTigerSG].[Persons].[PersonLocation].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Persons].[PersonLocation].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Persons].[PersonLocation].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Persons].[PersonLocation].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Persons].[PersonLocation].[modificationSource] AS [ModificationSource] FROM (((((( [OptimaTigerSG].[Locations].[Location]
RIGHT JOIN [OptimaTigerSG].[Persons].[PersonLocation] ON
[OptimaTigerSG].[Locations].[Location].[id]=[OptimaTigerSG].[Persons].[PersonLocation].[locationId]) LEFT JOIN [OptimaTigerSG].[Persons].[Person] ON [OptimaTigerSG].[Persons].[Person].[id]=[OptimaTigerSG].[Persons].[PersonLocation].[personId]) LEFT JOIN [OptimaTigerSG].[Persons].[PersonSpecialty] ON
[OptimaTigerSG].[Persons].[PersonLocation].[id]=[OptimaTigerSG].[Persons].[ PersonSpecialty].[personLocationId]) LEFT JOIN [OptimaTigerSG].[Locations].[Specialty] ON
[OptimaTigerSG].[Locations].[Specialty].[Id]=[OptimaTigerSG].[Persons].[PersonSpecialty].[specialtyId]) LEFT JOIN [OptimaTigerSG].[Locations].[LocationResource] ON
[OptimaTigerSG].[Locations].[LocationResource].[id]=[OptimaTigerSG].[Locations].[Specialty].[descriptionId]) LEFT JOIN [OptimaTigerSG].[Locations].[LocationResourceDetail] ON
[OptimaTigerSG].[Locations].[LocationResource].[id]=[OptimaTigerSG].[Locations].[ LocationResourceDetail].[resourceId]) WHERE ( ( [OptimaTigerSG].[Persons].[PersonLocation].[id] IN (@Id1) AND [OptimaTigerSG].[Locations].[LocationResourceDetail].[languageId] = @LanguageId2))',N'@Id1 int,@LanguageId2 int',@Id1=2001,@LanguageId2=1 ** Query for location**

exec sp_executesql N'SELECT [OptimaTigerSG].[Locations].[Location].[id] AS [Id], [OptimaTigerSG].[Locations].[Location].[subBrickId] AS [SubBrickId], [OptimaTigerSG].[Locations].[Location].[locationTypeId] AS [LocationTypeId], [OptimaTigerSG].[Locations].[Location].[locationClassId] AS [LocationClassId], [OptimaTigerSG].[Locations].[Location].[contactInfoId] AS [ContactInfoId], [OptimaTigerSG].[Locations].[Location].[name] AS [Name], [OptimaTigerSG].[Locations].[Location].[otherName] AS [OtherName], [OptimaTigerSG].[Locations].[Location].[keyword] AS [Keyword], [OptimaTigerSG].[Locations].[Location].[status] AS [Status], [OptimaTigerSG].[Locations].[Location].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Locations].[Location].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Locations].[Location].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Locations].[Location].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Locations].[Location].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Locations].[Location] WHERE ( ( ( [OptimaTigerSG].[Locations].[Location].[id] = @Id1)))',N'@Id1 int',@Id1=78

Query for Person exec sp_executesql N'SELECT [OptimaTigerSG].[Persons].[Person].[id] AS [Id], [OptimaTigerSG].[Persons].[Person].[firstName] AS [FirstName], [OptimaTigerSG].[Persons].[Person].[lastName] AS [LastName], [OptimaTigerSG].[Persons].[Person].[keyword] AS [Keyword], [OptimaTigerSG].[Persons].[Person].[sex] AS [Sex], [OptimaTigerSG].[Persons].[Person].[dateOfBirth] AS [DateOfBirth], [OptimaTigerSG].[Persons].[Person].[officialId] AS [OfficialId], [OptimaTigerSG].[Persons].[Person].[phone] AS [Phone], [OptimaTigerSG].[Persons].[Person].[mobile] AS [Mobile], [OptimaTigerSG].[Persons].[Person].[email] AS [Email], [OptimaTigerSG].[Persons].[Person].[universityId] AS [UniversityId], [OptimaTigerSG].[Persons].[Person].[yearOfGraduation] AS [YearOfGraduation], [OptimaTigerSG].[Persons].[Person].[comments] AS [Comments], [OptimaTigerSG].[Persons].[Person].[bankId] AS [BankCode], [OptimaTigerSG].[Persons].[Person].[titleId] AS [TitleId], [OptimaTigerSG].[Persons].[Person].[status] AS [Status], [OptimaTigerSG].[Persons].[Person].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Persons].[Person].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Persons].[Person].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Persons].[Person].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Persons].[Person].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Persons].[Person] WHERE ( ( ( [OptimaTigerSG].[Persons].[Person].[id] = @Id1)))',N'@Id1 int',@Id1=1953

Query for PersonSpecialty

exec sp_executesql N'SELECT [OptimaTigerSG].[Persons].[PersonSpecialty].[id] AS [Id], [OptimaTigerSG].[Persons].[PersonSpecialty].[personLocationId] AS [PersonLocationId], [OptimaTigerSG].[Persons].[PersonSpecialty].[specialtyId] AS [SpecialtyId], [OptimaTigerSG].[Persons].[PersonSpecialty].[principal] AS [Principal], [OptimaTigerSG].[Persons].[PersonSpecialty].[status] AS [Status], [OptimaTigerSG].[Persons].[PersonSpecialty].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Persons].[PersonSpecialty].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Persons].[PersonSpecialty].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Persons].[PersonSpecialty].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Persons].[PersonSpecialty].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Persons].[PersonSpecialty]
WHERE ( ( ( [OptimaTigerSG].[Persons].[PersonSpecialty].[personLocationId] = @PersonLocationId1)))',N'@PersonLocationId1 int',@PersonLocationId1=2001

Query for Specialty exec sp_executesql N'SELECT [OptimaTigerSG].[Locations].[Specialty].[Id], [OptimaTigerSG].[Locations].[Specialty].[descriptionId] AS [DescriptionId], [OptimaTigerSG].[Locations].[Specialty].[specialist] AS [Specialist], [OptimaTigerSG].[Locations].[Specialty].[status] AS [Status], [OptimaTigerSG].[Locations].[Specialty].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Locations].[Specialty].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Locations].[Specialty].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Locations].[Specialty].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Locations].[Specialty].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Locations].[Specialty] WHERE ( ( ( [OptimaTigerSG].[Locations].[Specialty].[Id] = @Id1)))',N'@Id1 int',@Id1=70

Query for LocationResource

exec sp_executesql N'SELECT [OptimaTigerSG].[Locations].[LocationResource].[id] AS [Id], [OptimaTigerSG].[Locations].[LocationResource].[value] AS [Value], [OptimaTigerSG].[Locations].[LocationResource].[shortValue] AS [ShortValue], [OptimaTigerSG].[Locations].[LocationResource].[status] AS [Status], [OptimaTigerSG].[Locations].[LocationResource].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Locations].[LocationResource].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Locations].[LocationResource].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Locations].[LocationResource].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Locations].[LocationResource].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Locations].[LocationResource] WHERE ( ( ( [OptimaTigerSG].[Locations].[LocationResource].[id] = @Id1)))',N'@Id1 int',@Id1=122

Query for LocationResourceDetail

exec sp_executesql N'SELECT [OptimaTigerSG].[Locations].[LocationResourceDetail].[id] AS [Id], [OptimaTigerSG].[Locations].[LocationResourceDetail].[resourceId] AS [ResourceId], [OptimaTigerSG].[Locations].[LocationResourceDetail].[languageId] AS [LanguageId], [OptimaTigerSG].[Locations].[LocationResourceDetail].[value] AS [Value], [OptimaTigerSG].[Locations].[LocationResourceDetail].[shortValue] AS [ShortValue], [OptimaTigerSG].[Locations].[LocationResourceDetail].[status] AS [Status], [OptimaTigerSG].[Locations].[LocationResourceDetail].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Locations].[LocationResourceDetail].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Locations].[LocationResourceDetail] WHERE ( ( ( [OptimaTigerSG].[Locations].[LocationResourceDetail].[resourceId] = @ResourceId1)))',N'@ResourceId1 int',@ResourceId1=122

If you see the last query it contains only the resourceid in the where clause. But in my predicate method FillPredicateExpressionForUnPlannedCall i am passing also the languageid as an parameter. Please let me know why the predicate condition is not appended into the locationresourcedetail table query.

Thanks

Prabhu

Query for LocationResourceDetail

exec sp_executesql N'SELECT [OptimaTigerSG].[Locations].[LocationResourceDetail].[id] AS [Id], [OptimaTigerSG].[Locations].[LocationResourceDetail].[resourceId] AS [ResourceId], [OptimaTigerSG].[Locations].[LocationResourceDetail].[languageId] AS [LanguageId], [OptimaTigerSG].[Locations].[LocationResourceDetail].[value] AS [Value], [OptimaTigerSG].[Locations].[LocationResourceDetail].[shortValue] AS [ShortValue], [OptimaTigerSG].[Locations].[LocationResourceDetail].[status] AS [Status], [OptimaTigerSG].[Locations].[LocationResourceDetail].[createdBy] AS [CreatedBy], [OptimaTigerSG].[Locations].[LocationResourceDetail].[createdOn] AS [CreatedOn], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modifiedBy] AS [ModifiedBy], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modifiedOn] AS [ModifiedOn], [OptimaTigerSG].[Locations].[LocationResourceDetail].[modificationSource] AS [ModificationSource] FROM [OptimaTigerSG].[Locations].[LocationResourceDetail] WHERE ( ( ( [OptimaTigerSG].[Locations].[LocationResourceDetail].[resourceId] = @ResourceId1)))',N'@ResourceId1 int',@ResourceId1=122

If you see the last query it contains only the resourceid in the where clause. But in my predicate method FillPredicateExpressionForUnPlannedCall i am passing also the languageid as an parameter. Please let me know why the predicate condition is not appended into the locationresourcedetail table query.

If you want to pass a filter to that PrefechPath query, then you have to pass that filter to the PrefetchPath itself.

it should look like the following: prefetchPath.Add(PersonLocationEntity.PrefetchPathPersonSpecialty).SubPath.Add( PersonSpecialtyEntity.PrefetchPathSpecialty).SubPath.Add(SpecialtyEntity.PrefetchPathLocationResource, 0, myFilter).SubPath.Add(LocationResourceEntity.PrefetchPathLocationResourceDetail);//.SubPath.Add( LocationResourceDetailEntity.PrefetchPathLanguage);