Will my LinqToSql execution be deffered if i filter with IEnumerable instead of IQueryable?

Question

I have been using these common EntityObjectFilters as a "pipes and filters" way to query from a collection a particular item with an ID:

public static class EntityObjectFilters
{
    public static T WithID<T>(this IQueryable<T> qry,
       int ID) where T : IEntityObject
    {
        return qry.SingleOrDefault<T>(item => item.ID == ID);
    }

    public static T WithID<T>(this IList<T> list,
        int ID) where T : IEntityObject
    {
        return list.SingleOrDefault<T>(item => item.ID == ID);
    }
}

..but i wondered to myself: "can i make this simpler by just creating an extension for all IEnumerable<T> types"?
So i came up with this:

public static class EntityObjectFilters
{
    public static T WithID<T>(this IEnumerable<T> qry,
       int ID) where T : IEntityObject
    {
        return qry.SingleOrDefault<T>(item => item.ID == ID);
    }
}

Now while this appears to yield the same result, i want to know that when applied to IQueryable<T>s will the expression tree be passed to LinqToSql for evaluating as SQL code or will my qry be evaluated in it's entirety first, then iterated with Funcs?

I'm suspecting that (as per Richard's answer) the latter will be true which is obviously what i don't want. I want the same result, but the added benefit of the delayed SQL execution for IQueryable<T>s. Can someone confirm for me what will actually happen and provide simple explanation as to how it would work?

EDIT:

Solution i went with

    public static T WithID<T>(this IEnumerable<T> qry,
        int ID) where T : DomainBase
    {
        if (qry is IQueryable<T>)
            return ((IQueryable<T>)qry).SingleOrDefault<T>(item => item.ID == ID);
        else
            return qry.SingleOrDefault<T>(item => item.ID == ID);
    }

score 3 · Accepted Answer · answered May 17 '10 at 09:16

3

No, when qry is typed as IEnumerable<T> it will call Enumerable.SingleOrDefault rather than Queryable.SingleOrDefault. The lambda expression will be converted into a delegate instead of an expression tree, and it won't use SQL.

Note that SingleOrDefault doesn't use deferred execution in the first place - it's always immediate - but the difference is where the querying is performed. You almost certainly want the database to do it. If you have a look at your logs with the simplified version, you'll see that all the results are being fetched - whereas with the IQueryable<T> overload the SQL will contain the relevant filtering.

answered May 17 '10 at 09:16

Jon Skeet

1,421,763
867
9,128
9,194

Are you saying that in the first function in the first example the `SingleOrDefault` won't even use an SQL `WHERE`? – Matt Kocaj May 17 '10 at 09:22
Yes, that is exactly what Jon meens. Doing this will possibly retrieve all data rows from a table and filter client side. This would be very bad for performance. – Steven May 17 '10 at 10:10
Hold on: he says `SingleOrDefault` does not use deferred execution but it can query in SQL if it's `Queryable.SingleOrDefault`. This would seem to suggest that the first function in the first example the would use an SQL `WHERE`. Agreed? – Matt Kocaj May 17 '10 at 10:18
@cottsak: The first function should use a WHERE in the SQL, yes. Using your second snippet, over `IEnumerable` *won't* use a WHERE. – Jon Skeet May 17 '10 at 10:49
@Jon: refer to the edit: Is this an acceptable compromise meeting the requirements in a single extension method? – Matt Kocaj May 17 '10 at 19:24
@cottsak: Yes - it's a bit ugly, but it makes it somewhat simpler for the caller. One issue is that you then *can't* easily force it to be done in-process; usually you'd consider using `AsEnumerable` to force the rest of a query to be performed in-process, but that won't work in this case. – Jon Skeet May 17 '10 at 21:46
Yeh it's not that attractive with the casting garb. I'm not sure the option of forcing the use of delegates is any advantage in the case of selecting a single item. I can understand the case for a range - good example is when the expression logic can't be converted to SQL. Why might the 'in-process' option be an advantage to me in this case? – Matt Kocaj May 18 '10 at 05:28
@cottsak: I don't think it would, to be honest. It was more of a general concern when you start doing this sort of thing - it's something to watch out for if you do anything similar in the future. – Jon Skeet May 18 '10 at 06:18
@Jon I think i just found a bug. By extending `IEnumerable` like i have in my solution example, am i not forcing `qry` to always be cast to `IEnumerable` within the method? And by doing so (casting to a less derived type), never getting to the first part of the `if` condition? – Matt Kocaj Jun 14 '10 at 05:39
@cottsak: No, because "is" deals with the *execution time* type of the object, not the *compile time* type. – Jon Skeet Jun 14 '10 at 06:34
@Jon: What is your view of pre-compilation (like this: http://lanitdev.wordpress.com/2010/01/06/unexpected-benefits-of-precompilation-of-linq/) in this case? I can't seem to tell if there is a benefit. – Matt Kocaj Jun 14 '10 at 07:16
@cottsak: I don't have enough experience to say, to be honest. – Jon Skeet Jun 14 '10 at 08:26

Will my LinqToSql execution be deffered if i filter with IEnumerable instead of IQueryable?

Solution i went with

1 Answers1