LinqToSql - avoiding excessive queries to the database

Question

This scenario comes up often, now that I use LinkToSql and the classes it creates.

Classic scenario - two tables:

Member
ID
Name
...

Membership
ID
MemberID (foreign key)
Start (datetime)
Expiration (datetime)
...

An active membership would be one where now is between start and expiration.

To find out if a user has an active membership, I have created this partial class to extend on the LinkToSql-generated Member-class:

Partial Public Class Member
    Public ReadOnly Property HasActiveMembership() As Boolean
        Get
            Dim activeMembershipCount As Integer = (From m In Me.Memberships _
                                Where m.MemberId = Me.MemberId _
                                And m.StartDate < Now And m.ExpirationDate > Now() _
                                Select m).Count

            If activeMembershipCount > 0 Then Return True Else Return False

        End Get
    End Property
End Class

This works great for one member, but when I want to list 700 members and if they have active membership or not, it makes 700+ calls to the database.

How should I change my model/classes?

I could create a query just for lists, where make the entire thing into one query, which is pretty straightforward, but I would like to avoid that, if I could.

Answer 1

You have a few options; for example, you could use DataLoadOptions.LoadWith or DataLoadOptions.AssociateWith to fetch the data eagerly - however, the optimal approach depends on how you are using it.

For example, you could write a "users with active membership" query (either in LINQ using Any, or with a SPROC/UDF). That would then be 1 round trip, but is arguably less object-centric (and more query centric). Which might be fine...

Answer 2

It looks like you already have an association defined, perhaps due to a FK relationship between the tables. That being the case, you can simply extend your membership query using a where clause using the entity reference already defined on the class.

C# example:

var query = db.Members.Where( m => m.Memberships
                                    .Any( ms => ms.StartDate > now
                                                && ms.ExpirationDate < now ));