locked
Filter on navigational property RRS feed

  • Question

  • Hi

    I'm trying to make a Linq expression witch filer based on navigation properties.

    Class ResourceType include a navigational property Resources.

    The following is trying to get all ResourceType witch has not been deleted and include all Resources that has not been deleted.

    The first filtering succedes, that is the it only picks up ResourceTypes that has not been deleted, but the filtering for navigational does not work.

    Any suggestions why it doesn't work?

    var dbquery = from r in query
                         where r.IsDeleted.Equals(null) || r.IsDeleted.Value == false
                              select new
                              {
                                  resourceType = r,                             
                                  resources = from res in r.Resources
                                             where res.IsDeleted.Equals(null) || res.IsDeleted.Value == false
                                             select res
                              };
        
          
                var resourceTypes = dbquery.AsEnumerable().Select(r => r.resourceType); 

     

    Saturday, August 3, 2013 5:06 PM

Answers

  • Hi;

    To your question, "Is it possible to have the result in an IEnumerable<ResourceType>? Not if you want the SQL server to return a filtered list of Resources. If you take a look at resourceType in the result set of the query dbquery you will find that each ResourceType contains a property which contain all associated records of Resources, unfiltered. That is why in the select clause there is a subquery to return a collection of Resources, filtered, to just contain the Resources you need. This query returns a anonymous type and really not suitable as a return type of a function. You could create a class to hold the two propertie, something like this

    public class MyFilteredResults
    {
        public ResourceType resourceType { get; set; }
        public Resources resources { get; set; }
    }
    
    // Then the select clause would change as this
    
    select new MyFilteredResults
    {
        resourceType = r,
        resources = (from res in r.Resources
                     where res.IsDeleted == null || res.IsDeleted == false
                     select res)
    };
    
    // and have the return type as IEnumerable<MyFilteredResults>. 
    
    The only way to get a return type of IEnumerable<ResourceType> from the query is to have the select clause as follows
    
    select r;
    
    Then return the return value of the query to the caller then filter the Resources property.

      


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.


    Tuesday, August 6, 2013 6:36 PM

