Switching from LinqToXYZ to LinqToObjects

Question

In answering this question, it got me thinking...

I often use this pattern:

collectionofsomestuff //here it's LinqToEntities
    .Select(something=>new{something.Name,something.SomeGuid})
    .ToArray() //From here on it's LinqToObjects
    .Select(s=>new SelectListItem()
        {
            Text = s.Name, 
            Value = s.SomeGuid.ToString(), 
            Selected = false
        })

Perhaps I'd split it over a couple of lines, but essentially, at the ToArray point, I'm effectively enumerating my query and storing the resulting sequence so that I can further process it with all the goodness of a full CLR to hand.

As I have no interest in any kind of manipulation of the intermediate list, I use ToArray over ToList as there's less overhead.

I do this all the time, but I wonder if there is a better pattern for this kind of problem?

Answer 1

There is a much better option: AsEnumerable

Usage is similar:

collectionofsomestuff //here it's LinqToEntities
    .Select(something=>new{something.Name,something.SomeGuid})
    .AsEnumerable() //From here on it's LinqToObjects
    .Select(s=>new SelectListItem()
        {
            Text = s.Name, 
            Value = s.SomeGuid.ToString(), 
            Selected = false
        })

This, however, doesn't force a full copy to be made like ToList() or ToArray(), and preserves any deferred execution from your provider.

Answer 2

Reed's answer is indeed correct, if you are doing simple assignments in the remainder of the LINQ query. However, if you are doing significant work or computation in the LinqToObjects section of your query, his solution has some slight problem if you consider the connections to the underlying data source:

Consider:

collectionofsomestuff //here it's LinqToEntities
    .Select(something=>new{something.Name,something.SomeGuid})
    .AsEnumerable() //From here on it's LinqToObjects
    .Select(s=>new SelectListItem()
        {
            Text = s.Name, 
            Value = s.SomeGuid.ToString(), 
            OtherValue = someCrazyComputationOnS(s)
        })

If you can imagine for a second the code for the LinqToEntities select function (highly simplified, but you should get the picture), it might look something like:

using(SqlConnection con = createConnection())
{
    using(SqlCommand com = con.CreateCommand())
   {
       con.Open();
       com.CommandText = createQuery(expression);

       using(SqlDataReader reader = com.ExecuteReader())
       {
           while(reader.Read())
           {
               yield return createClrObjectFromReader(reader);
           }
       }
   }
}

This method supports the traditional Linq deferred execution patterns. This means that whenever a result is read from the reader, it will be "yielded" back to the caller, and the next value won't be read until the caller requests it.

So, in the above code, the sequence of execution for a result set of 5 records would be:

con.Open();

reader.Read();
createClrObjectFromReader(reader);
// at this point there is a yield back to the caller
someCrazyComputationOnS(s);


reader.Read();
createClrObjectFromReader(reader);
// at this point there is a yield back to the caller
someCrazyComputationOnS(s);


reader.Read();
createClrObjectFromReader(reader);
// at this point there is a yield back to the caller
someCrazyComputationOnS(s);


reader.Read();
createClrObjectFromReader(reader);
// at this point there is a yield back to the caller
someCrazyComputationOnS(s);


reader.Read();
createClrObjectFromReader(reader);
// at this point there is a yield back to the caller
someCrazyComputationOnS(s);

// ONLY here does the connection finally get closed:
con.Close();

Although this does preserve the deferred execution pattern. This is not optimal in this situation. Calling ToList() or ToArray() would cause the entire raw query results to be buffered into an Array or List, after which point the SqlConnection could be closed. Only after the SqlConnection had been closed would the calls to someCrazyComputationOnS(s) actually occur.

In most cases, this isn't a concern and Reed's answer is indeed correct, but in the rare case you are doing large amounts of work on your dataset, you definitely want to buffer the results before proceeding with large LinqToObjects queries.