Select doesn't work on IQueryable but does on IList

Question

I have two lines of code, one is

AllItems().Where(c => c.Id== id)
          .Select(d => new Quality(d.QualityType)).ToList();

and the other one

AllItems().Where(c => c.Id== id).ToList()
          .Select(d => new Quality(d.QualityType)).ToList();

The only difference is on the second statement ToList() is called after the Where statement. The second statment works just fine.

On the first statement, the default parameterless constructor is hit instead of the constructor with the parameter. so the list is created but the objects in the list are initialized with default values rather than with the d.QualityType.

you can see the full source of the file in question at (Method: GetBestQualityInHistory)

https://github.com/kayone/NzbDrone/blob/master/NzbDrone.Core/Providers/HistoryProvider.cs

**Edit: After further investigation, this seems to be a SubSonic bug, if the Last ToList is replaced by an OrderBy subsonic throws an The construtor 'Void .ctor(NzbDrone.Core.Repository.Quality.QualityTypes, Boolean)' is not supported.

Answer 1

If SubSonic works in the same way as Entity framework you cannot use constructors with parameters - you must use parameterless constructors and initializers. My very high level explanation of this is that the query is not executed as is - it is translated to SQL and because of that you must use property initializers so that expression tree knows which properties in the projected type should be filled by values. When using constructor with parameters, expression tree doesn't know where the passed parameter belongs to (it doesn't check content of the constructor). The real constructor (parameterless) is called once you execute Tolist and result set is materialized into QuantityType instances.

Answer 2

This isn't really an answer, and I was going to make it a comment, but needed more room for code snippet.

Judging by the SubSonic code, I'm pretty sure that in some cases where you get the "constructor not supported" errors, SS is for some reason trying to parse your new ...() statement into SQL. The offending method is part of the SQL Formatter, and looks like it only handles DateTime:

    protected override NewExpression VisitNew(NewExpression nex)
    {
        if (nex.Constructor.DeclaringType == typeof(DateTime))
        {
            // ...omitted for brevity...
        }
        throw new NotSupportedException(string.Format("The construtor '{0}' is not supported", nex.Constructor));
    }

I think that would normally be hit if you did something like:

someData .Where(data => data.CreatedDate <= new DateTime(2011, 12, 01)) .Select(data => data)

Then that newDateTime would be translated to SQL. So however you change the Linq to get that exception, I think that is what is happening. I assume this is because if you add an .OrderBy() after the .Select() then you are no longer calling the OrderBy on the IQueryable of whatever AllItems() returns, but instead trying to order by what is returned by the .Select(), which is the enumerable of new Quality obejcts, so SS probably tries to turn all that into SQL.

I'm wondering if it would work right if you reversed it?

AllItems().Where(c => c.Id== id)
      .OrderBy(d => d.QualityType)
      .Select(d => new Quality(d.QualityType));