none
Stored Procedure with Multiple Result sets of Complex Types RRS feed

  • Question

  • Hi,

    I have a stored procedure which return 3 results sets all of which are of complex types.

    dbo.Procedure

    -- This stored procedure first populates @VehicleRunIDs which is a user defined table type having just one integer column 

    --  @VehicleRunIDs  is then passed to 3 different procedures which returns 3 different result sets of complex types. Each result set have different set of columns.

    EXEC dbo.Procedure1 @VehicleRunID;
    EXEC dbo.Procedure2 @VehicleRunID;
    EXEC dbo.Procedure3 @VehicleRunID;

    To consume this stored proc in my WPF application, i have entity framework 6. Using entitycontext I call this sp.

    In my edmx file, I have 3 different Complex types (type1,type2,type3) which correspond to the return types of Procedure1 ,Procedure2 and Procedure3.

    I have also modified Return Type node for base 'Procedure' and also provided 3 ResultMapping.

    I have followed these links -

    https://msdn.microsoft.com/en-US/data/jj691402

    https://www.codeproject.com/articles/675933/returning-multiple-result-sets-from-an-entity-fram

    Now in my code I have created a new type MYType having 3 properties of List<type1> l1, List<type2> l1, List<type3> l3

    Now I have below code

        var result_VRInfo = this.ExecuteStoreQuery<type1>(sql, parameters.ToArray()); -- parameters is the list of sqlparameters 

                MYType finalResult = new FinalVRResult();
                finalResult.l1= new List<type1>();
                finalResult.l2 = new List<type2>();

                    finalResult.l3= new List<type3>();

                var vrInfo = result_VRInfo;

                finalResult.l1.AddRange(vrInfo );

                var riderInfo = vrInfo.GetNextResult<typ2>();

    -- At this point riderinfo is coming as null where as finalresult.l1 has results as expected.

    Kindly provide some help and let me know if this is even possible.

    Friday, March 3, 2017 10:43 AM

All replies

  • Hi pirates07,

    Based on your description, your case more related to EF Stored Procedure. I will move your case to Ef forum for better support.

    Best regards,

    Kristin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 6, 2017 1:41 AM
  • Hi pirates07,

    If you use code first, Once we have an ObjectContext then we can use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal, like this:

     using (var db = new BloggingContext())
        {
            // If using Code First we need to make sure the model is built before we open the connection
            // This isn't required for models created with the EF Designer
            db.Database.Initialize(force: false);
    
            // Create a SQL command to execute the sproc
            var cmd = db.Database.Connection.CreateCommand();
            cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]";
    
            try
            {
                
                db.Database.Connection.Open();
                // Run the sproc 
                var reader = cmd.ExecuteReader();
    
                // Read Blogs from the first result set
                var blogs = ((IObjectContextAdapter)db)
                    .ObjectContext
                    .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);   
    
    
                foreach (var item in blogs)
                {
                    Console.WriteLine(item.Name);
                }        
    
                // Move to second result set and read Posts
                reader.NextResult();
                var posts = ((IObjectContextAdapter)db)
                    .ObjectContext
                    .Translate<Post>(reader, "Posts", MergeOption.AppendOnly);
    
    
                foreach (var item in posts)
                {
                    Console.WriteLine(item.Title);
                }
            }
            finally
            {
                db.Database.Connection.Close();
            }
        }

    If you use Database first, please refer the following steps

    1. right click on your model and select Open With.. then Xml

    2. Find the complex type and function import in your model

    3. Remove the complex type

    4. Update the function import so that it maps to your entities

    5. Find the function mapping element:

    6. Replace the result mapping with one for each entity being returned

    7. retrieve the result like this:

    using (var db = new BlogEntities())
        {
            var results = db.GetAllBlogsAndPosts();
    
            foreach (var result in results)
            {
                Console.WriteLine("Blog: " + result.Name);
            }
    
            var posts = results.GetNextResult<Post>();
    
            foreach (var result in posts)
            {
                Console.WriteLine("Post: " + result.Title);
            }
    
            Console.ReadLine();
        }

    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/jj691402(v=vs.113).aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, March 7, 2017 6:09 AM
    Moderator
  • This answer doesn't actually work with Complex Types.
    Tuesday, December 18, 2018 10:38 PM
  • This answer doesn't actually work with Complex Types.

    You can do what is in the link


    https://docs.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets

    The above link also shows how to  use IObjectContextAdapter that will allow you to use the EF backdoor, use ADO.NET, MS SQL Command objects , use a datareader with multiple resultsets being  read and you can use the DTO pattern to return complex types.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/ 

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp



    Wednesday, December 19, 2018 5:38 AM