Hi,

I am having tables with the following structure

Territory(Table) --- NetworkId(Columns) --- LocationId(Columns)(FK)

Location(Table) --- Id(Columns)(PK) --- SubBrickId(Columns)(FK)

SubBrick(Table)

   --- Id(Columns)(PK)
   --- BrickId(Columns)(FK)

Brick(Table)

   -- Id (Columns)(PK)
   -- RegionId (Column)(FK)

Region (Table)

   --- Id(Column)(PK)
   --- CountryId(Column)(FK)

Country

   --- Id(PK)

All the above tables have descriptionId column and this column is associated with a respective resource tables in our project.

I need to retrieve the brick,subbrick, region and country details for a given networkid.

What i had done is getting a territoryEntity collection. Already done the prefetch and applied the networkid filter. So while executing all the related entities are fetched in a single call..no lazyloading. Here the subbrick,brick,region and country queries return the expected rows.

For retrieving the list of subbrick details i had looped through for each territoryentity in the territoryentity collection object. done code like this

foreach(TerritoryEntity row in territories) { SubBrickEntity subBrick = row.Location.SubBrick_; //Populating the list control for binding fillSubbrick.Add(subBrick.Description,subBrick.Id) //Description property is a custom property we had defined in the subbrick partial class }

I am getting duplicate values for subbrick for each and every networkid. Please let me know how can i retrieve the distinct subbrick values or there is any other method to retireve the subbrick collection seperately.

Help is very much appreciated.

Thanks

Prabhu

Hi Prabhu. The prefetch guarantee that for each Territory a Location related will fetched and for each Location a SubBrick related. So according to your DB schema, Territories can have duplicate Locations and Locations can have duplicate SubBrick. In this case is better if you obtain SubBrick Collection filtered by NetworkId (using relations).

Here is an example:

What I want: ALL CUSTOMERS WHICH HAVE ORDERS SOLD BY EMPLOYEE 6

If I try to get the customers via an Orders Fetch I'll obtain duplicate customers:

// collection to retrieve
EntityCollection<OrdersEntity> orders = new EntityCollection<OrdersEntity>(new OrdersEntityFactory());

// path
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.OrdersEntity);
path.Add(OrdersEntity.PrefetchPathCustomers);

// i want to filter all the customers that have orders sold by employee 6
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(OrdersFields.EmployeeId == 6);

// get the data
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter, path);
}

// see the data
foreach (OrdersEntity order in orders)
{
    Console.WriteLine(order.Customers.CustomerId);
}

The solution is get CUSTOMERS filtered by employeeID of their related orders

// collection to retrieve
EntityCollection<CustomersEntity> customers = new EntityCollection<CustomersEntity>(new CustomersEntityFactory());

// i want to filter all the customers that have orders sold by employee 6
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId);
filter.PredicateExpression.Add(OrdersFields.EmployeeId == 6);

// get the data
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, filter);
}

// see the data
foreach (CustomersEntity customer in customers)
{
    Console.WriteLine(customer.CustomerId);
}

Hi,

Thanks for the reply... But i am not able to set the relation against the subbrick entity.

For me i need to get the list of Subbricks for a given territory based on networkid.

While adding the relation for subbrick Entity there is no territoryEntityUsingNetworkId.It contains only relation between location. I can able to set the relation like

filter.Relations.Add(SubBrickEntity.Relations.LocationEntityUsingSubBrickId);

But i need to list of all distinct subbrick details based on networkid present in the territoryEntity object.

Help is very much appreciated.

Regards

Prabhu

One more thing... I can't able to get subbrick collections based on networkid directly because subbrick contains 1:n relationship with location only.

territory entity is having relationship with locationentity.

I need to get the subbrick collection for a given networkid through the follwoing path

Territory ---> Location ---> Subbrick (NetworkId) (SubBrickId) (LocationId)

Hi,

you can add 2 relations to walk from Subbrick to Territory :

filter.Relations.Add(SubBrickEntity.Relations.LocationEntityUsingSubBrickId);
filter.Relations.Add(LocationEntity.Relations.TerritoryUsingLocationId;

(the order is important)

Thanks..

hi,

As you advised above, i am able to retrieve the Location Entity fields.

The code i have used here is:

        List<LocationInfo> rowsToReturn = null;

        EntityCollection<LocationEntity> locations = new EntityCollection<LocationEntity>(new LocationEntityFactory());

        IRelationPredicateBucket filter = new RelationPredicateBucket();
        filter.Relations.Add(LocationEntity.Relations.TerritoryEntityUsingLocationId);
        filter.Relations.Add(LocationEntity.Relations.SubBrickEntityUsingSubBrickId);
        filter.Relations.Add(SubBrickEntity.Relations.GeographyResourceEntityUsingDescriptionId);
                filter.PredicateExpression.Add(TerritoryFields.NetworkId == 2);         

        oData.FetchEntityCollection(locations, filter);

        if (locations.Count > 0)
        {
    rowsToReturn = new List<LocationInfo>();
            foreach (LocationEntity location in locations)
            {
      LocationInfo rowNew = new LocationInfo();
              rowNew.Id = location.Id;
              rowNew.Name = location.Name;
            **rowNew.SubBrickName = location.SubBrickName;**
              rowsToReturn.Add(rowNew);
            }
        }

return rowsToReturn;

Here i need to retrieve the Subrick entity fields also for my output, for example here is SubBrickName. how do i retrieve other entity fields too.

Thanks

If you are using the Adapter, then you'll have to prefetchPath the Subrick Entity when fetching the location. So the following should work:

rowNew.SubBrickName = location.SubBrick.Name;

PrefetchPath is doable in SelfServicing too, or you might just use the above line of code and the Subrick will be fetched using LazyLoading.

Another Solution: If you are only returning some set of fields from Location and SubBrick entity you might use a DunamicList or a Typed List. Better than fetching EntityCollections and then loop on them to return some set of fields.

Thanks for your reply.

I tried with Prefetchpath solution first.

I have set that per page, the paging size is 7.

So for the page 1, First 7 distinct records i am getting. that is absolutely correct.

For the second page, instead fetching next 7 distinct records(8-14), it fetches 8 to 14 of original records and then doing Distinct. so in this case, second paging gives only one Distinct record.

Please refer the attached files for more information.

Thanks.

Please get the latest runtime libs which uses a more optimal paging query. Your traces show you're using an older Sqlserver DQE.