Using Linq to SQL and Sql Reporting Services

Question

I have a question regarding the integration of business objects developed using Linq To Sql for data query and integrating with Sql Server Reporting Services.

We have a set of business objects that query a couple of back end databases that is developed with Linq to SQL. The SQL that gets generated is relatively dynamic (based on the conditions the user selects) and involves multiple joins, some inner, some outer, etc. Linq to SQL worked great for this. However, we ran into issues when we tried to implement reports with SQL Reporting Services after the initial roll out. We didn't have the ability to bind the SSRS reports to our business layer. What we essentially ended up doing is getting the SQL that is executed from SQL Profiler and creating stored procedures, and using the stored procedures in the reports. As one can imagine, this became a problem as we maintained the code, needing to update both our business layer and the stored procedure.

I have done some looking and I see that Custom Data Extensions appear to be an approach to do this. Is this the solution to the problem? Does anyone have a better approach? Are there any example of implementing a solution like this using LINQ?

http://www.devx.com/dbzone/Article/31336

Thanks

Answer 1

BTW, you don't need to use Profiler to see the generated SQL.

var query = ( from c in db.Customers where c.LastName = "Someone" select c );

// output the query SQL Debug.WriteLine(query);

Return query.ToList();

Alternatively, what we did was to hook into DataContext's Log property. This writes out our SQL and parameters automatically everytime we hit the database. We have found this very useful to identify unnecessary database calls.

public class DataBase : DataBaseModelDataContext
{
    internal DataBase()
    {
    }

    public DataBase(CommonObjects.BaseParameters param) {
        #If (DEBUG) 
        Log = new DataBaseLoger();
        #endIf //(DEBUG) 
    }

    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {   
        System.Data.Linq.ChangeSet cs = GetChangeSet();

        // update audit fields for each insert
        foreach (object entity in cs.Inserts)
        {
            UpdateAuditFields(entity);                               
        }

        // update audit fields for each update
        foreach (object entity in cs.Updates)
        {
            UpdateAuditFields(entity);
        }

        base.SubmitChanges(failureMode);
    }
}

public class DataBaseLoger : System.IO.TextWriter { public override Encoding Encoding { get { return new System.Text.UTF8Encoding(); } }

    public override void WriteLine(string value)
    {
        System.Diagnostics.Trace.WriteLine(System.DateTime.Now.ToString("hh:mm:ss") + " " + value, "Information");
    }

    public override void WriteLine()
    {
        System.Diagnostics.Trace.WriteLine("", "Information");            
    }

    public override void WriteLine(string format, params object[] arg)
    {
        WriteLine(string.Format(format, arg));
    }
}

Answer 2

Use the ReportViewer control in local mode, against an ObjectDataSource which in turn uses a simple class with "Get" methods, each returning IEnumerable<ClassNeededForReport>.

Sample doing the above (minus Linq): http://msdn.microsoft.com/en-us/library/ms251692(VS.80).aspx

Just write your "Get" method to use Linq, optionally doing a .ToList() if required.

Answer 3

Just wanted to close the loop on this a little bit...

We have worked through implementing this with a LINQ to SQL application, but it should work fine with EF also.

Essentially, it's laid out in the devx article listed above.

http://www.devx.com/dbzone/Article/31336

There are several things we ran into, one of which is the need to "flatten" our data. We have custom routines to flatten the data into a row set that can be consumed by the SSRS reports. You will also need to pay attention to the setup instructions in the article above.

Just a reminder, we need to use the Web Service functions of SSRS in our implementation. If you can utilize the local reports, it's much easier. If you are interested in local reports with your domain model, here is a good series I just ran across that uses nHibernate with SSRS.

http://codebetter.com/blogs/peter.van.ooijen/archive/2009/07/01/reporting-against-a-domain-model.aspx

http://codebetter.com/blogs/peter.van.ooijen/archive/2009/07/08/domain-driven-reports-adding-custom-code.aspx

John