Click here to Skip to main content
13,249,813 members (65,854 online)
Rate this:
Please Sign up or sign in to vote.
I am trying to retrieve data from stored procedure in entity framework code first model. But while I try it gives me an error “The underlying provider failed on an open” for the inner list of data.
            using (ProductDbContext dbUserContext = new ProductDbContext())
                listProducts = dbUserContext.Database.SqlQuery<products>("SP_GetAllProducts");
                dbUserContext.Database.Initialize(force: false);
                var dbProdCmd = dbUserContext.Database.Connection.CreateCommand();
                dbProdCmd.Connection = dbUserContext.Database.Connection;
                dbProdCmd.CommandText = "SP_GetAllProducts";
                dbProdCmd.CommandType = CommandType.StoredProcedure;
                    // dbUserContext.Database.Connection.Open();
                    var prodReader = dbProdCmd.ExecuteReader();
                    listProducts = ((IObjectContextAdapter)dbUserContext)
                   .Translate<products>(prodReader, "Products", MergeOption.AppendOnly).ToList();
                //((IObjectContextAdapter)dbUserContext).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
                return View(listProducts);

Stored Procedure:
CREATE PROCEDURE [dbo].[SP_GetAllProducts]
	-- Add the parameters for the stored procedure here
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    -- Insert statements for procedure here
	SELECT * FROM [DBO].[Products]
	SELECT * FROM [DBO].[prd1]
	SELECT * FROM [DBO].[prd2]
	SELECT * FROM [DBO].[prd3]
	SELECT * FROM [DBO].[prd4]
	SELECT * FROM [DBO].[prd5]
	SELECT * FROM [DBO].[prd6]

Model: Model structure is something like this:
using System.Collections.Generic;
using System;
using System.Data.Entity;
namespace Project.Models
    public class Products
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime CreationDate { get; set; }
        public DateTime ModificationDate { get; set; }
        public virtual ICollection<t> prd1 { get; set; }
        public virtual ICollection<t> prd2 { get; set; }
        public virtual ICollection<t> prd3 { get; set; }
        public virtual ICollection<t> prd4 { get; set; }
        public virtual ICollection<t> prd5 { get; set; }
    public class ProductContext : DbContext
        public DbSet<t> prd { get; set; }
        public DbSet<t> prd1 { get; set; }
        public DbSet<t> prd2 { get; set; }
        public DbSet<t> prd3 { get; set; }
        public DbSet<t> prd4 { get; set; }
        public DbSet<t> prd5 { get; set; }

Please reply ASAP.

[Edit]Code block added[/Edit]
Posted 5-Jan-13 5:33am
Updated 5-Jan-13 5:36am
Zoltán Zörgő 6-Jan-13 6:09am
Any progress?
Rohit Kesharwani 7-Jan-13 4:01am
As above we have mentioned our Stored Procedure structure, there are multiple select queries for different tables to fetch records.

If we use join then will it resolve our issues?

Please let me know your thoughts and suggestations.


1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Your stored procedure will return 7+1 different result sets. I am pretty sure that EF won't be able to handle this even if all have the same fields and all can be mapped to the entity you want. Check this to see how multiple result sets can be handled in EF5:[^]
If you use EF before v5, I suggest you use UNION between the seven select statements and, and leave nocount on. This way you will be forced to provide union compatibility between the seven selections.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171114.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100