Using Entity Framework for a Master Detail Report

[sumo]

Hi there,

I have a .net core web project which uses entity framework to access data from a sql server database. I would like to know how I can pass the data from an entity framework db call to a stimulsoft report, before I view the report .
I searched through the git hub examples but I could not find anything similar and I'm stuck.

If you could explain in some simple steps how I can pass a simple result (one db table) from entity framework core and pass this to a report for preview, I'm pretty sure I could work out the rest.

Any help will be greatly appreciated as I have been going in circles.

[Lech Kulikowski]

Hello,

You can retrieve data to the DataSet and then provide it to the report with the RegData() method.

Thank you.

[sumo]

Thanks Lech,

I have managed to retrieve the data from entity framework and convert it to a DataSet.
Is it possible to replace a Sql Server DataSource and TableName with a DataSet? I have tried this but I could not get it working with the DataSet.

var report = new StiReport();
DataSet ds;
IEnumerable<Variety> varieties;

report.Load(StiNetCoreHelper.MapPath(this, "Reports/VarietiesReport.mrt"));
report.Dictionary.Databases.Clear(); // I was hoping this would be the magic line of code needed

varieties = _context.Varieties.OrderBy(x => x.VarietyName); // Use Entity Framework to collect and sort the data
ds = varieties.ToDataSet("IGrowApp", "LkpVarietyTbl"); // this creates a DataSource and DataTable with matching names used in the report. The Entity Framework result is converted to a DataSet with an Extensions class I developed.
report.RegData(ds);
return StiNetCoreViewer.GetReportResult(this, report);

I can confirm that the DataSet works correctly because I tried it on another report where I copied my Variety class to a separate ClassLibrary project and then used this as a Business Objects (via the Data from Business Objects Wizard) using the same code above, just a different report name. The report displayed correctly with the required result from the DataSet.
Any further help is much appreciated.

Thank you.

[sumo]

Okay, so after some further digging the code below now works. I had to call DataSources.Clear() as well, then call report.Dictionary.Synchronize() after registering the DataSet. I hope this is the correct way to do this?
This now allows me to quickly design a report on my dev machine pointing directly at the sql server database so I can see the data when previewed, then I can quickly drop the report into the web project and provide the data from my controller via entity framework.

Someone reading this may ask, why did I not just choose a direct sql server connection?
I chose an Entity Framework context over a direct sql connection, because its a mult-tenant web app where one user may access a completely separate tenant database. The EF DataContext automatically handles the connection to the db based on the authenticated user, so I don't have to worry about it, all I have to do is use the EF context to fill a DbSet etc, it auto handles what db it should come from. Having the ability to point the report directly at a development sql database and then deploy it to test>production with a small amount of code required to hook it up to EF will save me a lot of time.

var report = new StiReport();
DataSet ds;
IEnumerable<Variety> varieties;

report.Load(StiNetCoreHelper.MapPath(this, "Reports/VarietiesReport.mrt"));
report.Dictionary.Databases.Clear();
report.Dictionary.DataSources.Clear();

varieties = _context.Varieties.OrderBy(x => x.VarietyName);
ds = varieties.ToDataSet("IGrowApp", "LkpVarietyTbl");
report.RegData(ds);
report.Dictionary.Synchronize();
return StiNetCoreViewer.GetReportResult(this, report);

[sumo]

I think it would be a good idea if the guys at Stimulsoft built an example using Entity Framework to collect data for a Stimulsoft Master/Detail Report. Apologies if you have already done this. This may save some users many hours of work/grief down the track as EF has been around for ages and I could not find an example on your GitHub or any decent examples online, which I think would be a lot more relevant for businesses than accessing data from XML flat files etc.

I did see others with the same issue and converting an Entity Framework result to a DataSet is not an easy task, especially if the Model classes and Database support Nullable data types, which a strongly .Net DataSet does not support unless you look into adding DataTable columns with the property AllowDBNull = true for Nullable data types.

Cheers and have a good day.

[Lech Kulikowski]

Hello,

As a way, you can try to use business objects:

StiReport report = new StiReport();
varieties = _context.Varieties.OrderBy(x => x.VarietyName);
report.RegBusinessObject("varieties ", varieties);
report.Dictionary.SynchronizeBusinessObjects();

Thank you.