Hello,

I am new to LLBL Pro but have a large project that uses it, I am really struggling with actions that in volve m:n relationships. We are using self service and it seems like I need to create views in the db to get what I want is this true?

I am including a sample of sql that joins 6 tables and I need to get a Collection of data set back to the page and be able to manipulate it from other user controls, I get close but can seem to get the joined fields in my grid view.

Here is the sql:

SELECT p.ProductId,p.Name,p.sku,ps.Name,s.Name season,pt.Name as ptype,pc.name as category FROM Product p,ProductSeries ps,ProductSeriesSeasons pss,Season s,ProductType pt,ProductCategory pc WHERE p.ProductTypeId = pt.ProductTypeId and pt.ProductCategoryId = pc.ProductCategoryID and p.ProductSeriesId = ps.ProductSeriesId and p.ProductSeriesId = ps.ProductSeriesId and ps.ProductSeriesId = pss.ProductSeriesId and pss.seasonid = s.seasonId AND pc.Name ='Bottoms' AND ps.Name = 'Peloton'

Here is how I am trying to get this back with the objects

ProductCollection getProducts = new ProductCollection();

    RelationCollection relationsToUse = new RelationCollection();
    relationsToUse.Add(ProductEntity.Relations.ProductTypeEntityUsingProductTypeId);
    relationsToUse.Add(ProductTypeEntity.Relations.ProductCategoryEntityUsingProductCategoryId);

    IncludeFieldsList includeFields = new IncludeFieldsList();
    includeFields.Add(ProductFields.ProductId);
    includeFields.Add(ProductFields.Name);
    includeFields.Add(ProductFields.Cost);
    includeFields.Add(ProductFields.Msrp);
    includeFields.Add(ProductTypeFields.Name);
    includeFields.Add(ProductCategoryFields.Name);

    IPredicateExpression selectFilter = new PredicateExpression();
    selectFilter.Add(ProductCategoryFields.Name == "Tops");

    getProducts.GetMulti(selectFilter, 0, null, relationsToUse,null,includeFields,0,0);


    gvProducts.DataSource = getProducts;
    gvProducts.DataBind();

As you can seen I need to get a list of products and product info from several tables into my grid.

Thank

Hi rtbike,

You are pretty close. Here is an example of how to use related objects on databinding.

As you are using SelfServicing, you don't need to use PrefetchPaths. However, for performance things, you may consider it.

Do you want to modify the objects related to Product, or just want to modify Product?

Hi Daelmo,

Thanks for the quick reply, getting up to speed with your product and having some support is great, especially in the real world when projects are due yesterday.

I actually looked at the example you linked to, but missed the eval in the grid that got the field from the join table / object, though I would like restrict the size of the object if I could when I create it . I did create a type view and will look into this idea more as in this case, our product has many parts, 10 or so tables where created to rationalize.

I will need to read a little more about Prefetching too.

so there in my code behind I made the joins with relationsToUse and that seemed to work fine for filtering, at this point my gridview is auto generate columns but that will change when I get it working with the other controls, I do not need to update the objects in this page, for the most part when updating a product we just bind to the joined tables and get the id's of the related properties then just save on the main product table.

Anyway what really got me was that the includeExcludeFields seemed to have no effect on my grid? How do I get that working it seems very handy. Also I got access in the intelisence to the related fields when trying to create and Include List, an exclude list in this case would be too big, the include list is really what I want to use.

rtbike wrote:

Hi Daelmo, so there in my code behind I made the joins with relationsToUse and that seemed to work fine for filtering, at this point my gridview is auto generate columns but that will change when I get it working with the other controls, I do not need to update the objects in this page, for the most part when updating a product we just bind to the joined tables and get the id's of the related properties then just save on the main product table.

If you just want to modify/update the product's fields and reference the other related fields, I recommend you to use EntityCollections and Eval the corresponding related fields (any option of this post should work).

rtbike wrote:

Anyway what really got me was that the includeExcludeFields seemed to have no effect on my grid? How do I get that working it seems very handy. Also I got access in the intelisence to the related fields when trying to create and Include List, an exclude list in this case would be too big, the include list is really what I want to use.

The goal of the ExcludeIncludeFieldsList feature is fetch a subset of the fields of an entity for performance reasons. That doesn't mean that the object only have some fields. What the feature says is: exlude these fields as they are too big and I want to improve the performance on roundtrips. If you inspect the generated sql you will notice that only a subset of fields are fetched. That means that the entity will have (for instance) 100 fields, but only 10 fields are fetched and filled into.

So, you need to customize the declarative gridview (no autogenerate columns) to include only those fields you want to show.

Thanks again,

Last question for now, can I alias Entity Fields when I fetch them or do I have to create a typed list for that?

we go back and forth on naming the fields for use with objects and decided to name things more generic then we would normally so we could call product.name or productType.name but now this seems to a problem when building a grid with Name and Name.

I don't understand 100% your question. You can rename the entities on LLBLGenPro Designer. For example, you have a table on your DB, that table has a field named "SomeLargeName", then you can rename it on LLBLGen Designer for convenience on you code (for example, "Name").

Now, if you just want to alias for some purposes (for example, get a DataTable to pass to a report that expects some specific names) you should use typedViews, typedLists or DynamicLists

Please explain what are your need on this.

I think I solved my issue, I was just getting confused by many fields in different tables with the same name, so I wanted to Alias some of the name to make the code more readable but in the end I have to prefix with object name so it is not a problem i.e. ProductField.Name, ProductTypeField.Name

But in grid view and Eval("Name") is only for the base collection and related fields are then refereced Eval("ProductType.Name) so this is ok. I thought I was going to have a problem with the Evals.

Thanks for your reply, I am sure I will have some more questions in near future.

Todd