LINQ to SQL: How to create something like conditional include?

Question

I'd like to create a query contains User whose entities has Event with type == 1. I would also like the Event entries loaded into EventsCollection. My code would be something like .Include(u => u.EventsCollection), but this only loads all Event entries - I want to load only with type == 1.

I have simple model like this:

public class User
{
    public int UserId { get; set; }

    public ICollection<Event> EventsCollection { get; set; }
}
public class Event
{
    public int EventId { get; set; }
    public int UserId { get; set; }
    public int Type { get; set; }

    public User User {get; set; }
}

Any ideas?

Edit:

Finally I've found the solution: We need fresh DataContext object with NO Event entities have been previously loaded.

Then we need to filter Events like this:

var filteredEventsCollection = db.Events
    .Where(ev => ev.Type == 1)
    .ToList(); //Materialize query here

Now we should query users like this:

var usersWithFilteredEvents = db.Users
    .Where(u => u.EventsCollection.Any(ev => ev.Type == 1))
    .ToList();

And that is all! EF will substitute EventsCollection with data from the first query by itself.

Not really sure about what you are asking but probably you are looking for LINQ Contains (equivalent to SQL WHERE ... IN (SELECT ). — bubi, Jun 18 '15 at 17:27
LINQ Contains can help me to find User entities with event type == 1, but I also want to load them into EventsCollection. — Alexander, Jun 18 '15 at 17:30
Load with lazy load? When you access the property? (BTW, don't forget virtual!) — bubi, Jun 18 '15 at 17:31
Yes, lazy load should help but the number of request to the DB will grow (one more request for each entity in Users collection)! I'm looking for solution to load everything in one query. Thank you anyway. — Alexander, Jun 18 '15 at 17:36
Here is a similar question http://stackoverflow.com/questions/16798796/ef-include-with-where-clause — Steve Greene, Jun 18 '15 at 17:52

jjj · Accepted Answer · 2015-06-19T23:48:17.850

1

You can't use Include and filter which entities are eagerly-loaded (or lazy-loaded) but you might be able to fake it by using a projection:

var result = (from u in db.Users
              where u.EventsCollection.Any(ev => ev.Type == 1)
              select new {
                  User = u,
                  Events = u.EventsCollection.Where(ev => ev.Type == 1),
              }).ToList()    // materialize query here!
             .Select(uev => uev.User)
             .ToList();

This should return an IEnumerable with the events you want in EventCollection
This won't work if other Event entities have been previously loaded into the context!
Lazy loading must also be disabled for User.EventsCollection by removing virtual -- which you've already done -- or disabling it for the entire context.
See this link

In any case, I'd probably just use the projection directly, since that's safer.

edited Jun 19 '15 at 23:48

answered Jun 19 '15 at 17:29

jjj

4,822
1
16
39

Thank you for your answer but this solutions doesn't work for me. It looks like there is no query materialization on .AsEnumerable() call. All my EventsCollection = null. – Alexander Jun 19 '15 at 17:48
But I've found a very similar solution. You can check it out on my first post. – Alexander Jun 19 '15 at 17:48
@Alexander: well, it won't actually materialize until the Enumerable is used, but maybe you're right, `ToList()` would do it for sure. I'll change that. – jjj Jun 19 '15 at 23:47

Yuri · Answer 2 · 2015-06-19T16:02:31.603

this will give you what you are looking for:

void Main(){

List<User> users = new List<User>{
 new User{ UserId=1},
 new User{ UserId=2}
};

List<Event> events = new List<Event>{
    new Event{ EventId=1, UserId=2, Type=0},
    new Event{ EventId=2, UserId=2, Type=1},
    new Event{ EventId=4, UserId=2, Type=1},
    new Event{ EventId=5, UserId=2, Type=0},
    new Event{ EventId=1, UserId=1, Type=1},
    new Event{ EventId=2, UserId=1, Type=0},
    new Event{ EventId=4, UserId=1, Type=0},
    new Event{ EventId=5, UserId=1, Type=1},
};



var result = users.GroupJoin(events.Where (e =>e.Type==1 ),
    u => u.UserId,
    e => e.UserId,
    (a, b) => new {a.UserId,b});    
//result.Dump();

}

 // Define other methods and classes here
public class User
{
  public int UserId { get; set; }

  public ICollection<Event> EventsCollection { get; set; }
}
public class Event
{
  public int EventId { get; set; }
  public int UserId { get; set; }
  public int Type { get; set; }

  public User User {get; set; }
}

score -2 · Answer 3 · answered Jun 19 '15 at 02:28

If you are only trying to get the events where userID ==1, you can use the following. This will give you only the event you need from the Event Collection for the user.

        User u = new User()
        {
            UserId = 60
        };

        Event e1 = new Event() { EventId = 1, UserId = 2, Type = 3, User = u };
        Event e2 = new Event() { EventId = 2, UserId = 3, Type = 4, User = u };
        Event e3 = new Event() { EventId = 3, UserId = 4, Type = 5, User = u };
        Event e_Test = new Event() { EventId = 7, UserId = 1, Type = 5, User = u };

        u.EventsCollection = new List<Event>();

        u.EventsCollection.Add(e1);
        u.EventsCollection.Add(e2); 
        u.EventsCollection.Add(e3);
        u.EventsCollection.Add(e_Test);


        var h = from use in u.EventsCollection where use.UserId == 1 select use;

This does not answer question. He is trying to get Users with Event type ==1. Plus, your User has no relation to Event collection. User=>UserID=60 and in your events UserId from 1 to 5 — Yuri, Jun 19 '15 at 17:52

LINQ to SQL: How to create something like conditional include?

3 Answers3