locked
How to include two where conditions in LINQ query RRS feed

  • Question

  • I have the following linq query. The query gives compile time error when i add second where condition which checks IsExcluded flag

    var package = repository.GetQuery()
                       .Where(a => a.PackageID == 2)
                       .Include(a => a.PackageDetails.Where(x=>x.IsExcluded()) // this is where its failing
                       .Include(a => a.PackageDetails.Select(b => b.UserDocument))
                       .Include(a => a.SendHistories)
                       .Include(a => a.SendHistories.Select(b => b.User))
                       .SingleOrDefault();

    Wednesday, February 15, 2012 7:49 PM

Answers

  • Hi lax4u;

    Because you will be filtering on the relations you can not use the Include method. Also I needed to use the let query statement to do the filtering I had to use query syntax and not method syntax. You will also need to disable Lazy Loading because as soon as you access any of the navigation properties it will load the related tables if you do not do this. The following code snippet should do what you want.

    repository.ContextOptions.LazyLoadingEnabled = false; 
    
    var result = (from a in repository.GetQuery()
                  where a.PackageID == 2
                  let isExcluded = a.PackageDetails.Where( x => x.IsExcluded())
                  let userDocument = a.PackageDetails.Select(b => b.UserDocument)
                  let sendHistories = a.SendHistories
                  let user = a.SendHistories.Select(b => b.User)
                  select new
                  {
                      a,
                      isExcluded,
                      userDocument,
                      sendHistories,
                      user
                  }).SingleOrDefault();
                   
    var package = result.a;


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Allen_MSDN Monday, February 20, 2012 2:32 AM
    Thursday, February 16, 2012 6:21 AM

All replies

  • Hi lax4u;

    The Include method does NOT filter a related entity it only selects a related entity to be included in the query result. If you wish to filter a related entity you will need to do it in a Select method outside of the include, at the end of the query.

    .


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, February 15, 2012 8:38 PM
  • Can you modify the above query and show?

    Wednesday, February 15, 2012 8:58 PM
  • Hi lax4u;

    Because you will be filtering on the relations you can not use the Include method. Also I needed to use the let query statement to do the filtering I had to use query syntax and not method syntax. You will also need to disable Lazy Loading because as soon as you access any of the navigation properties it will load the related tables if you do not do this. The following code snippet should do what you want.

    repository.ContextOptions.LazyLoadingEnabled = false; 
    
    var result = (from a in repository.GetQuery()
                  where a.PackageID == 2
                  let isExcluded = a.PackageDetails.Where( x => x.IsExcluded())
                  let userDocument = a.PackageDetails.Select(b => b.UserDocument)
                  let sendHistories = a.SendHistories
                  let user = a.SendHistories.Select(b => b.User)
                  select new
                  {
                      a,
                      isExcluded,
                      userDocument,
                      sendHistories,
                      user
                  }).SingleOrDefault();
                   
    var package = result.a;


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Allen_MSDN Monday, February 20, 2012 2:32 AM
    Thursday, February 16, 2012 6:21 AM