I posted the following message to the ASP.NET forum. I thought I would also try here, since the DTO's are populated from an LLBLGen Pro object graph. Basically, I have a hand-rolled Entity collection with a Prefetch collection attached to it. I say hand-rolled because it is actually a dynamic datatable with dynamic relations and I was never able to figure out how to use Prefetch or LINQ with such an animal, unfortunately. At some future time I'll post my solution and see if anyone can help me improve it.

In the meantime, I just need to figure out how to deal with this ReportViewer issue (and the GridView issue if possible, though I have another hand-rolled solution for the GridView).

I am using Visual Studio 2008, C#, .NET 3.5, LLBLGen Pro 2.6, Oracle.

Here is the original message.

Hi.

I have a data object where one of the properties is a collection of another data object. I never really figured out how to map this to a GridView, but I came up with a workaround. Now I am stuck and cannot figure out any way to do this in a ReportViewer. If anyone can help me with the reporting part, or even suggest a better solution for the GridView, let me know.

Here is how I solved the GridView issue:

Say we have these objects:

public class BoysWithToys : List<BoyWithToy> { }

public class BoyWithToy { string FirstName { get; set; }

string LastName { get; set; }

int BoyNumber { get; set; }

List<Toy> FavoriteToys { get; set; } }

public class Toy { int BoyNumber { get; set; }

string Toy { get; set; } }

What I wanted to do was to map BoysWithToys to the GridView and present one GridView row per BoyWithToy record. Where there was more than one FavoriteToys record I wanted to list them in a bulleted list in the FavoriteToys column. I accomplished this by calling RowDataBound() for the GridView, casting the e.Row.DataItem to BoyWithToy, then looping through all of the FavoriteToys and adding each Toy to a bulleted list, then setting the column's text to that list.

I have no idea how to accomplish anything similar in ReportViewer. If I simply drag the BoysWithToys DataSource to the designer and run it, the FavoriteToys column shows '#Error'.

Can anyone help me?

Thanks, Carl

This is not strictly an LLBLGen question I think. What I do with ReportViewer sometimes is to project my EntityCollection onto a DataTable or onto a DataSet if it is a graph (read more here).

I hadn't tried bind a custom class to a reportViewer. Consider that report manages the relations so you just need to pass the data struct separately. For example you can add a reportSource Orders and another OrderDetails separately.

Hi, David.

You're right, it isn't strictly LLBLGen Pro. The only reason I posted it here is because folks here think in terms of "graphs", where a collection can contain a collection. I only started having such animals when I started using LLBLGen Pro. I love it, but it brings in these unique issues.

The link you provided refers to EntityView2, which, as far as I can tell, does't work with dynamic data tables, only with Entities. The problem is that my original LLBLGen Pro graph is formed by stringing together a bunch of dynamic relations. A better source database, with proper PKs and FKs would have made a big difference, but unfortunately, I didn't have the luxury of reengineering the tables.

Here is a subset of what I had to do:

