none
Proper way to filter an Include() query in Linq to Entities RRS feed

  • Question

  • Hey all.

    I have just started using EF for a project. I am using Self-Tracking Entities and need to fully populate the objects to go across services.

    Basically, I will have a lot of queries, where I want to return an object with some of its navigation properties. I have started to use the Include() statement to attach a navigation property. I have a User object that has a collection of CurrentSystemSessions and my first go at returning this object is as follows:

            var SessionSet = from U in RoutingContext.Users.Include(U => U.CurrentSystemSessions)
                     where U.SystemSessions.Any(SS => SS.SessionToken == currentSessionToken)
                     select U;
    


    This returned me my User object with ALL CurrentSystemSessions which is not what I want. I want to attach only one CurrentSystemSession to the User object. So I finally got the following working with anonymous types.

            // Get the user from the database
            var SessionSet = from U in RoutingContext.Users
                     where U.SystemSessions.Any(SS => SS.SessionToken == currentSessionToken)
                     select new { UserObject = U, CurrentSystemSessionObject = U.CurrentSystemSessions.Where(CSS => CSS.SystemSession.SessionToken == currentSessionToken) };
    
            // Now return the user if we got one
            var SessionSetObject = SessionSet.SingleOrDefault();
    
            // return object of type User
            User UserToReturn = null;
    
            // if we got a row from the database
            if (SessionSetObject != null)
            {
              // set the user object to what was retrieved
              UserToReturn = SessionSetObject.UserObject;
    
              // now copy over the current system session
              UserToReturn.CurrentSystemSessions.Add(SessionSetObject.CurrentSystemSessionObject.FirstOrDefault());
            }
    


    But this seems too complex to do every single time I'll need to do this. Which will most likely be with every query I do!!

    Is there a better way of doing this? I tried to change the Include() statement to include a lamda expression to filter, but got an error, and after googling it, I found out you cannot filter in an Include() statement!


    Developer
    Friday, August 26, 2011 2:53 PM

Answers

  • Hi,

    Perhaps, you need to start querying from the other end: search for CurrentSession and "Include" Users that the session represents.

    Here is the code that might help here:

          string currentSessionToken = "some token";
    
          using (var context = new TestEntities())
          {
            var currentSession = (from s in context.SystemSessions.Include("Users")
                       where s.SessionToken == currentSessionToken
                       select s).FirstOrDefault();
    
            if (currentSession != null && currentSession.Users != null)
            {
              Users user = currentSession.Users;
    
            }
          }
    

    Best regards

    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 7:46 AM
  • Hi Paul,

    Welcome!

    If you are using DbContext, you can use .Query method to achieve your goal, you can refer here:

    http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 8:58 AM
    Moderator
  • Thanks for the answers folks.

    JA Reyes, I will look into the LinqKit to see what it can do for me.

    Rustam, I get your point about querying from the other side, and in this occasion that would work, however this is a very simple query and I would assume it wouldn't work for other more complex queries.

    Alan, I have never seen the .Query() method and it looks like that would have been a handy tool. However, as I am using STE's, I need to use the ObjectContext rather than the DbContext. At least until a template is created for STE's to use the DbContext.

    However, looking at the post, along with others, I have noticed I can simplify my code noted above, as the linking between objects is all done for you, so no need for to manually do it! So my code would now look like:

          // Create a context to the routing database
          using (RoutingAndAuthEntities RoutingContext = new RoutingAndAuthEntities(Routing.GetRoutingDatabaseConnectionString()))
          {
            // Get the user from the database
            var UserSessionSet = from U in RoutingContext.Users
                       where U.SystemSessions.Any(SS => SS.SessionToken == currentSessionToken)
                       select new { UserObject = U, CurrentSystemSessionObject = U.CurrentSystemSessions.Where(CSS => CSS.SystemSession.SessionToken == currentSessionToken) };
    
            // Now return the user if we got one
            var SessionSetObject = UserSessionSet.SingleOrDefault();
    
            // return object of type User
            User UserToReturn = null;
    
            // if we got a row from the database
            if (SessionSetObject != null)
            {
              // set the user object to what was retrieved
              UserToReturn = SessionSetObject.UserObject;
            }
    
            return UserToReturn;
          }
    



    Developer
    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 11:35 AM

All replies

  • Here's how I'd proceed if I didn't know the answer (which I don't)

    I'd study when Includes are needed and when they are not.  I know for example you can kick off queries that populate the navigational classes without having to specify the Include.  If I couldn't get it there, I'd resort to a SPROC on DB or View or something at the DB layer that makes this simple to do. 


    JP
    Monday, August 29, 2011 3:52 AM
  • Hi,

    Take a look at this extenssion kit named LINQKit:

    http://www.albahari.com/nutshell/linqkit.aspx

    Best regards,

    JA Reyes. 


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Monday, August 29, 2011 6:25 AM
  • Hi,

    Perhaps, you need to start querying from the other end: search for CurrentSession and "Include" Users that the session represents.

    Here is the code that might help here:

          string currentSessionToken = "some token";
    
          using (var context = new TestEntities())
          {
            var currentSession = (from s in context.SystemSessions.Include("Users")
                       where s.SessionToken == currentSessionToken
                       select s).FirstOrDefault();
    
            if (currentSession != null && currentSession.Users != null)
            {
              Users user = currentSession.Users;
    
            }
          }
    

    Best regards

    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 7:46 AM
  • Hi Paul,

    Welcome!

    If you are using DbContext, you can use .Query method to achieve your goal, you can refer here:

    http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 8:58 AM
    Moderator
  • Thanks for the answers folks.

    JA Reyes, I will look into the LinqKit to see what it can do for me.

    Rustam, I get your point about querying from the other side, and in this occasion that would work, however this is a very simple query and I would assume it wouldn't work for other more complex queries.

    Alan, I have never seen the .Query() method and it looks like that would have been a handy tool. However, as I am using STE's, I need to use the ObjectContext rather than the DbContext. At least until a template is created for STE's to use the DbContext.

    However, looking at the post, along with others, I have noticed I can simplify my code noted above, as the linking between objects is all done for you, so no need for to manually do it! So my code would now look like:

          // Create a context to the routing database
          using (RoutingAndAuthEntities RoutingContext = new RoutingAndAuthEntities(Routing.GetRoutingDatabaseConnectionString()))
          {
            // Get the user from the database
            var UserSessionSet = from U in RoutingContext.Users
                       where U.SystemSessions.Any(SS => SS.SessionToken == currentSessionToken)
                       select new { UserObject = U, CurrentSystemSessionObject = U.CurrentSystemSessions.Where(CSS => CSS.SystemSession.SessionToken == currentSessionToken) };
    
            // Now return the user if we got one
            var SessionSetObject = UserSessionSet.SingleOrDefault();
    
            // return object of type User
            User UserToReturn = null;
    
            // if we got a row from the database
            if (SessionSetObject != null)
            {
              // set the user object to what was retrieved
              UserToReturn = SessionSetObject.UserObject;
            }
    
            return UserToReturn;
          }
    



    Developer
    • Marked as answer by eyeballpaul Monday, August 29, 2011 11:36 AM
    Monday, August 29, 2011 11:35 AM
  • Hi,

    Thanks for sharing your experience here.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 29, 2011 12:35 PM
    Moderator