How to handle Entity-Data Source with Navigation Properties?

[David]

I'm building an executive summary report that uses a single group. I only need a summary, so the Details section is hidden, and I only populate the group header and footer with aggregate data (see the reportdesigner.png attachment).  I have brute-forced a solution by manually computing my summary values, the first page of the resulting report is in the reportcapture.png attachment.

The problem is that I will be doing a similar report that is organized the same way, but given it will be generating charts from the details rather than just computing summary information, my current code-behind brute-force solution likely won't work as well.  And I'm thinking that I shouldn't have had to brute-force it in the first place.

The background is that I have a set of Entity-Data Framework objects projected from a simple SQL database.  The two tables in question are a table of Circuits (mapped to Circuit objects in C#), and a master table of logged voltage/power entries.  Each power entry was measured by a meter (and has a relation to yet another table of Meters) and was of course measured from a specific Circuit. 

EDF has projected an ObjectSet of Circuits, which is my DataSource for the report.  Within each Circuit object, EDF made me a Navigation Property called LoggedACMeterDatas.  I group the report based on the Circuit.id (the table key).  So each group header has as its "focus" a specific Circuit.  Hence the "Fields.Name" and "Fields.Circuit.Location.Name" resolve to the correct thing on a single Circuit object.

The problem is now I want to access the LoggedACMeterDatas (an EntityCollection that is projected from a Foreign-Key Relation), in order to fill in some summary information in the report.  For example, the "Period Start" and "Period End" are the earliest and latest timestamp in the LoggedACMeterDatas .  The power summary fields are each an average of a specific field across all the LoggedACMeterDatas for the current Circuit in the grouping.

I would have liked to build an Expression along the lines of Min(Fields.LoggedACMeterDatas.Timestamp) for the Period Start.  After all, specifying Fields.Name in the group header (which in the context of the entire report is a collection, but in the context of the group is a scalar) seems to do the right thing.  But I could find no way to do that with field that is an EntityCollection.  Similarly, for the power summaries, something like Avg(Fields.LoggedACMeterDatas.VoltagePhase1) to get an average of all the VoltagePhase1 members of the sub-collection.  In short, I can specify Fields.LoggedACMeterDatas in an Expression, but there seems to be precious little I can do with it.

So my brute-force was to add the following properties to the Circuit class, essentially doing the above in code-behind, where I can do something useful with the LoggedACMeterDatas member:

public Double AverageVoltagePhase1
{ get { return (from entry in LoggedACMeterDatas select entry.VoltagePhase1).Average(); } }

public Double AverageVoltagePhase2
{ get { return (from entry in LoggedACMeterDatas where entry.VoltagePhase2.HasValue select entry.VoltagePhase2.Value).Average(); } }

public Double AverageVoltagePhase3
{ get { return (from entry in LoggedACMeterDatas where entry.VoltagePhase3.HasValue select entry.VoltagePhase3.Value).Average(); } }

public Double AverageCurrentPhase1
{ get { return (from entry in LoggedACMeterDatas select entry.CurrentPhase1).Average(); } }

public Double AverageCurrentPhase2
{ get { return (from entry in LoggedACMeterDatas where entry.CurrentPhase2.HasValue select entry.CurrentPhase2.Value).Average(); } }

public Double AverageCurrentPhase3
{ get { return (from entry in LoggedACMeterDatas where entry.CurrentPhase3.HasValue select entry.CurrentPhase3.Value).Average(); } }

public Double AverageWattsPhase1
{ get { return (from entry in LoggedACMeterDatas select entry.WattsPhase1).Average(); } }

public Double AverageWattsPhase2
{ get { return (from entry in LoggedACMeterDatas select entry.WattsPhase2).Average(); } }

public Double AverageWattsPhase3
{ get { return (from entry in LoggedACMeterDatas select entry.WattsPhase3).Average(); } }

public Double AveragePowerFactor
{ get { return (from entry in LoggedACMeterDatas select entry.AvgPowerFactorAllPhases).Average(); } }

public DateTime StartPeriod
{ get { return (from entry in LoggedACMeterDatas orderby entry.Timestamp select entry.Timestamp).First(); } }

public DateTime EndPeriod
{ get { return (from entry in LoggedACMeterDatas orderby entry.Timestamp select entry.Timestamp).Last(); } }

So as I said above, the brute-force workaround is ok in this case, but it seems like I should have been able to do all this in the realm of the report.  Secondly, I don't see how I'm going to be able to build a Chart instead of a table of scalar averages---I'm going to need some mechanism by which I can hand off the EntityCollection to the Chart.  But I don't see how I can do that.

Any help appreciated...

[David]

Some additional information and thoughts...

To some extent I thought maybe I could solve this by using something that could accept the LoggedACMeterDatas as it's primary DataSource---a Master/Detail pattern in other words.  I'm only aware of two ReportItems that accept a DataSource property:

• Sub-Reports
• Table/Crosstab

Certainly for the start/end dates, a Sub-Report is a sledgehammer of a solution.  I'd just as soon use my two one-liner LINQ expressions in the code-behind.  The power summary area is more report-ish, but again, a Sub-Report seems like a lot of work for something that should be simple.

I tried getting Tables to work, but I couldn't find a way to make the Table DataSource point to something that I could then use to compute the data I was looking for.  And the more I read about Tables, the more I realized they are more complex than I thought (in a good way).  So maybe the answer lurks there, but I couldn't get it to work.  I'd be perfectly happy to be shown the magic expression that gets a Table to Do The Right Thing(tm) for this report.

The other thought I had was maybe a User-Function that would transform the EntityCollection<LoggedACMeterData> into something more palatable to the Report engine.  I tried a function that returned IEnumerable<LoggedACMeterData>, but I couldn't get any farther along than that.  Maybe an IEnumerable isn't the right thing.  I'd also be perfectly happy with that approach, since it should scale to my next problem which is creating Charts for each Circuit.  Charts are naturally going to want to be handed collections of data from which to create Series, but they don't seem to like EntityCollection. 

As always, any insight would be helpful.

[Steve]

Hi David,

To make my answer more clear, let's start with a simple entity model like the following one:



The above entity model is based on the Adventure Works sample database and contains only two entities: ProductModel and Product with a 'one-to-many' relationship between them. Now, let's create a report that displays all products, grouped by model and computes the average price of the products for each model. To do this, we need to create an EntityDataSource component that binds the report to the Product entities and add a single group in our report that groups by ProductModel. Then we can use an expression like the following one in the group footer to compute the average price:

= Avg(Fields.ListPrice)

Please, check the sample report attached to this post for a better illustration. In case you need only the aggregated data (the average price in this particular example), you can simply hide the detail section of the report (set the Visible property to false), and you'll get a report displaying only the summarized results.

From your explanation in the forums I've got the impression that you are trying to bind your report to the master entities (the ProductModel entities according to our example) and then you need to summarize the detail entities somehow (the Product entities according to our example). However you cannot do that, because there is no binding to the detail entities in your report, hence you resort to custom methods to accomplish your goal. While this is a valid approach in general, it seems like too much work to get the job done in your particular case. Instead, you can bind your report to the detail entities (the Product entities) and use the corresponding navigation property to get the master entities (the ProductModel entities). Now you can simply group by the master entities and summarize the detail entities, because you have all that data available in your report. This is not so much different from what you'll normally do when querying the same data from the database with a SQL or a LINQ query, only that the reporting engine computes the grouping and the aggregates automatically for you.

I hope the above explanation is enough to resolve your problem.

Kind regards,
Steve
the Telerik team

[David]

So essentially---"Don't do it that way"

Fair enough.  I was hoping to structure the reports the same way EDM allows the code to work: drill-down from the top using Navigation Properties.  But the Reporting Engine wants the more traditional database-y way of doing things: drill-up from the bottom.

My mindset is more of O-O data structures, which make EDM useful to my way of approaching problems.  But I can work the other way round if that's the way the Reporting Engine wants to do it.  I was hoping to stay top-down, and also avoid some issues with ObjectSets going out of sync with the database, but I can work around all of that.

One more thing: most of the time I'm going to use my EDM-based EntityDataSource just at Design Time.  At run-time I allow the user to filter the data and just hand over an Array.  For example, I have a Page which allows the user to select which Circuits will be in the report.  Here's the code-behind that launches the ReportViewer:

{
    ReportViewWindow window = new ReportViewWindow();
    PowerSummaryReport psr = new PowerSummaryReport();
    window.DataContext = psr;
    psr.DataSource = (from circuitFilter in CircuitFilterList.CircuitFilterList.Circuits
                    where circuitFilter.IsEnabled
                    select circuitFilter.Circuit).ToArray();
    window.Owner = this.Parent as Window;
    window.ShowDialog();
}

As you can see, I'm overriding DataSource just after I create the PowerSummaryReport to be the filtered set of Circuits rather than just the entire Circuits table (which is what DataSource is set to at Design Time).  How far along does the PowerSummaryReport() constructor take things? 

Ideally it wouldn't create a whole new EDM Context and new database connection since I plan to override the DataSource anyway.  But I like being able to use the Context as I'm building the report, for obvious reasons.

Thanks again.

[Ivan]

Hello David,

Using navigation properties is convenient but costly, because each access to a navigation property can potentially lead to executing an additional query against the database to materialize entities that are not already cached in memory. Since reports typically operate on large subsets of data you might end with many round-trips to the database during the generation of a single report. To avoid this you should write a single (LINQ) query against your entity model to retrieve all data necessary for the report at once. The recommended approach is to extend your ObjectContext with a partial class and add a method there that executes the query. This way you can bind your EntityDataSource to the method instead of directly to the auto-generated property. This is discussed more in-depth in the following help topic from the online documentation: Retrieving data from an Entity Data Model with the EntityDataSource component.

Keeping the above in mind, you can place the LINQ query from your code snippet in a method of the ObjectContext, so you can continue using the EntityDataSource component with your report just by binding it to that method. If you decide later to parametrize your query, you can add arguments to that method and configure the EntityDataSource to pass report parameters to them, for example. A good discussion on passing parameters to methods with EntityDataSource can be found in the following help topic from our documentation: Using parameters with the EntityDataSource component.

Regarding your question about maintaining the lifecycle of the object context, you have two options:

1. You can pass the Type of an ObjectContext to the EntityDataSource component. In this case a separate instance of that ObjectContext is created for the sake of the report and then destroyed automatically, when it is no longer needed by the reporting engine.

2. You can pass an instance of an ObjectContext to the EntityDataSource component. In this case the provided instance of the ObjectContext is reused when generating the report. However, then it is your responsibility to maintain the lifecycle of the provided ObjectContext instance properly.

This is explained in a greater detail in the following topic of the documentation: Maintaining the lifecycle of the ObjectContext with the EntityDataSource component.

Greetings,
Ivan
the Telerik team