Entity Framework Dynamic Report

• Download EFDynamicReport-master.zip - 55.6 KB

Introduction

Since Entity Framework 6.1 you can use EF metadata in order to reveal information about DB structure. You can find good explanation of such approach here:  Mapping Between Types & Tables.

In this article I would like to show how you can easily create dynamic reports based on EF metadata.
The main idea is to collect from EF mapping the information about required tables and columns and then build SQL query which will return all necessary for customer information.

The main advantage of such approach is:

• Separation between your reports and DB Structure.

• Сode reuse, information about DB will be taken from Entity Framework mapping.

• Easy way to extend your reports.

I created a small library which use described approach and could simplify work with dynamic reports. All source codes available in GitHub: EFDynamiReport

Below I will describe how you can quickly setup your dynamic report within this small library, which could be easily added inside your project

Using the code

1. Setup your Entity Framework mappings.

This article do not cover all details about using of Entity Framework but you can find many usefull articles here.
For our demo project we will use simple DB schema which contains information about students in school.

public class Student
{
    [Key]
    public int StudentId { get; set; }

    public string FirstName { get; set; }

    //some code removed for brevity...
}

public class SchoolDbContext : DbContext
{
     public DbSet<Student> Students { get; set; }

    //some code removed for brevity...
}

2. Configure Report Template

For each report we should declare what columns will be available in it.
In our case for this purpose we use report template class StudentsReport which contains information about the all columns which will be available in report.

public class StudentsReportTemplate : ReportTemplate
{
    ///some code removed for brevity...

    public override IEnumerable<IReportColumn> ReportColumns
    {
        get
        {
           return new List<IReportColumn>
                {
                    StudentTable.Column("First Name", x => x.FirstName),
                    StudentTable.Column("Last Name", x => x.LastName),
                    StudentTable.Column("Phone", x => x.Phone),
                    StudentTable.Column("Home Adress", x => x.HomeAdress),
                    new AverageScore(queryExtractor, StudentTable).Column("Average Score"),
                    new MinimumScore(queryExtractor, StudentTable).Column("Minimum Score"),
                    new MaximumScore(queryExtractor, StudentTable).Column("Maximum Score"),
                    new Age(StudentTable).Column("Age"),
                    new Subjects(queryExtractor, StudentTable).Column("Subjects")
                };
        }
    }

    public override IReportDataSource ReportDataSource
    {
        get { return StudentTable.GetReportDataSource(); }
    }
}

Each statement is declaring one of the possible report columns.
For example: StudentTable.Column("First Name", x => x.FirstName) is declaring that report can contain students names.

If you will build report with only one "First Name" column, the result SQL query will be look like this:

SELECT 
    s.FirstName 
FROM 
    [dbo].[Students] AS s

3. Create report and get report data from it

After we configured Report Template we can create report model from it.

StudentsReportTemplate template = new StudentsReportTemplate(...);

//Report Template create IReportModel object which do not depend from concrete report template.
IReportModel reportModel = template.CreateReport();

In order to get report data we specify what columns and filters we want to see in report (all columns should exists in report, see step #2 for more details)

var filters = new IReportFilter[0];    
var columns = new IReportColumn[]
{
    new ReportColumn { Title = "First Name"}
}   

var data =  reportModel.Get(columns, filters)

For example report about the students averages scores will look like this one: 

Result SQL query will look like this:

SELECT 
    ( SELECT AVG(e.Score)  FROM ExamenResults as e WHERE e.StudentId = s.StudentId) AS AverageScore,
    s.FirstName AS FirstName 
    s.LastName AS LastName
FROM 
    FROM [dbo].[Students] AS s

History

You can find all source codes in GitHub: EF Dynamic Report