Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
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;
 
	 //((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();
	
                dbProdCmd.CommandText = "SP_GetAllProducts";
                dbProdCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    // dbUserContext.Database.Connection.Open();
                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();
 
                    dbUserContext.Database.SqlQuery<products>("SP_GetAllProducts");
 
                    var prodReader = dbProdCmd.ExecuteReader();
 
                    listProducts = ((IObjectContextAdapter)dbUserContext)
                   .ObjectContext
                   .Translate<products>(prodReader, "Products", MergeOption.AppendOnly).ToList();
                    prodReader.Dispose();
                }
                finally
                {
                    //dbUserContext.Database.Connection.Close();
                    //((SqlConnection)dbUserContext.Database.Connection).Close();

                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Close();
                }
                //((IObjectContextAdapter)dbUserContext).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
                return View(listProducts);
            }
Stored Procedure:
CREATE PROCEDURE [dbo].[SP_GetAllProducts]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;
 
    -- 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]
END
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.
Thanks.
 
[Edit]Code block added[/Edit]
Posted 5-Jan-13 5:33am
Edited 5-Jan-13 5:36am
ProgramFOX127.9K
v3
Comments
Zoltán Zörgő at 6-Jan-13 6:09am
   
Any progress?
Rohit Kesharwani at 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.
 
Thanks,

1 solution

Rate this: bad
good
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: http://msdn.microsoft.com/en-US/data/jj691402[^]
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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 490
1 Maciej Los 305
2 Richard MacCutchan 270
3 Mathew Soji 220
4 BillWoodruff 210
0 OriginalGriff 8,834
1 Sergey Alexandrovich Kryukov 7,477
2 DamithSL 5,689
3 Maciej Los 5,329
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2014
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