Entity Framework code first - many to many filtering

Question

Small EF question. I have a many to many relationship mapped in EF. X..Y So when I have one X there is a property X.Ys.

Now what I want to do is use a Linq Query to get several X's but I don't want to have all Y's inside the selected X's. I want the Y's filtered on Y.RegistrationDate > Date.Today.

So when I have one X and itterate through .Y's I will only get future Y's.

UPDATE This works, resulting in S having distinct ug's with it's relationship only containing upcoming events. But don't tell me this cant be simplified??!!

        var t = (from ug in uof.Ugs.All().ToList()
                 from upcomingEvent in ug.Events
                 where upcomingEvent.Date >= DateTime.Today
                 select new
                 {
                     ug,
                     upcomingEvent
                 }).ToList();

        var s = (from ug in t.Select(x => x.ug).Distinct()
                 select new UG
                 {
                     Id = ug.Id,
                     Name = ug.Name,
                     Description = ug.Description,
                     WebSite = ug.WebSite,
                     Events = ug.Events.Where(x => x.Date >= DateTime.Today).ToList()
                 }).ToList();

UPDATE2

Added image to show that even with basic context manipulation I'm still getting 2 events, event when I take 1!

exampledebugimage

Answer 1

EF does not support this scenario as you want it, what you can do however is this:

var date = DateTime.Date;
var query = from x in Xs                
            select new 
            {
                X = x
                Ys = x.Ys.Where(i = > i.RegistrationDate > date)
            }

Which will give you a collection of X's with their corresponding Y's that match your criteria.

Answer 2

Have you tried?:

var query = Xs
.Select(x => new { x, yCol = x.YCol.Where(y => y.Date >= DateTime.Today) })
.AsEnumerable()
.Select(x => x.x)
.ToList();

See: http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx

All those .ToList you use will mean you load the whole table from the db before filtering. So watch out for that.

UPDATE: As fixup doesn't work with Many-To-Many

As Slauma mentioned in the comments make sure you don't use this technique if you are going to submit the changes as the changetracking will think you altered the collection. Or even better make sure you use .AsNoTracking() which will improve performance anyway.

We can use the same solution as above but slightly different for many-to-many. See this example:

[TestClass]
public class ContextTest
{
    [TestMethod]
    public void FixupTest()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<Context>());

        using (var db = new Context())
        {
            db.Groups.Add(new Group
            {
                Name = "G1",
                Users = new List<User>{
                    new User{ Name = "M"},
                    new User{Name = "S"}
                }
            });

            db.SaveChanges();
        }

        using (var db = new Context())
        {
            var group = db.Groups
                .Select(g => new { g, Users = g.Users.Where(u => u.Name == "M") })
                .AsEnumerable()
                .Select(g => {
                    g.g.Users = g.Users.ToList();
                    return g.g;
                })
                .First();

            Assert.AreEqual(1, group.Users.Count);
        }
    }


}



public class User
{
    public int ID { get; set; }
    public string Name { get; set; }

    public ICollection<Group> Groups { get; set; }
}



public class Group
{
    public int ID { get; set; }
    public string Name { get; set; }

    public ICollection<User> Users { get; set; }
}

The test pass and the generated sql is:

SELECT 
[Project1].[ID] AS [ID], 
[Project1].[Name] AS [Name], 
[Project1].[C1] AS [C1], 
[Project1].[ID1] AS [ID1], 
[Project1].[Name1] AS [Name1]
FROM ( SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Join1].[ID] AS [ID1], 
    [Join1].[Name] AS [Name1], 
    CASE WHEN ([Join1].[Group_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Groups] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[Group_ID] AS [Group_ID], [Extent3].[ID] AS [ID], [Extent3].[Name] AS [Name]
        FROM  [dbo].[GroupUsers] AS [Extent2]
        INNER JOIN [dbo].[Users] AS [Extent3] ON [Extent3].[ID] = [Extent2].[User_ID] ) AS [Join1] ON ([Extent1].[ID] = [Join1].[Group_ID]) AND (N'Mikael' = [Join1].[Name])
)  AS [Project1]
ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC