locked
The query results cannot be enumerated more than once RRS feed

  • Question

  • Hi Guys,

    I have a DataContext class which I copy and paste from a dbml generated code, and modified it to include caching of result data.  Below is one of the method I have used on my testing:


           
    Code Block

    [Function(Name = "dbo.usp_LkUp_GetMaritalStatus")]
            public ISingleResult<GetMaritalStatusResult> GetMaritalStatus()
            {
                ISingleResult<GetMaritalStatusResult> status;

                if (CacheHelper.GetValue<ISingleResult<GetMaritalStatusResult>>("LkUp_MaritalStatus") == null)
                {
                    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
                    status = ((ISingleResult<GetMaritalStatusResult>)(result.ReturnValue));
                    CacheHelper.Add("LkUp_MaritalStatus", result.ReturnValue);
                }
                else
                    status = CacheHelper.GetValue<ISingleResult<GetMaritalStatusResult>>("LkUp_MaritalStatus");

                return status;
            }



    I try to iterate through the records by using foreach statement:

    Code Block

    foreach (GetMaritalStatusResult ccode in test.GetMaritalStatus())
        Response.Write("status : " + ccode.MaritalStatus + ccode.StatusID.ToString() + "<Br/>");


    This code block is located inside the page load event.  I then tried refreshing page by pressing CTRL F5, so this should give me the result data from cache, since it is the second time the method was called.  But what I got instead was "The query results cannot be enumerated more than once" error.  

    I tried binding the resultset to a dropdownlist, and it works fine on every refresh, it is able to read the data from cache.  It appears that the error only occurs when I try to iterate manually.

    Any ideas?

    Thanks,
    Enzo


    Wednesday, January 2, 2008 6:59 AM

Answers

  • Or if you don't care about the sproc return value (ISingleResult.ReturnValue) you might just simply cache the result of status.ToList() directly, so that the cache value is a list.

     

    Wednesday, January 2, 2008 8:42 PM
  • Your cache works well and caches the output of stored procedure correctly. The problem is that you have cached the ISingleResult<T> output of your stored procedure instead of its results collection (as a List<T> or T[]). The ISingleResult<T> output of LINQ to SQL cannot be enumerated more than once. If you enumerate it for the second time you will get exception.

    When you bind the ISingleResult<T> to your dropdown-list for the first time, the results are enumerated and the drop-down list is populated. When you bind the same ISingleResult<T> instance to the same control again, it detects that the data-source is the same as its last source (by comparing their object references) and does not enumerate the ISingleResult<T> instance for the second time.

    To overcome your problem, you sould implement your own ISingleResult<T> as a caching wrapper for the ISingleResult<T> generated by LINQ to SQL. See below:

        public class WrapperResult<T> : ISingleResult<T>
        {
            #region Fields

            List<T> results;
            object returnValue;

            #endregion

            #region Constructor

            public WrapperResult(ISingleResult<T> inner)
            {
                results = inner.ToList();
                returnValue = inner.ReturnValue;
            }

            #endregion

            #region IEnumerable<T> Members

            public IEnumerator<T> GetEnumerator()
            {
                return results.GetEnumerator();
            }

            #endregion

            #region IEnumerable Members

            IEnumerator IEnumerable.GetEnumerator()
            {
                return ((IEnumerable)results).GetEnumerator();
            }

            #endregion

            #region IFunctionResult Members

            public object ReturnValue
            {
                get { return returnValue; }
            }

            #endregion

            #region IDisposable Members

            public void Dispose()
            {
            }

            #endregion
        }


    Wednesday, January 2, 2008 9:14 AM

