CODEWITHSUNDEEP
c#asp.net-mvclinq-to-sql
user330366
user330366

Reputation:

Use LINQ-to-SQL to return an object that has child objects filtered

I have a MembershipGroups table that is associated with a child Members table. The Members table has a Status column which can be set to Active or Inactive.

I want to select all MembershipGroups and only their active Members

As an example,

MembershipGroups

ID----Title

1-----Group #1

2-----Group #2

Members

MembershipGroupID-Name--Status

1-------------------------John----Active

1-------------------------Sally----Inactive

1-------------------------David---Inactive

I'm trying to create a query that looks something like the following (which doesn't currently work):

var query = from mg in db.MembershipGroups
where mg.Members.Status = "Active"
select mg

The result for this example should return a MembershipGroup of ID#1 with only one child Member entity

How can use LINQ-to-SQL to select a parent object that filters on child objects? If I were using straight T-SQL then this would be a simple join with a where clause but it seems to be much more difficult to do using LINQ-to-SQL.

Upvotes: 2

Views: 4593

Answers (3)

Jim Wooley
Jim Wooley

Reputation: 10408

In LINQ to SQL, you can use the AssociateWith method on the DataLoadOptions to set your child filter at the context level. 

DataLoadOptions opt = new DataLoadOptions();
opt.AssociateWith<Member>(m => m.Status == "Active");
db.LoadOptions = opt;

With this in place, you can simply return your member groups (or filter them for the active ones using where mg.Any(group => group.Members.Status == "Active"). Then when you try to drill into the Members of that group, only the Active ones will be returned due to the LoadOptions.

See also http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.associatewith.aspx . One word of warning, once you set the LoadOptions on a context instance, you can not change it. You may want to use a customized context to use this option.

As an alternative, you could use LINQ to SQL's inheritance model to create an ActiveMember type using the Status column as your discriminator and then create an association between the MemberGroups and ActiveMembers types. This would be the approach you would need to use to model this with the Entity Framework if you though about going that route as well as EF doesn't support the concept of the LoadOptions.

Upvotes: 1

Aducci
Aducci

Reputation: 26694

Edit - Updated answer to return the MemberShipGroup object

var query = (from mg in db.MembershipGroups
            join m in db.Members.Where(mem => mem.Status == "Active")
            on mg.ID equals m.MembershipGroups into members
            select new
            {
               MembershipGroup = mg,
               Members = members
            }).AsEnumerable()
              .Select(m => new MembershipGroup  
                           {
                             ID = m.MembershipGroup.ID,
                             Title = m.MembershipGroup.Title,
                             Members = m.Members 
                           });

Upvotes: 4

Winger
Winger

Reputation: 676

Make sure you are including the child objects you are trying to filter on, inside the query.

E.g.

var query = db.MembershipGroups .Include("Members") .Where(m => m.Members.Status == "Active");

Upvotes: 0

Related Questions