none
Noob How to: ExecuteQuery sql statement RRS feed

  • Question

  • Hi I'm just re-learning sql and wanting to learn linq too.

    My Class:

    public  class DogCollection : ObservableCollection<Dog>

    {

          public DogCollection()  : base()

                {

                  WPFsandbox6.Properties.Settings settings = new WPFsandbox6.Properties.Settings();

                  string strConn = settings.DogBreedsConnectionString;

                  DogBreedsDataContext dc = new DogBreedsDataContext(strConn);

    try

    {

      object[] objects = new object[0];

      var query = dc.ExecuteQuery<Dog>(@"select db.name, db.description,       a.description as attributes from dogbreeds db

          left join dogattributeXref dax on db.breedid = dax.breedid

          left join attributes a on dax.attributeid = a.attributeid", objects );

     

      foreach (Dog d in query)// here is the error

      {

        //string image = @"../../media/" + d.Image;

        Add(d);

      }

    }

    catch (Exception ex)

    {  MessageBox.Show(ex.ToString()); }

     

          }    

    public ObservableCollection<Dog> Dogs

    {     get { return this; }    }

     

    }

    note: Forgive the terrible formatting please or if any braces are ill placed; the code does compile.

     

    Although query is filled with the dog objects I need, when I try ‘foreach’ to add to the base class I get the error:

    ---------------------------

    System.InvalidOperationException: The query results cannot be enumerated more than once.

       at System.Data.Linq.SqlClient.SqlProvider.OneTimeEnumerable`1.GetEnumerator()

       at WPFsandbox6.DogCollection..ctor() in C:\Documents and Settings\username\My Documents\Visual Studio 2008\Projects\WPFsandbox\WPFsandbox6\Dog.cs:line 42

     

    I don’t understand where it is thinking query is enumerated. It is only filled with data.

    Thanks in advance for any insight.

     

    Friday, April 4, 2008 8:58 PM

Answers

  • This probably just caused by the debugger 'looking' at the 'query' variable and enumerating it to display its values in the watch window before you get to the foreach. The result of ExecuteQuery is an IEnumerable wrapped over a ADO DataReader and it can only be enumerated once since the DataReader can only be read once.

     

    You can always turn the result into a List using ToList();  dc.ExecuteQuery(...).ToList();

     

     

     

    Friday, April 4, 2008 9:06 PM
    Moderator

All replies

  • This probably just caused by the debugger 'looking' at the 'query' variable and enumerating it to display its values in the watch window before you get to the foreach. The result of ExecuteQuery is an IEnumerable wrapped over a ADO DataReader and it can only be enumerated once since the DataReader can only be read once.

     

    You can always turn the result into a List using ToList();  dc.ExecuteQuery(...).ToList();

     

     

     

    Friday, April 4, 2008 9:06 PM
    Moderator
  • Yes. I get no messagebox/error if I just run the program without stepping threw.

     I get a list box that contains as of yet empty images which I will correct soon.

    I never knew the debugger would cause such an error. Go figure.

     

    I think I might try your list suggestion if after filling in the image fields in the db., I draw a blank.

    I am not very familiar with the var classs/keyword.

    Thanks a lot.

    Saturday, April 5, 2008 1:00 PM
  • This probably just caused by the debugger 'looking' at the 'query' variable and enumerating it to display its values in the watch window before you get to the foreach. The result of ExecuteQuery is an IEnumerable wrapped over a ADO DataReader and it can only be enumerated once since the DataReader can only be read once.

     

    You can always turn the result into a List using ToList();  dc.ExecuteQuery(...).ToList();

     

     

     

    Is the same thing with ToArray as well?
    I need it because I want to Load items from CreateSourceQuery, I was wondering if ToArray executes the same, so I don't have to use ToList which I am sure it costs more performance to initialize a List than an Array, I am discarding the result anyway.

    Shimmy
    Wednesday, December 30, 2009 4:00 PM