All replies

  • Hi sfddsfsdfsfsd;

    It looks like it should work. Is any exception thrown, if so please post exception message and inner exception message.

     


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, August 3, 2013 8:06 PM
  • Hi

    No exception, I can just see at the result that it does not care about the filer for the navigation properties. it pick even the ones with IsDeleted == true.

    Saturday, August 3, 2013 8:13 PM
  • Hi sfddsfsdfsfsd;

    Is the database that you are querying a test database and that the data is test data and NOT real? If so can you zip it and post the database to a public folder on your Skydrive so I can see what is going on.

       


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, August 3, 2013 8:19 PM
  • Hi

    I've uploaded a backup. The database name is resursBokning2.

    Here is a link to sky drive:

    https://skydrive.live.com/redir?resid=B660E8B4BB8FB3FC!131&authkey=!ABDYH-aBvc6CUW8

    Sunday, August 4, 2013 6:35 AM
  • In SQL Management Studio in order for me to restore from a backup I need a copy of the database attached to my server already, so a backup will not help me.

    Please use a archiving utility to zip the file up before uploading, zip the actual database file not a backup so I can attach it to the server.


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Sunday, August 4, 2013 1:19 PM
  • Ok, sorry for waisting your time like that.

    Now there's a zipped database file in place.

    (i Think you can create a new database with the same name as the backed up database and restore from that. Nevermind the zipfile is in Place.)

    Sunday, August 4, 2013 9:42 PM
  • Hi;

    This seems to be working for me. Try it out and let me know.

    // Instance of DbContext
    var db = new ResursEntities();
    
    var dbquery = from r in db.ResourceTypes
                  where r.IsDeleted == null || r.IsDeleted == false
                  select new
                  {
                      resourceType = r,
                      resources = (from res in r.Resources
                                   where res.IsDeleted == null || res.IsDeleted == false
                                   select res)
                  };

      


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Sunday, August 4, 2013 11:41 PM
  • Hi

    It still dosen't work for me.

    It's as if no filter is there for Resources. It picks up Resources.IsDeleted set to true.

    Monday, August 5, 2013 2:32 PM
  • Hi;

    From the database you sent me how many total records of type ResourceTypes are you expecting and in each of those records how many in Resources are there are suppose to be?


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, August 5, 2013 5:01 PM
  • Hi

    I'm expecting Resources with property IsDeleted == true not to be picked up. But for me the IsDeleted == true is picked up. That's why I say it's like there's no filter for Resources. The first one for ResourceType works as it supposed to.

    Tuesday, August 6, 2013 5:57 AM
  • Hi;

    Here is the code I used to test and the result I got. Please show the results you are getting with the code below.

    var db = new ResursEntities();
    
    var dbquery = from r in db.ResourceTypes
                  where r.IsDeleted == null || r.IsDeleted == false
                  select new
                  {
                      resourceType = r,
                      resources = (from res in r.Resources
                                   where res.IsDeleted == null || res.IsDeleted == false
                                   select res)
                  };
    
    var results = dbquery.ToList();
    
    foreach(var rType in results)
    {
        Console.WriteLine("ID = {0}\tType = {1}\tIsDeleted = {2}", rType.resourceType.Id,
            rType.resourceType.Type, rType.resourceType.IsDeleted);
        foreach (var res in rType.resources)
        {
            Console.WriteLine("\tResourceId = {0}\tName = {1}\t\tSortOrder = {2}\tTypeId = {3}\tIsDeleted = {4}",
                res.ResourceId, res.Name, res.SortOrder, res.TypeId, (res.IsDeleted == null)? "NULL" : res.IsDeleted.ToString());
        }
    }
    
    // The results of the above code
    ID = 1	Type = ResourceType	IsDeleted = False
    	ResourceId = 1	Name = Badminton 1		SortOrder = 1	TypeId = 1	IsDeleted = NULL
    	ResourceId = 2	Name = Badminton 2		SortOrder = 2	TypeId = 1	IsDeleted = False
    	ResourceId = 4	Name = Badminton 3		SortOrder = 4	TypeId = 1	IsDeleted = NULL
    	ResourceId = 5	Name = 123412345555		SortOrder = 3	TypeId = 1	IsDeleted = NULL
    	ResourceId = 6	Name = sdfadsfdsf		SortOrder = 5	TypeId = 1	IsDeleted = NULL
    	ResourceId = 7	Name = asdfasfasfw		SortOrder = 6	TypeId = 1	IsDeleted = NULL
    ID = 2	Type = Resource	IsDeleted = False
    	ResourceId = 3	Name = Tennis 1		SortOrder = 1	TypeId = 2	IsDeleted = NULL
    

      


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, August 6, 2013 1:41 PM
  • Hi

    Thanks

    Now I'm getting the desired result.

    I had this:

    var results = dbquery.AsEnumerable().Select(r => r.resourceType).ToList();

    Instead of this:

    var results = dbquery.ToList();

    Now result is a List<a>

    where a = new{ResourceType ResourceType,  IEnumerable<Resource> Resource
    }


    Is it possible to have the result in an IEnumerable<ResourceType>  ?

    Because that's what's expected to return.


    Tuesday, August 6, 2013 5:55 PM
  • Hi;

    To your question, "Is it possible to have the result in an IEnumerable<ResourceType>? Not if you want the SQL server to return a filtered list of Resources. If you take a look at resourceType in the result set of the query dbquery you will find that each ResourceType contains a property which contain all associated records of Resources, unfiltered. That is why in the select clause there is a subquery to return a collection of Resources, filtered, to just contain the Resources you need. This query returns a anonymous type and really not suitable as a return type of a function. You could create a class to hold the two propertie, something like this

    public class MyFilteredResults
    {
        public ResourceType resourceType { get; set; }
        public Resources resources { get; set; }
    }
    
    // Then the select clause would change as this
    
    select new MyFilteredResults
    {
        resourceType = r,
        resources = (from res in r.Resources
                     where res.IsDeleted == null || res.IsDeleted == false
                     select res)
    };
    
    // and have the return type as IEnumerable<MyFilteredResults>. 
    
    The only way to get a return type of IEnumerable<ResourceType> from the query is to have the select clause as follows
    
    select r;
    
    Then return the return value of the query to the caller then filter the Resources property.

      


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.


    Tuesday, August 6, 2013 6:36 PM
  • Ok. Thanks a lot for the help. Really appreciate it.

    Take care!

    Tuesday, August 6, 2013 9:42 PM
  • Hi;

    Not a problem, glad I was able to help.

    Please mark the post that answered your question as "Mark As Answer" and also "Mark As Helpful".

    Thank you.


    Fernando (MCSD)

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

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Tuesday, August 6, 2013 10:03 PM