private static void BuildQuery
            (out ResultsetFields dtFields, out IRelationPredicateBucket filter)
        {
            IPredicate predicate;
            DynamicRelation dynamicRelation;

            // define the fields that make up the inner dynamic table
            ResultsetFields dtInnerFields = new ResultsetFields(6);

            // this typed list contains the fields from RFMODELMASTER that we are interested in
            RfModelMasterCoreTypedList rfModelMasterList = new RfModelMasterCoreTypedList();

            // loop through the typed list and add the fields to the dynamic table
            IEntityFields2 fields = rfModelMasterList.GetFieldsInfo();

            for (int i = 0; i < fields.Count; i++)
            {
                dtInnerFields[i] = fields[i];
            }

            // instantiate a RelationPredicateBucket to contain relations and filters
            filter = new RelationPredicateBucket();

            // WHERE RfModelMasterFields.ArchiveField is false
            predicate = (RfModelMasterFields.ArchiveField == false);
            filter.PredicateExpression.Add(predicate);

            // define the table for the inner subselect
            DerivedTableDefinition dtDefinition =
                new DerivedTableDefinition(dtInnerFields, SelectRequestsDetails.InnerTableAlias,
                    filter.PredicateExpression);

            // add a dynamic relation: RIGHT JOIN the inner subselect to the outer select; 
            // right join because the outer is the controller
            EntityField2 rfidField =
                new EntityField2(SelectRequestsDetails.RfIdFieldName,
                    SelectRequestsDetails.InnerTableAlias, typeof(string));
            predicate = (AeControlCategoryFields.HierPointId == rfidField);
            dynamicRelation =
                new DynamicRelation(dtDefinition, JoinHint.Right,
                    EntityType.AeControlCategoryEntity, string.Empty, predicate);

            // define the fields that make up the outer dynamic table
            // get a count of all the properties to set the ResultSetFields count
            int propertyCount = 25;

            dtFields = new ResultsetFields(propertyCount);
            // propertyIndex is auto-incremented for each new field added to the dynamic table.
            int propertyIndex;

            // this typed list contains the fields from REQMASTER and several related tables that we are interested in
            ReqMasterSearchTypedList reqMaster = new ReqMasterSearchTypedList();
            fields = reqMaster.GetFieldsInfo();

            // loop through the typed list and add the fields to the dynamic table
            for (propertyIndex = 0; propertyIndex < fields.Count; propertyIndex++)
            {
                dtFields[propertyIndex] = fields[propertyIndex];
            }

            // define the fields in the outer select that come from the inner select
            dtFields.DefineField(RfModelMasterFields.AuditableEntity, propertyIndex++,
                SelectRequestsDetails.AuditableEntityFieldName, SelectRequestsDetails.InnerTableAlias);
            dtFields.DefineField(RfModelMasterFields.AuditCommitteeSegmentShort, propertyIndex++,
                SelectRequestsDetails.LobNameFieldName, SelectRequestsDetails.InnerTableAlias);
            dtFields.DefineField(RfModelMasterFields.DirectReport, propertyIndex++,
                SelectRequestsDetails.DirectReportNameFieldName, SelectRequestsDetails.InnerTableAlias);
            dtFields.DefineField(RfModelMasterFields.AuditDirector, propertyIndex++,
                SelectRequestsDetails.DirectorNameFieldName, SelectRequestsDetails.InnerTableAlias);
            dtFields.DefineField(RfModelMasterFields.AuditManagerLead, propertyIndex++,
                SelectRequestsDetails.ManagerNameFieldName, SelectRequestsDetails.InnerTableAlias);

            // then specify the rest of the query elements
            // reset the filter (clear out previous predicates/relations)
            // since this is a typed list, call GetRelationInfo() on the typed list instead of  'filter = new RelationPredicateBucket()',
            // otherwise existing filters on the typed list are not included.
            filter = reqMaster.GetRelationInfo();
            filter.Relations.Add(dynamicRelation);

            // StatusMaster.StatusDescription field; 
            dtFields.DefineField(StatusMasterFields.StatusDesc, propertyIndex++,
                SelectRequestsDetails.StatusDescriptionFieldName);
            // ReqMasterFields.StatusCode == StatusMasterFields.StatusCode relation
            // create a dynamic relation: LEFT JOIN ON ReqMaster.StatusCode = StatusMaster.StatusCode
            predicate = (ReqMasterFields.StatusCode == StatusMasterFields.StatusCode);
            dynamicRelation =
             new DynamicRelation(EntityType.ReqMasterEntity, JoinHint.Inner,
                 EntityType.StatusMasterEntity, string.Empty, string.Empty, predicate);
            filter.Relations.Add(dynamicRelation);

...

and on and on it goes, tying two Typed Lists together along with several other fields from other dynamically related entities, none of which have keys such that I could have done a Prefetch...

It is all retrieved using FetchTypedList:

            // create the dynamic list
            DataTable selectResultsList = new DataTable();

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                // fetch the data
                adapter.FetchTypedList(dtFields, selectResultsList, filter, 0, sort, true);
            }

