Using LINQ as a ActiveReports DataSource

In my trials of trying to complete a whole project using LINQ and the OR/M solution, I came against a brick wall in ActiveReports. 

As part of the DataInitialize event, you typically iterate through the columns of your DataTable/DataView and pick out the columns—which then become the fields in your report (the textboxes have a DataField property that matches). The code looks similar to this:

private void ExceptionReport_DataInitialize(object sender, EventArgs e)

{

DataTable dt = this.DataSource as DataTable;

foreach (DataColumn column in dt.Columns)

{

Fields.Add(column.ColumnName);

}

}

Well, that’s fine and dandy; however, our anonymous type var doesn’t have “columns” to speak of… but, is it necessary to “loop through” the DataSource since we’re explicitly new’ing up our data using LINQ?  Let’s assume so.   We discussed the other day that we can create a DataTable by looping through our variable object, so would that work in this case?  Yes, it would!

MyDataContext db = new MyDataContext();

var exceptionRecords = from i in db.Exceptions

where i.Year == year

orderby i.ExceptionTypes.ExceptionTypeName descending

group i by i.ExceptionTypes.ExceptionTypeName into grouping

select new

{

Count = grouping.Count(),

ExceptionTypeName = grouping.Key

};

 

DataTable dt = new DataTable();

dt.Columns.Add(new DataColumn(“Count”, typeof(Int32)));

dt.Columns.Add(new DataColumn(“ExceptionTypeName”, typeof(string)));

      

foreach (var record in exceptionRecords)

{

DataRow dr = dt.NewRow();

dr[“Count”] = record.Count;

dr[“ExceptionTypeName”] = record.ExceptionTypeName;

dt.Rows.Add(dr);

}

 

this.DataSource = dt;

Now, our DataInitialize event will fire just fine—because we’re using a DataTable as the DataSource.  But that’s a bit hokey—it requires reevaluating your data, additional loops, and doing everything twice in the event your data schema changes—a horrid DRY violation.

So, to our prior question—is it necessary to iterate through our DataSource if we’re new’ing up our data in LINQ?  Let’s go for the NO this time.  Look at our LINQ sample below:

MyDataContext db = new MyDataContext();

var exceptionRecords = from i in db.Exceptions

where i.Year == year

orderby i.ExceptionTypes.ExceptionTypeName descending

group i by i.ExceptionTypes.ExceptionTypeName into grouping

select new

{

Count = grouping.Count(),

ExceptionTypeName = grouping.Key

};

 

this

.DataSource = incidentRecords.ToList();

This example queries our data context, matching exactly what we’re looking for in the database and new’ing up our data into a new var object.  In addition, we’re setting the DataSource equal to our the ToList() of our var.  As far as I can tell, this is required.  Simply pushing the anonymous type to the reporting engine will return a null exception.  The ToList works like a champ.

We already know we have two columns here—Count and ExceptionTypeName.  Why not simply add the fields inside the ReportStart event. 

this.Fields.Add(“Count”);

this.Fields.Add(“ExceptionTypeName”);

With this, we can remove DataInitialize and simply rely on ReportStart and details_Format to read, add columns, and populate our report.

private void detail_Format(object sender, EventArgs e)

{

Type.Text = Fields[“ExceptionTypeName”].Value.ToString();

       Count.Text = Fields[“Count”].Value.ToString();

       Percent.Text = String.Format(“{0:0.##%}”,

 (float)((int)Fields[“Count”].Value / countOfExceptions));

}

There we go, instant report with less coding and more LINQing!  I’m sure future versions of ActiveReports will support the new LinqDataSource or, perhaps, more intutitive ways of handling the Fields, but this works for now—and is very speedy.

[update 16:27: For a single record, your LINQ query cannot explicitly return a single record and still convert your DataSource to a List using the .ToList() method.  For this example (and to use ToList() to convert your DataSource), skip the .Single and simply return “all found records”.  A list of 1 record, for now, is still OK.

Instead of :

var exceptionRecord = (from i in db.Exceptions

where i.ExceptionId == exceptionId

select i).Single();

 

DataSource = incidentRecord.ToList(); <— FAILS

Use:

var exceptionRecord = from i in db.Exceptions

where i.ExceptionId == exceptionId

select i;

 

DataSource = incidentRecord.ToList(); <— WORKS

Tags: activereports, linq, datadynamics