Getting mapped rows from IQueryable<ForeignKey> in Linq2Sql

Question

The situation is: a webpage displays a listing of Gatherings to a logged-in user. Among this Queryable group might be Gatherings that the user is subscribed to but has not yet viewed until loading this page. Now that the Gatherings have been viewed by this user, I want to mark them as such.

I can get this to work by using ToList() and Contains() (see below) but that means I have to make 2 trips to the database: 1 for ToList() and 1 for foreach(). I've tried other ways to get these Subscriptions but they end up as EntitySet instead.

Users     Gatherings
=====     ==========
UserId    GatheringId

GatheringSubscriptions
======================
GatheringSubscriptionId
GatheringId
UserId
IsViewed

// update unviewed=>viewed for this user's subscription to each
// of these gatherings
public void MarkViewed(IQueryable<Gathering> gatherings, int userId) {
    List<int> gIdsList = gatherings.Select(g => g.GatheringId).ToList();
    IQueryable<GatheringSubscription> gSubs = db.GatheringSubscriptions
        .Where(gs =>
            gs.UserId == userId &&
            !gs.IsViewed &&
            gIdsList.Contains(gs.GatheringId))
        .Distinct();
    foreach (GatheringSubscription gSub in gSubs)
        gSub.IsViewed = true;
    db.SubmitChanges();
}

How can I achieve the same thing but with only 1 trip to the database?

Answer 1

Same problem as here: http://stackoverflow.com/questions/721395/linq-question-querying-nested-collections

The solution is to change this:

db.GatheringSubscriptions
    .Where(gs =>
        gs.UserId == userId &&
        !gs.IsViewed &&
        gIdsList.Contains(gs.GatheringId))
    .Distinct();

to this:

tickers.SelectMany(t => t.GatheringSubscriptions)
    .Where(gs =>
        gs.UserId == userId &&
        !gs.IsViewed)
    .Distinct();