locked
Multiple Result Sets from Stored Procedures RRS feed

All replies

  • Hi TomBean,

    Welcome to the MSDN forum.

    Note:The schema that is used depends on the version of Visual Studio that the model is created in. Please refer to this page: http://msdn.microsoft.com/en-us/data/jj650889

    Thus, you are required to use Visual Studio 2012 to upgrade to edmx 3.0 which supports multiple result sets.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, November 27, 2012 2:51 AM
  • Alexander,

    If you check the article I mentioned, you will find that the only version of Visual Studio mentioned is 2010.  The article never mentions Visual Studio 2012 but is obvious the ADO.NET Entity Data Model used is not the one in Visual Studio 2010.  I thought the author of the article, who I believe is Rowan Miller, had upgraded the ADO.NET Entity Data Model before he wrote the article.

    Because of all the poor documentation and bad examples, if that's what this is, the Entity Framework is the most confusing and difficult thing I've ever seen in my 25 years of developing software.

    Check the article and let me know what you think.

    Thanks


    TomBean

    Tuesday, November 27, 2012 3:00 AM
  • Hi TomBean,

    Unfortunately you can't use multiple result sets in VS 2010. The reason is that the EF runtime that has support for multiple result sets is part of .NET 4.5, which you cannot target in 2010. So you will need VS 2012 to use it. Once EF 6 ships then this will work in 2010 again, as we will have moved all of EF out of the .NET framework and into the EF assembly.

    Having said that, multiple result sets is not really a first class feature yet. The runtime supports it, but the tooling is still lagging behind. Even in 2012 you need to edit xml to get it to work. because of that we can hardly call it a great new feature, which is why it is not called out and emphasized as much as other features.

    The documentation page for EF is here: http://msdn.microsoft.com/en-us/data/ee712907

    You should refer to that over any documentation on CTP versions of EF, the RTM version of document for multiple result sets is here (http://msdn.microsoft.com/en-us/data/jj691402). In this case I think the CTP was released to get feedback and ideas about the feature before it was known that it would not be available on 4.0.

    I'd like to hear more about your experience with the documentation and finding information on what you wanted. We have been improving our documentation a lot lately, but we still have a ways to go. So if you wanted to talk to me more about what type of documentation you would like to see or what topics you think are missing then I am happy to listen. You can reply here or email me at glenncATmicrosoft.com

    -Glenn


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Tuesday, November 27, 2012 10:49 PM
  • Glenn,

    Rather than continue to try to find out why the article I referenced regarding how to implement multiple result sets in Visual Studio 2010, I upgraded to Visual Studio 2012 and .NET 4.5.

    When I set my Target framework to 4.5, the .edmx file was converted to use EDMX V3 but "xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" />" was appended to each EntitySet line so I decided to regenerate all my Entity Data Models with VS 2012.

    When I finished resolving the multiple errors caused by all the .edmx files being put in the same name space until I set the Custom Tool Namespace for each .edmx and .tt file, I got errors in a couple of *Context.cs files for a few DbSet entries, for example  "error CS0246: The type or namespace name 'Note' could not be found (are you missing a using directive or an assembly reference?)".

    I searched the Internet and got over 7000 hits and the only ones that seemed to pertain to my situation were ones that said add a reference to EntityFramework.dll and I already had one.

    A big part of my frustration is that when I follow the advice I get and switch to a different version of Visual Studio, I get errors that should:  1) be handled by the code generator;  2) provide more information about the problem the tools created.

    There were no such errors in the Visual Studio 2010 version of my project but it wouldn't handle multiple result sets while Visual Studio 2012 will supposedly handle multiple result sets but won't generate code that will compile.

    Do you have any suggestions about what the error means or how I can find what the compiler doesn't like about the generated code?

    Thanks


    TomBean

    Wednesday, November 28, 2012 1:58 AM
  • The reason for the errors are because tables with the same name, e.g. Note, are in two databases and the generated code was created in the root folder of the project.  When the .cs file was created for the table, the last one generated overwrote the previous one.

    As soon as I added an entity data model, I set the custom tool namespace for files so they are all database specific, however, this did not cure the problem of the .cs files for tables with the same name from being overwritten.

    In order to resolve the problem, I had to create subfolders for each entity data model and move the .edmx files to their subfolder.

    It would have been nice if some warning about the generated files being overwritten was given or even better, if the code generator created the subfolders for the entity data models.

    Thanks


    TomBean


    • Edited by TomBean Wednesday, November 28, 2012 5:52 PM
    Wednesday, November 28, 2012 4:41 PM
  • I must be missing something here. I can't seem to reproduce the behavior you are describing. I want to reproduce the behavior that you are seeing and fix it in EF. Possibly in the manner that you have suggested.

    I just created three edmx models in VS 2012 in the root of a console application, all pointing at different databases with a blog and post table, and there are no build errors.

    If you expand your EDMX file in solution explorer do you see a .tt file for the model?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, November 28, 2012 6:53 PM
  • Glenn,

    Under each of my .edmx files I have a DBNameDataModel.Context.tt, a DBNameDataModel.Designer.cs, a DBNameDataModel.edmx.diagram, and a DBNameDataModel.tt.

    In two of my databases, I have a table named Notes.  When the ADO.NET Entity Data Model was added for the first database, everything was all right but the Note.cs file was in the root folder of my project.  Once the entity data model was added, I changed the Custom Tool Namespace to Project.DBNameDataModel.

    When I added the entity data model for the second database, the Note.cs was also put in the project's root folder and overwrote the one from the first database.

    I had started a WinForm application so I could add my entity data modesl from scratch to make sure the problems I was seeing weren't because of the upgrade from Visual Studio 2010 to 2012.  I added the models for the databases that had errors and, like you, I didn't get any build errors.  However, when I looked in my project's root directory, there was only one Note.cs file and the Note.cs file had been removed from the list of files in DBNameDataModel.tt.  The entries for Note were also removed from the first .edmx file I added.

    I think if you check your project's root directory, you will only find one .cs file for the tables with the same name.


    TomBean

    Wednesday, November 28, 2012 7:25 PM
  • Hi Tom,

    This is an issue with the code generation templates (http://entityframework.codeplex.com/workitem/690). Although the generated classes are nested under the EDMX in solution explorer, on disk they are stored in the same folder as the EDMX files. If both EDMX files generate a Note.cs then it ends up being the same file on disk and the definition gets overwritten by the last model to perform code generation. The workaround for this bug is to put one of your EDMX files in a subfolder or a separate project. 

    The reason we haven't spent the time to fix this bug is that there's a more fundamental issue with having two models with the same entity name (http://entityframework.codeplex.com/workitem/483). When EF looks at the xml of your EDMX-based model it needs to work out which class to use for the Note entity, the only information that it has is the name of the entity so it looks in your assembly for a class named Note. When it sees two classes it fails because it doesn't know which one to use. This issue only applies when you are using POCO classes (that is, simple classes without any additional attributes etc. to let EF know how they are used by your model). In VS2012 the EF Designer generates POCO classes by default - we decided the benefits of simple class definitions didn't outweigh this issue as not very many people have two models in the same assembly with the same entity names. More importantly there are a number of ways to work around the limitation:

    • Put your models in separate assemblies/projects (in bigger applications this is typically what folks do anyway).
    • In the EF Designer rename the entity in one of the models. Because EF maintains a mapping to the database the name of the table can stay the same. When you save the model, EF will re-run code generation and a class with the updated name will be generated.
    • Swap back to non-POCO code generation (http://msdn.microsoft.com/en-us/data/jj556581) - EF will add a bunch of attributes to your classes so that it knows which class belongs to which model.

    We do plan to enable support for POCO classes with the same name in the same assembly but it is a complicated feature to enable (purely because of how EF is implemented).

    Hope this helps provide some clarification and ways to workaround this limitation.

    ~Rowan


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    • Proposed as answer by Rowan Miller Friday, November 30, 2012 6:59 PM
    • Unproposed as answer by TomBean Monday, December 31, 2012 5:58 PM
    Friday, November 30, 2012 6:59 PM
  • Rowan,

    I had already moved each of the EDMX files into a subfolder named after the data model and changed the Custom Tool Namespace thinking that would solve the problems.  But, those changes didn't work because the namespace is ignored.  However, I believe the reason "not very many people have two models in the same assembly with the same entity names" is because it doesn't work rather than that they don't have tables of the same name in different databases.

    I assume there was a good reason for switching from the ObjectContext to the DbContext.  Was it done for improved performance?  Wouldn't the same technique of adding "a bunch of attributes" to classes work in the DbContext?

    In other words,  what would I be giving up if I switched back to non-POCO code generation?

    I have resolved the problem using a different method than any you suggested.  I didn't want to because of additional problems that will be caused but I renamed the tables causing the problem in one of the databases.

    Thanks


    TomBean

    Friday, November 30, 2012 9:16 PM
  • Hi Tom,

    DbContext is a newer and much simpler API surface, everything you can do with DbContext can also be done with ObjectContext... but it often takes a lot more complicated code. Most folks prefer the simpler entity classes too - they are much closer to what you would write if you wrote them by hand (they also make things like serialization, data binding, etc. easier because they don't have a bunch of additional properties and attributes on them). I would highly recommend taking one of the other workarounds over reverting back to ObjectContext.

    Renaming the database table is a valid workaround... but I didn't list it because it's not something most people would be willing to do (my self included :)).

    ~Rowan


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, November 30, 2012 10:02 PM
  • Rowan,

    Before I found I needed multiple result sets in a stored procedure, I was using ObjectContext with Visual Studio 2010 and it was working fine and didn't seem complicated at all (compared to stored procedures).

    My real concern is about performance.

    Does the DbContext perform better than the ObjectContext?  If so, would you give me guesstimate of the performance improvement?

    Thanks


    TomBean

    Friday, November 30, 2012 11:31 PM
  • Hi Tom,

    No, there isn't a performance benefit using DbContext (DbContext is actually just an API façade over ObjectContext).

    You will get some serious performance improvements if you target .NET 4.5 though - http://blogs.msdn.com/b/adonet/archive/2012/04/05/ef5-performance-considerations.aspx. Based on that I would recommend sticking with VS2012 and .NET 4.5, you'll get the same performance improvements regardless of whether you use DbContext or ObjectContext.

    All our tutorials etc. these days use DbContext, so that may be a reason to use it over ObjectContext.

    I'm not sure if you saw the 'Accessing Multiple Result Sets with Code' section in this article that shows one way you can use multiple result sets without having to edit the EDMX manually. - http://msdn.microsoft.com/en-us/data/jj691402.

    ~Rowan


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Saturday, December 1, 2012 1:09 AM
  • Rowan,

    I'm using a Silveright client.  Are there any examples for Silverlight?

    Thanks


    TomBean

    Monday, December 3, 2012 6:33 PM
  • I added a service reference to the Entity Data Servce with my stored procedure that has multiple result sets and there is no reference to the stored procedure in the Silverlight's service.edmx.

    I also noticed the schema of the Silverlight service reference is "http://schemas.microsoft.com/ado/2007/06/edmx".  That is older than the Visual Studio 2012 version and if I'm not mistaken, it is Version 1.0.

    Is there a way to get Silverlight to see the stored procedure and the multiple result sets?

    Thanks


    TomBean

    Tuesday, December 4, 2012 1:53 AM