Ticket T317718

Visible to All Users

Bind XtraReport to a stored procedure through EF

kirsten greed

created 9 years ago (modified 9 years ago)

[DevExpress Support Team: CLONED FROM T317573: EF Connection string for xaf ReportDataV2]
No, thats not it because it doesnt give me the ability to bind to the output of a stored procedure.

My Stored Proc returns a list of string results

I tried the following

C#
      class ReportsDB : jtDbContext  
    {  
        public IList<SPResultString> CancelOrders()  
        {  
            return GetSpResults("TestOrders");  
        }  
    }  

where in jtDBContext I have  

            public IList<SPResultString> GetSpResults(string spName)  
        {  
            return  Database.SqlQuery<SPResultString>(spName).ToList();  
        }  


and the SPResultString is  

        public class SPResultString  
    {  
        public string Result { get; set; }  
    }

When I try to use ReportsDB the StoredProc result will not appear.
http://screencast.com/t/MphMJYfRYIp6

Answers approved by DevExpress Support

Yaroslav (DevExpress Support)

created 9 years ago (modified 9 years ago)

Hi Kirsten,
I believe the following article is exactly what you're looking for: How to: Bind a Report to an Entity Framework Data Source. Try to accomplish the steps mentioned in that tutorial and let me know if there are any difficulties with it. I also would like to emphasize that while you can create and show a regular report using our built-in data access solution, it is NOT compatible with our XAF framework. A similar issue has been already discussed in the Setting criteria for defined reports thread raised by you: you tried to create a report using the DataSourceWizard with the help of the EFDataSource component, and it worked, but the ReportServiceController's criterion was not applied to the report.
Regards,
Yaroslav.

Show previous comments (20)

kirsten greed 9 years ago

Thanks Yaroslav.
I tried following the instructions at the link
When I browse to the dll that contains the context I still run into trouble
http://screencast.com/t/TPY8iThrCn

kirsten greed 9 years ago

I aren't sure how to add a stored proc to a code first DBContext… so I guess I might just stick with XPO.

Yaroslav (DevExpress Support) 9 years ago

The error you encountered is rather unexpected and without additional information it's almost impossible to say what might be the underlying cause of this behavior. I can confirm though that our EFDataSource component fully supports Entity Framework version 5.0 and higher. BTW, Kirsten, how do you bind your report to that stored procedure? Are you designing the report at design time in Visual Studio or through the XAF application UI facilities? If it's about the second case, I'd like to remind that only two specialized data sources (ViewDataSource and CollectionData) are supposed to be used in a XAF application. And even though you should be able to design an XtraReport of any complexity using all available report features in Visual Studio, mixing two ORMs in the context of a single project is probably not the best choice.

kirsten greed 9 years ago

Thanks Yaroslav, At the moment the report is being done at design time. I will look for some guidance on how to hook a stored procedure into the code first context.

kirsten greed 9 years ago

checking this out
http://stackoverflow.com/questions/4845246/does-entity-framework-code-first-support-stored-procedures

Yaroslav (DevExpress Support) 9 years ago

Thank you for the clarification, Kristen. I see what you mean. No, we do not support such a scenario - your stored procedure has to be imported from a database and mapped to the EDM. Of course, you can always subclass the DbContext and expose the query as a public property available for data-binding.
Regards,
Yaroslav

kirsten greed 9 years ago

I tried your suggestion and edited the question to show my attempt.

kirsten greed 9 years ago

My idea of reverting to xpo hasn't worked out either, as the xafml designers seem to get upset if i mix EF and XPO in a solution.
T319346
T316356

Yaroslav (DevExpress Support) 9 years ago

>>My idea of reverting to xpo hasn't worked out either, as the xafml designers seem to get upset if i mix EF and XPO in a solution.
Yes, some of the classes are used both by XPO and EF, and this causes the designer crash. In any case, it seems my colleagues have already helped you overcome this issue.

>>I tried your suggestion and edited the question to show my attempt.
I think my last comment was somewhat obscured. Please accept my apologies for this. The approach I suggested is about an object binding approach, i.e. a scenario where you discover the target class schema to initialize the binding design time, and at runtime you supply the actual data to the report (How to: Bind a Report to a List Object at Design Time and Provide Data at Runtime). However, here all the work of fetching data from the server side and binding it to the report should be completely done manually. Just be to clear: all stored procedures you see in the DataSourceWizard must be defined in your EDMX model using the corresponding CSDL elements. It should look like this:

XML
<EntityContainer Name="NorthwindEntities" annotation:LazyLoadingEnabled="true" >  
       <FunctionImport Name="MyStoredProcedure1" ReturnType="Collection(NorthwindModel.Orders)" />  
</EntityContainer>

You should either manually define this container for entity sets and function imports, or use the auto-generated functionality described in the How to: Import a Stored Procedure MSDN help topic. But then again, I'm afraid this won't work if we're taking about the Code First approach.

