How to asynchronously call a database with subrecords using LINQ?

Question

I'm using EF6 and want to make the following query fully asynchronous:

 await MyDataContext.ADbSet.
                     First(a => a.Something == "Something").
                     ASubCollection.
                     Select(x => new { x.SubCollectionId }).
                     ToListAsync();

This doesn't work, I believe due to First() returning the actual entity and access to ASubCollection being an ICollection, not an IQueryable.

I was able to work around this with the following code:

 await MyDataContext.ADbSet.
                     Where(a => a.Something == "Something").
                     SelectMany(a => a.ASubCollection).
                     Select(x => new { x.SubCollectionId }).
                     ToListAsync();

However, this seems "hacky" as I'm using a Where(...) when I should be using a First() as I know at compile time that there will be exactly one element satisfying the query. Is there a better way of doing this?

@VladimirFrolov at what point? Please elaborate. – Alex Oct 16 '13 at 00:05 — Alex, Oct 16 '13 at 00:05
`.ADbSet.Where(a => a.Something == "Something").Take(1)` – Vladimir Oct 16 '13 at 05:53 — Vladimir, Oct 16 '13 at 05:53

Spontifixus · Accepted Answer · 2015-07-16T10:31:24.700

The call to First() is a call that actually enumerates the underlying sequence and returns an entity instead of a Task. Thus First() won't work together with the await-keyword.

Your second solution is completely valid (and not "hacky" at all) in this context, because there is no need to add a limit to the generated database query, as the Where(...)-call will return exactly one element in this special case - with or without a limit in the query.

If the Where-call is likely to return multiple elements, or you simply want to ensure that there will be only the first element considered, inserting a call to Take(1) will bring the first element of the sequence, but still be an IQueryable:

await MyDataContext.ADbSet
                   .Where(a => a.Something == "Something")
                   .Take(1)
                   .SelectMany(a => a.ASubCollection)
                   .Select(x => new { x.SubCollectionId })
                   .ToListAsync();

Well, this is what Joel Spolsky calls "leaking abstraction" :) Thanks for the info! — DarkWanderer, Oct 17 '13 at 12:02
@DarkWanderer Seems you are right about that! :) For those who, like me, might wonder what "leaking abstraction" is all about, here's the link to Joel's blog: [The Law of Leaky Abstractions](http://www.joelonsoftware.com/articles/LeakyAbstractions.html) — Spontifixus, Oct 17 '13 at 12:13

How to asynchronously call a database with subrecords using LINQ?

1 Answers1