All replies

  • Your cache works well and caches the output of stored procedure correctly. The problem is that you have cached the ISingleResult<T> output of your stored procedure instead of its results collection (as a List<T> or T[]). The ISingleResult<T> output of LINQ to SQL cannot be enumerated more than once. If you enumerate it for the second time you will get exception.

    When you bind the ISingleResult<T> to your dropdown-list for the first time, the results are enumerated and the drop-down list is populated. When you bind the same ISingleResult<T> instance to the same control again, it detects that the data-source is the same as its last source (by comparing their object references) and does not enumerate the ISingleResult<T> instance for the second time.

    To overcome your problem, you sould implement your own ISingleResult<T> as a caching wrapper for the ISingleResult<T> generated by LINQ to SQL. See below:

        public class WrapperResult<T> : ISingleResult<T>
        {
            #region Fields

            List<T> results;
            object returnValue;

            #endregion

            #region Constructor

            public WrapperResult(ISingleResult<T> inner)
            {
                results = inner.ToList();
                returnValue = inner.ReturnValue;
            }

            #endregion

            #region IEnumerable<T> Members

            public IEnumerator<T> GetEnumerator()
            {
                return results.GetEnumerator();
            }

            #endregion

            #region IEnumerable Members

            IEnumerator IEnumerable.GetEnumerator()
            {
                return ((IEnumerable)results).GetEnumerator();
            }

            #endregion

            #region IFunctionResult Members

            public object ReturnValue
            {
                get { return returnValue; }
            }

            #endregion

            #region IDisposable Members

            public void Dispose()
            {
            }

            #endregion
        }


    Wednesday, January 2, 2008 9:14 AM
  • Or if you don't care about the sproc return value (ISingleResult.ReturnValue) you might just simply cache the result of status.ToList() directly, so that the cache value is a list.

     

    Wednesday, January 2, 2008 8:42 PM


  • Thanks guys, I'll try both suggestions.

    Thanks,
    Enzo
    Thursday, January 3, 2008 1:16 AM

  • ISingleResult<SP_ObtieneConexExchangeResult> result = bdContext.SP_ObtieneConexExchange(nuevoPerfil.IdPerfil, "");     
                                       
    var OUS = from ous in bdContext.TBL_OU_ADs     
    where     
    ous.Activa == true    
    && ous.OU == result.First<SP_ObtieneConexExchangeResult>().Conexion  
    select ous;     
        
    foreach (TBL_OU_AD ou in OUS)     
    {     
     //Code     
    }    
     


    ISingleResult<SP_ObtieneConexExchangeResult> result = bdContext.SP_ObtieneConexExchange(nuevoPerfil.IdPerfil, "");  
     
    string OUConex = result.First<SP_ObtieneConexExchangeResult>().Conexion.ToString();  
                                    
    var OUS = from ous in bdContext.TBL_OU_ADs  
    where  
    ous.Activa == true 
    && ous.OU == OUConex   
    select ous;  
     
    foreach (TBL_OU_AD ou in OUS)  
    {  
     //Code  

    I solve them changing my code. I create a new variable string, and I store in my new var the value of the variable that can't be enumerate more once. Try it and good luck!



    Monday, February 23, 2009 11:10 AM
  • Try this and it should work. I had the same issue. I just copied the result into a List object and then used the list object to loop through (iterate) and it works fine.

     

    //-- Data context to execute the Onyx stored procedure
    OnyxSprocsDataContext context = new OnyxSprocsDataContext();  

    //-- Execute the qpe stored procedure and get the census data
    var qpeImportCensusResult = context.qpexSpImportCensusXml(importCensusInputXml);

    //-- Copy the result from the stored proc into a list object to avaoid the ""The query results cannot be enumerated more than once." error
    List<qpexSpImportCensusXmlResult> qpeImportCensusResultList = new List<qpexSpImportCensusXmlResult>(qpeImportCensusResult);  

    //-- Check to make sure the result from the stored proc is not null/empty.
    if (qpeImportCensusResultList != null && qpeImportCensusResultList.Count() > 0)
    {
        StringBuilder builder = new StringBuilder();
        foreach (qpexSpImportCensusXmlResult r in qpeImportCensusResultList)
       {
           builder.Append(r.XML_F52E2B61_18A1_11d1_B105_00805F49916B);
       }
     
       qpeImportCensusXmlString = builder.ToString();
    }  

    //-- Dispose the data context
    context.Dispose();

    Thursday, July 30, 2009 7:15 PM
  • I used parts of your solution and its a great answer, well done!
    Wednesday, August 26, 2009 12:00 PM
  • Vinodh Mylvaganam

    Thanks, I'm searching for this answer about forth months, this really solved my problem!
    Thursday, January 14, 2010 12:42 PM
  • Try this and it should work. I had the same issue. I just copied the result into a List object and then used the list object to loop through (iterate) and it works fine.

    //-- Data context to execute the Onyx stored procedure
    OnyxSprocsDataContext context = new OnyxSprocsDataContext();  

    //-- Execute the qpe stored procedure and get the census data
    var qpeImportCensusResult = context.qpexSpImportCensusXml(importCensusInputXml);

    //-- Copy the result from the stored proc into a list object to avaoid the ""The query results cannot be enumerated more than once." error
    List<qpexSpImportCensusXmlResult> qpeImportCensusResultList = new List<qpexSpImportCensusXmlResult>(qpeImportCensusResult);  

    //-- Check to make sure the result from the stored proc is not null/empty.
    if (qpeImportCensusResultList != null && qpeImportCensusResultList.Count() > 0)
    {
        StringBuilder builder = new StringBuilder();
        foreach (qpexSpImportCensusXmlResult r in qpeImportCensusResultList)
       {
           builder.Append(r.XML_F52E2B61_18A1_11d1_B105_00805F49916B);
       }
     
       qpeImportCensusXmlString = builder.ToString();
    }  

    //-- Dispose the data context
    context.Dispose();

     

    Hello Vinodh Mylvaganam,

    I am having trouble implementing your solution in VB.net

    I have the following code:

    Dim

     

    jobHistoryXmlResult = (jobHistoryLinqToSql.GetJobHistoryXML(xml))

     

    Dim jobHistoryXmlResultResultList As New List(Of qpexSpImportCensusXmlResult)(jobHistoryXmlResult)


    but I get the error: - type qpexSpImportCensusXmlResult is not defined

    What type should I use for the list?

    Thanks.
    Tuesday, January 19, 2010 4:39 PM
  • Hi,

     

    I found .ToList() to be the best solution.

    I was calling a sproc like

     

    double? valueReturn;

    var resultsReturned = DataBaseContext.StoreProcedure1(StartDate, EndDate).ToList();

    foreach(var item in resultsReturned)

    {

         valueReturn = valueReturn + item.ColumnName;


    }

     

     


    Kind Regards, Kevin Heathfield Cape Town, South Africa, ^5
    Monday, January 17, 2011 9:24 AM
  • Deat Vinodh,

     

    Thanks a lottttttttttttttttttttttt.

     

    It really solved my problem.

    Thursday, February 17, 2011 6:51 AM
  • Try this and it should work. I had the same issue. I just copied the result into a List object and then used the list object to loop through (iterate) and it works fine.

     

    //-- Data context to execute the Onyx stored procedure
    OnyxSprocsDataContext context = new OnyxSprocsDataContext();  

    //-- Execute the qpe stored procedure and get the census data
    var qpeImportCensusResult = context.qpexSpImportCensusXml(importCensusInputXml);

    //-- Copy the result from the stored proc into a list object to avaoid the ""The query results cannot be enumerated more than once." error
    List<qpexSpImportCensusXmlResult> qpeImportCensusResultList = new List<qpexSpImportCensusXmlResult>(qpeImportCensusResult);  

    //-- Check to make sure the result from the stored proc is not null/empty.
    if (qpeImportCensusResultList != null && qpeImportCensusResultList.Count() > 0)
    {
        StringBuilder builder = new StringBuilder();
        foreach (qpexSpImportCensusXmlResult r in qpeImportCensusResultList)
       {
           builder.Append(r.XML_F52E2B61_18A1_11d1_B105_00805F49916B);
       }
     
       qpeImportCensusXmlString = builder.ToString();
    }  

    //-- Dispose the data context
    context.Dispose();

    .ToList(); does exactly this --> List<qpexSpImportCensusXmlResult> qpeImportCensusResultList = new List<qpexSpImportCensusXmlResult> !?

    Reduce code lines and increase performance


    Kind Regards, Kevin Heathfield Cape Town, South Africa, ^5
    Thursday, June 9, 2011 5:15 AM