IQueryable<CustomClass> as report data source

Hi. I’m trying to understand how a list (IQueryable) of custom class objects is bound to an LL report. I have a class similar to this:

public class ChildInfo
{
public string FirstName {get;set;}
public string LastName {get;set;}
}

public class MoreInfo
{
public string A {get;set;}
public string B {get;set;}
}

public class ReportData
{
public string Category {get;set;}
public ChildInfo Child {get;set;}
public MoreInfo More {get;set;}
}

When I design my report I drag a report container onto the report surface and am prompted for the object type and I choose Table. Up pops the Choose Source Table Path with about a dozen top-level nodes shown. The “Free content” node I understand and the ones that appear to be table names I don’t really understand why they are there. In any event, the ReportData node (class above) is there as well. If I highlight that node and click OK, then I can only see “simple” types like Category, I cannot see the Child field.

So I try again and highlight the Child node, but this only lets me see the Child and More nodes with their subfields, I cannot see the Category field to put on the report.

Am I doing something wrong such that I can’t see Category, Child.FirstName, Child.LastName, More.A, and More.B on the report, at the same time?

What is the maximumRecursionDepth parameter to the ObjectDataProvider? I create one of those to hold my IQueryable custom class query as the first parameter and have tried 0, 1, 2, 3, 4, and 5 for the max parameter and don’t really understand what it is doing with the 0, 1, 2, 3, 4, and 5 values. (I attach the ObjectDataProvider to the ListLabel.DataSource property. This all works, presumably as intended, but I’m having a hard time understanding what that second parameter is doing, and whether this has anything to do with not all of the fields (as described above) being available at the same time for my report.

Any guidance would be appreciated.

Hi Glenn,

Without having tried that - there’s a property “FlattenDataStructure” for the provider IIRC. That should help, as it will help you to get rid of the tables that are added for ChildInfo and MoreInfo and offers those just as expected as subfields in the main table. I understand that this option was added sometime later and the default wasn’t set to true for compatibility reasons (although it would make perfect sense in most cases).

Regarding the recursion depth, this comes to play in more complex structures where lists have sublists that have sublists etc. (think of an employee class with a List Subordinates member. The hierarchy could just go on for ever, thus it needs to stop somewhere. In your case, it just shouldn’t make much difference as long as you don’t set it to “0”.

HTH

G.

Thanks for the info. If I set FlattenStructure to true on the ObjectDataProvider, I get an OutOfMemory exception thrown by LL.

Well, in that case I’d raise an issue with combit support directly. Just double checked, the option works for me in a simple test scenario.

G.

Let me show the actual classes I’m using and the results I get using different values for the recursion depth.

This is my report data class:

public class EmployeeVisitorReportData
{
public Visitor Visitor { get; set; }
public PersonName EmployeeName { get; set; }
public string ReasonForVisit { get; set; }
public string CategoryName { get; set; }
public DateTimeOffset EntryTimeIn { get; set; }
public DateTimeOffset EntryTimeOut { get; set; }
public string CustomId { get; set; }
}

Visitor is a LINQ-to-SQL entity class, with a number of simple types that map to a SQL Server database table.

PersonName is a class that has two get/set properties: FirstName and LastName. It also has a few read-only properties that format the first and last name. The read-only property names are:

  • FirstNameLastName
  • FirstInitialLastName
  • LastNameFirstName
  • LastNameFirstInitial

So here’s where my understanding of the ObjectDataSource and the recursion depth stuff falls flat. If I set the FlattenStructure setting to true, I get an OutOfMemory exception, regardless of the recustion depth value.

If I set the recustion depth to 0, and then design a report with an instance of the EmployeeVisitorReportData class, while adding the report container object I choose Table and have two entries to choose from:

  • Free content
  • EmployeeVisitorReportData

If I choose EmployeeVisitorReportData then I can see these fields available for me to drop onto the report:

  • CategoryName
  • CustomId
  • EntryTimeIn
  • EntryTimeOut
  • ReasonForVisit

The Visitor and EmployeeName fields have been removed from the object for some reason. I can drop these 5 fields onto the report and they work correctly and display all the data correctly. But why can’t I access Visitor and EmployeeName?

If I design the report using the same technique just described, except use a recursion depth of 1, I am presented these fields that I can add to the report:

  • Free content
  • EmployeeName
  • EmployeeVisitorReportData
  • Visitor

If I pick EmployeeName, I can choose from all the properties in the PersonName class, but cannot see EmployeeVisitorReportData or Visitor so cannot place them on the report. Why is that? If I choose the Visitor field, I can see all of those fields just fine but cannot see the EmployeeName and EmployeeVisitorReportData fields. Why can’t I see the whole record structure?

If I expand the EmployeeVisitorReportData node I am shown the EmployeeName and Visitor fields. If I choose EmployeeName I’m allowed to add EmployeeName’s 6 fields and EmployeeVisitorReportData’s 5 fields onto the report, but the Visitor field is nowhere to be seen. Why? Likewise, if I choose Visitor rather than EmployeeName from the EmployeeVisitorReportData node, I can place any of the 5 EmployeeVisitorReportData fields on the report and/or all 50 (or so) of the Visitor fields on the report, but now EmployeeName is nowhere to be seen. Why?

If I pick a recusion depth of 2 or higher I seem to get a deeper picture of the LINQ-to-SQL structure, but I don’t know what the purpose of that is because none of those deeper levels are populated with data with the query I am using. Seems to me the only useful recursion depth level is 1, at least with a LINQ-to-SQL query as the data source. Do I understand this correctly?

I know this is a long description (wish I could post screen shots here to simplify this) but don’t know how else to describe the issues I’m seeing without this long example. I’ve highlighted my questions above, if that helps any.

Is the idea that I have to use a flat data structure and cannot have a hierarchy of properties for my report data? But then why can I sometimes see two hierarchies (e.g., EmployeeVisitorReportData + Visitor) but not all three hierarchives (e.g., EmployeeVisitorReportData + Visitor + EmployeeName)? If I have to use a “flat” class that won’t work so well because some of these fields, like Visitor, has many properties that would have to be defined and then populated.

Thanks for any help understanding how this is supposed to work.

Hi Glenn,

You really want to set FlattenStructure to true and if that throws an exception your best bet will be to contact support with a small sample - that’s what I always do for quick solutions.

Regarding your description, you’ll probably see the child fields if you added a child item underneath the parent table EmployeeVisitorReportData by clicking the “Append sub element” from the report container window. However you wouldn’t be able to access both parent and child fields easily this way. That’s exactly what the FlattenStructure switch is for.

BTW: I seem to be able to attach files here, there’s a checkbox right above the “OK” button.

G.

Hi, Günther, thanks again for your help.

This is interesting. I just finished updating our code base to use LL 19 (we were at LL 18). And after your comment figured I try the FlattenStructure again. It works now!

Now when I highlight EmployeeVisitorReportData in the Choose Source Table Path in the designer it just shows Visitor as a child, probably because Visitor is a relation whereas EmployeeName is just a class. In any event, I highlight EmployeeVisitorReportData, click OK, and now all my fields are there: the direct “simple” properties of EmmployeeVisitorReportData, the Visitor LINQ-to-SQL entity class, and the EmployeeName plain-old class.

And if I set the recursion depth to 0, then even the “noise” of the relations being listed goes away and our users now see a nice, clean list of fields from our report data source coming directly from the LINQ-to-SQL query. (I’ve tried attaching a small screenshot - thanks for the pointer about that checkbox.)

I think I’m starting to grok the recursion depth and flatten options. Thanks for the help!

Capture.PNG

Hi Glenn,

Great to hear it works now - my pleasure.

G.