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

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.

Answers


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.


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; }
}

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;

Need Your Help

Android call function on tab click?

android android-tabhost

i am trying to call a function instead to open an activity but it is not working . please give me some solution

Convert Yes/No/Null from SQL to True/False in a DataTable

c# sql sql-server datatable

I have a Sql Database (which I have no control over the schema) that has a Column that will have the varchar value of "Yes", "No", or it will be null. For the purpose of what I am doing null will be