Issue when using Visual Studio Report Designer with Linq to SQL datasource

Question

I don't expect to get a great response from this, so if you have had experience in this any contribution would be appreciated.

I am trying to use the Visual Studio Report Designer with a Linq to SQL datasource, I have no experience of using the Visual Studio Report Designer.

If I go through the wizard and use an object datasource I can get a simple report listing a single entity type (i.e. sql table) easily enough. My questions is how do I get the report to accept complex Linq queries with Joins, Sums etc. that have an anonymous return type. If I try this at the moment I get a compile time error from the report designer when trying to use the dot notation or aliased names to get to the fields that I need to reference in the results of the query. I think this is because the designer is expecting the same type as was specified in the wizard...

The error is:-

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Maybe I'm better off using DataSets?

Answer 1

Write a simple class containing all the properties your report will need (call it eg. MyData). Use simple .NET types for each property: string, int, etc

Write another class with a GetXxx method, which returns IEnumerable<MyData>. This Get method should return the results of your Linq query. You can do a .ToList() before returning, to get an in-memory list, allowing you to close the connection before returning.

Compile.

Now open your report designer (in same solution), and you should see MyData listed in your Data Sources window, with the Get method which you wrote.

If you drag the fields from this data source onto your report designer, and then refer to this report from a new ReportViewer control, the ObjectDatasource will be automatically created and linked up for you.

Hope that helps!