then all of that flat data piped into a one-to-many DTO graph. Here is part of that code:

        private static ISelectResultsList SelectResultsListDalToDtoList(DataTable selectResultsList)
        {
            SelectResultsDtoList resultsList = new SelectResultsDtoList();
            SelectResultsDto results;
            int tempRequestNumber = int.MinValue;

            for (int i = 0; i < selectResultsList.Rows.Count; i++)
            {
                DataRow row = selectResultsList.Rows[i];

                int requestNumber;
                if (!Int32.TryParse(row[SelectRequestsDetails.RequestNumberFieldName].ToString(), out requestNumber))
                {
                    throw new Exception("Request number cannot be null.");
                }

                if (tempRequestNumber != requestNumber)
                {
                    tempRequestNumber = requestNumber;

                    results = new SelectResultsDto();

                    results.RequestNumber = requestNumber;
                    results.RequestName = row[SelectRequestsDetails.RequestNameFieldName].ToString();

...

                int regRuleCode;
                if (Int32.TryParse(row[SelectRequestsDetails.RegulatoryRuleCodeFieldName].ToString(), out regRuleCode))
                {
                    IRegulatoryRule regRule = new RegulatoryRuleDto
                    {
                        RegulatoryRuleCode = regRuleCode,
                        RegulatoryRuleDescription = row[SelectRequestsDetails.RegulatoryRuleDescriptionFieldName].ToString(),
                        RequestNumber = requestNumber
                    };

                    if (resultsList.Last().RegulatoryRuleList == null)
                    {
                        resultsList.Last().RegulatoryRuleList = new RegulatoryRuleDtoList();
                    }

                    resultsList.Last().RegulatoryRuleList.Add(regRule);
                }
            }

            selectResultsList.Dispose();

            return resultsList;
        }

So, I end up with a list of the following DTO. Notice the contained RegulatoryRules list:

    public class SelectResultsDto : ISelectResults
    {
        public virtual int RequestNumber { get; set; }
        public virtual string RfId { get; set; }
        public virtual string AuditableEntity { get; set; }
        public virtual DateTime? NeededByDate { get; set; }
        public virtual DateTime? ExpectedDeliveryDate { get; set; }
        public virtual DateTime? AuditorNeededByDate { get; set; }
        public virtual DateTime? ProgrammerNeededByDate { get; set; }
        public virtual int? StatusCode { get; set; }
        public virtual string StatusDescription { get; set; }
        public virtual string RequestName { get; set; }
        public virtual string LobName { get; set; }
        public virtual string ActivityTypeDescription { get; set; }
        public virtual string AnalystName { get; set; }
        public virtual string ProgrammerName { get; set; }
        public virtual string RequestorName { get; set; }
        public virtual string DrName { get; set; }
        public virtual string DirectorName { get; set; }
        public virtual string ManagerName { get; set; }
        public virtual string AuditorName { get; set; }
        public virtual string CriticalControl { get; set; }
        public virtual string TestDescription { get; set; }
        public virtual IRegulatoryRuleList RegulatoryRuleList { get; set; }
    }

What I ended up doing is re-flattening the list back into a list where all of the columns of data are repeated for each regulatory rule, the way the data comes from LLBLGen Pro before I looped through creating the graph with the contained collection. Then I am able to group on the primary key (request number) in ReportViewer, which gives me the rows of regulatory rules but not repeating the other columns. Another approach would have been to split into two objects as you suggest (Orders/OrderDetails) and use subreports. But what I am looking for is a way to actualy use the OrderDetails (Regulatory Rules) without actually breaking it out of the Orders (SelectResults) graph. In other words, sort of a Dataset1=SelectResults; Dataset2=SelectResults.RegulatoryRuleList.

Any of that make any sense?

Yes, I understand you. However I don't see an obvious way of code what you want. My best recommendation at this point is: try to break the DTO object in two objects and bind the sources of the report accordingly.

If I remember well, when I create a RDLC for a collection of items that contains subitems I don't need to use subreports. This way:

1. Create my RDL based on some datasources linked between them. For example Order and OrderDetail. This generates two datasources (or reportsource, I don't remember), the information regarding to how these two objects are related is stored in the report.

2. Convert my RDL report into RDLC, then add it to my VSNet project.

3. Load ReportViewer, etc...

4. When loading the report, it expects two reportSources, one for Order and one for OrderDetail, the information about the relations is stored in the rdlc.

So, if that is true, you need to break your custom list of dto's into two sets. You can iterate or you can use linq I think (approximate code, not sure if it's so easy):

var subitems =  myDots.Regulatory Rules.Select();

Yes, I have concluded that all of these controls, such as ReportViewer and Gridview, are not "complex-data" aware. I would hope Microsoft would consider addressing this in the future, because more and more of us are using ORM tools such as LLBLGen Pro (or even MS's own EF) that are able to create these complex graphs.

Thanks again, Carl