Hi there,

I have a EntityCollection<OrdersBesteld> which fetches relational tables using prefetchpath and RelationPredicateBucket.

In my grid I would like to show no duplicate rows.

This is what I have now:

RelationPredicateBucket rpb = new RelationPredicateBucket();

            SortExpression se = new SortExpression(OrdersBesteldFields.ProductenId | SortOperator.Ascending);
            
            if (productId != null)
            {
                IPredicateExpression pexProductId = new PredicateExpression();
                pexProductId.Add(new FieldCompareValuePredicate(OrdersBesteldFields.ProductenId, null, ComparisonOperator.Equal, productId));

                rpb.PredicateExpression.Add(pexProductId);
            }

            rpb.Relations.Add(OrdersBesteldEntity.Relations.OrdersEntityUsingOrdersId);
            rpb.Relations.Add(ContactpersonenEntity.Relations.OrdersEntityUsingContactpersonenId);

            EntityCollection<OrdersBesteldEntity> fetchedEntities = (EntityCollection<OrdersBesteldEntity>)GetEntityCollection(new EntityCollection<OrdersBesteldEntity>(), rpb, 0, se, GetPrefetchPath(EntityType.OrdersBesteldEntity));
            

            List<IEntityPropertyProjector> propertyProjectors = new List<IEntityPropertyProjector>();
            propertyProjectors.Add(new EntityPropertyProjector(OrdersBesteldFields.OrdersId, "OrdersId"));
            propertyProjectors.Add(new EntityPropertyProjector(OrdersBesteldFields.Product, "Product"));
            propertyProjectors.Add(new EntityPropertyProjector(OrdersFields.Inboekdatum, "Inboekdatum"));
            propertyProjectors.Add(new EntityPropertyProjector(ContactpersonenFields.Voorletters, "Voorletters"));
            propertyProjectors.Add(new EntityPropertyProjector(ContactpersonenFields.Tussenvoegsel, "Tussenvoegsel"));
            propertyProjectors.Add(new EntityPropertyProjector(ContactpersonenFields.Achternaam, "Achternaam"));

            DataTable projection = new DataTable();
            fetchedEntities.DefaultView.CreateProjection(propertyProjectors, projection, false);

This is my result for the fetchedEntities in a gridview:

Id Name Date 12345 T. van Est 01-02-2010 12345 T. van Est 01-02-2010

the Id, Name and Date are all from related entities (Orders and Contactpersonen)

I would like to get this in my grid: Id Name Date 12345 T. van Est 01-02-2010

So not the second (duplicate) row...

How can I do this ?

Kind regards Nathan

It's the projection to the datatable that is creating the duplicates - entity collections will only ever contain one entity per row - with as many "child" objects as necessary.

If you don't need the child object data in the grid, why bother to pre-fetch the child objects, or project to a datatable. Simply fetch the entity collection, and bind the grid to that ?

Matt

Hi Matt,

Thanks for the reply.

But we do need the prefetched entities, because the data of the grid is coming from the prefetched entities.

When I don't use the propertyprojection (so only the fetched entitycollection), I get the duplicate records.

I read about that entitycollections will remove duplicates (eather server-side, or client-side) but why do I get duplicate records then ?

Nathan

        rpb.Relations.Add(OrdersBesteldEntity.Relations.OrdersEntityUsingOrdersId);
        rpb.Relations.Add(ContactpersonenEntity.Relations.OrdersEntityUsingContactpersonenId);

First of all you don't need to use the above. PrefetchPaths fetches the related entities, you only need to add relations if you are going to filter on the related entities.

You are fetching an entity and its related many entities (1-m), that's why when projecting you can get duplicates.

Let's take a step backward and think of what you need to display on the grid, and then think of which SQL query that will return the correct resultSet. If you can post the query here, we can guide you through LLBLGen code to execute it.

Hi Walaa,

Thanx for the reply. I will remove the 2 lines you suggested.

Also, here is the SQL query the I would like to execute:

SELECT DISTINCT 
orders.id,
inboekdatum,
contactpersoon = (SELECT rtrim(ltrim(titel + ' ') + ltrim(voorletters + ' ') + ltrim(tussenvoegsel + ' ') + achternaam) FROM contactpersonen WHERE id = orders.contactpersonen_id) 
FROM orders 
INNER JOIN orders_besteld ON orders_Besteld.orders_id = orders.id 
WHERE producten_id = 177348 AND relaties_id = 12999 
ORDER BY orders.id DESC

Kind regards Nathan

Your query doesn't show which fields belong to which table/entity. I'm speaking about inboekdatum, producten_id & relaties_id.

In general you can use a DynamicList to build the mentioned query.

Also if you can join to contactpersonen and remove the sub Select query from the main select list, then you can use a normal entityCollection fetch and create a custom property in the Orders entity to retunr the contactpersoon concatenation value. In other words do the concatenation at the client side rather than the database side.

Hi Walaa,

I re-wrote the sql, this is it:

SELECT DISTINCT 
orders.id, 
orders.inboekdatum, 
contactpersonen.voorletters, 
contactpersonen.tussenvoegsel, 
contactpersonen.achternaam
FROM         orders INNER JOIN
                      orders_besteld ON orders_besteld.orders_id = orders.id INNER JOIN
                      contactpersonen ON orders.contactpersonen_id = contactpersonen.id
WHERE    (orders_besteld.producten_id = 177348) AND (orders.relaties_id = 12999)
ORDER BY orders.id DESC

My question is: You wrote about custom property on the order Entity. How do I do this ?

Nathan

Simply by adding a property to the OrdersEntity class, prefereably in a separate file (partial class) to avoid being removed when re-generating the code.

Now that you changed the query.

You can easily use a DynamicList, if you don't need a 2-way databinding (no need to modify and save the data). Or an EntityCollection as described earlier.

Hi Walaa,

Using just the entityCollection didn't work for me.

I finally got it to work using the following:

 ResultsetFields fields = new ResultsetFields(5);
            fields.DefineField(OrdersFields.Id, 0, "OrderId", "Orders");
            fields.DefineField(ContactpersonenFields.Voorletters, 1, "Voorletters", "Contactpersonen");
            fields.DefineField(ContactpersonenFields.Tussenvoegsel, 2, "Tussenvoegsel", "Contactpersonen");
            fields.DefineField(ContactpersonenFields.Achternaam, 3, "Achternaam", "Contactpersonen");
            fields.DefineField(OrdersFields.Inboekdatum, 4, "Inboekdatum", "Orders");
            
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrdersBesteldEntity.Relations.OrdersEntityUsingOrdersId);
            bucket.Relations.Add(ContactpersonenEntity.Relations.OrdersEntityUsingContactpersonenId);

            if (productId != null)
            {
                IPredicateExpression pexProductId = new PredicateExpression();
                pexProductId.Add(new FieldCompareValuePredicate(OrdersBesteldFields.ProductenId, null, ComparisonOperator.Equal, productId));

                bucket.PredicateExpression.Add(pexProductId);
            }

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[3]);
            groupByClause.Add(fields[4]);
            DataTable dynamicList = new DataTable();

            FetchTypedEntity(fields, dynamicList, bucket);

Thanks for you're help though.

Nathan