kirsten greed 9 years ago

There is no .edmx report in my project. I could add one just for reporting… but my understanding is that EF will be phasing out .edmx files

Yaroslav (DevExpress Support) 9 years ago

Yes, that's true, Kristen. EF 7 is going to give up on EDMX in favor of code-based modeling. We will certainly take a closer look at this issue with its official release to see how our own DataSource Wizard can be improved, but at the moment we've not yet established any plans regarding that.
Feel free to contact us at any time.

kirsten greed 9 years ago

Thanks Yaroslav, this will be important for EF users like me.

Dennis Garavsky (DevExpress) 9 years ago

Hi Kirsten,

I want to emphasize that you can still achieve your ultimate goal (create a report based on a stored procedure result set) with our components today. For instance, you can use the XtraReport's SqlDataSource with the XAF ReportsV2 module as per How to use SqlDataSource with ReportsV2 and pass the application connection string into the data source?
Since you are eventually going to use the raw SQL to get your data (from stored procedures), it practically does not matter whether you get this data from ORM wrappers (e.g., in EF you can call DbContext.Database.SqlQuery<YourEntityType>("storedProcedureName", params) or by other means, because 1) after all you will not be using the main benefits your ORM offers (= an object-oriented approach instead of the direct SQL); 2) you will get the same result.

Moreover, it seems that the use of abstractions like SqlDataSource (take special note that it is not an XPO component, but rather something that uses a shared API from our Data library, which is also used by XPO) that seamlessly supports many popular database engines with almost no effort from your side and will be much easier to implement than configuring EF and using its wrappers like the method above I took from StackOverFlow. So, if you are not pursuing EF just for the sake of using EF-based methods all over your app, I suggest you try the approach above. Please let us know what you think. Thanks.

kirsten greed 9 years ago

Ah so in summary, the steps to being able to easily build a report, using the report wizard and an EF Stored proc are

Add a new item using the Report Wizard
select data-bound report
select Database ( NOT Entity Framework)
work through the screens setting up the data connection
Select Stored Procedure
Work through the rest of the report designing
Move the connection string to the right app.config
Edit the connection string to be similar to the other application connection strings ( i.e it does not need the xpoProvider )

Dennis Garavsky (DevExpress) 9 years ago

Hi Kirsten,
Your steps look correct. See also my help link above if you want to use the main XAF application connection string for your report. Should you experience any XAF integration difficulties, submit a separate ticket and attach your test project.

kirsten greed 9 years ago

More reminders for myself.
I had to register the predefined report
also i had to add a bindingsource in the report.
https://documentation.devexpress.com/#XtraReports/CustomDocument7547
then to run the report i had to set the xtraReport.Datasource before showing it.

kirsten greed 9 years ago

More reminders…
Sample Run Report
var rep = new ReportShippingManifest();
var xtraRep = (XtraReport)rep;
var dt1 = new DateTime(2016, 1, 1);
var dt2 = dt1.AddDays(7);
xtraRep.DataSource = db.ShippingManifests(dt1, dt2);
xtraRep.ShowPreview( );

kirsten greed 9 years ago

More reminders…
Sample Prepare Data
inside the DBContext class
public IList<SPShippingManifest> ShippingManifests(DateTime fromDate, DateTime toDate)
{
var param1 = new SqlParameter { ParameterName = "@StartDate", Value = fromDate };
var param2 = new SqlParameter { ParameterName = "@EndDate", Value = toDate };
var results = Database.SqlQuery<SPShippingManifest>("jtRptManifest @StartDate, @EndDate", param1, param2);
var output = results.ToArray();
var list = output.ToList();
return list;
}
SPShippingManifest is a non persisted business object with a structure identical to the output of the report.

kirsten greed 9 years ago

More reminders for myself
Use the report designer to set the bindingsource.datasource property to SPShippingManifest

kirsten greed 9 years ago

Studying this
https://www.devexpress.com/Support/Center/Question/Details/Q576385

Vasily (DevExpress Support) 9 years ago

Hi Kirsten,

Your last comment is not completely clear to me. You gave a link to this ticket there, but I do not see any new questions. Would you please clarify this? Maybe, you have provided the incorrect link.

kirsten greed 9 years ago

Thanks Vasily the comment should have read
Asked a new question on how to set the datasource after the user has set the parameters
https://www.devexpress.com/Support/Center/Question/Details/T332285
I will delete the comment.

Vasily (DevExpress Support) 9 years ago

Thank you for the clarification, Kirsten!
I would like to note that we have already posted an answer to the T332285: Show report parameters dialog prior to setting data for report ticket.
Let us know if you need any further assitance. We will be happy to help you.

Created: November 27, 2015 8:54 AM

Modified: January 22, 2016 9:51 AM

Platform: Reporting WinForms

Product: Reporting for WinForms

Build: 15.1.8

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Bind XtraReport to a stored procedure through EF

Answers approved by DevExpress Support

Recently viewed tickets