Constraining queries based on aggregations of properties of associations

Question

Quick question: would this trigger one query per each Tag item?

public static IQueryable<Tag> WhereTagHasLivePosts(this IQueryable<Tag> q)
{
    return q.Where(t => t.Posts.Where(p => DateTime.Now >= p.PublishTime && p.IsPublished == true).Count() > 0);
}

t.Posts.Where is actually an extension on IEnumerable, rather than IQueryable, so it seems like this might not be a good idea.

Thanks in advance,

Rei

Answer 1

you're exactly right - t.Posts is an EntityCollection (in linq-to-sql, it would be an EntitySet) and as such implements IEnumerable and not IQueryable.

While the thread was about linq-to-sql, it's the same logic as this:

http://damieng.com/blog/2009/06/01/linq-to-sql-changes-in-net-40

Mikeon says:

[...] EntitySet which is NOT IQueryable.

This prevents usage of linq2sql in any real life scenario.

Consider:

client.Orders.Where(o=>o.Total>1000); // runs linq to objects rather than linq to sql

Same problem exists in entiy framework.

Damien responds:

@Mikeon: EntitySet is the loaded in-memory collection for relationships.

If you want all the orders for a specific client where the total is greater than 100 then you perform:

db.Orders.Where(o => o.Client == client && o.Total > 1000)