locked
How to filter data using Include and Where? (Again) RRS feed

Answers

  • Hi Peter;

    The following code snippet should do what you want.

    var results = from o in ObjectContext.Occupations
    	   let CodeU = o.OccupationsCategories.Any(c => c.Code == "u" && c.Valid == 1)
    	   select new
    	   {
    		Title = o.Title,
    		Code = CodeU 
    		   ? o.OccupationsCategories.Where( c => c.Code == "u" ).FirstOrDefault( ).Code : 
    		   o.OccupationsCategories.Where( c => c.Code == "t" ).FirstOrDefault( ).Code,
    		Cat = CodeU 
    		   ? o.OccupationsCategories.Where( c => c.Code == "u" ).FirstOrDefault( ).Cat 
    		   : o.OccupationsCategories.Where( c => c.Code == "t" ).FirstOrDefault( ).Cat
    	   };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Alan_chen Tuesday, November 1, 2011 8:29 AM
    Thursday, October 20, 2011 4:43 PM

All replies

  • Hi Peter;

    To your question, "I have a simple question: is this solved with EF4.x or is the answer from the year 2009 still valid?", Using the Include method will always return all related records from the Include path of the record in question. And yes the method in the solution posted still works.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, October 19, 2011 6:33 PM
  • OK.

    I try to explain my problem.

    I have a table Occupations:

    Id   Title
    ----------------
     1   A
     2   B
     3   C
     4   D
    
    and a 2nd table OccupationsCategories:
    Id   FK   Code   Valid   Cat
    -------------------------------
     1    1      t       1     x     <---
    
     2    2      t       1     y
     3    2      u       1     y     <---
    
     4    3      t       1     x     <---
     5    3      u       0     y
    
     6    4      t       1     z
     7    4      u       0     x
     8    4      u       1     z     <---
    

    Now I'd like to have all Occupations with

    a) Category u and Valid, or

    b) Category t (default category, exists always)

     

    This should give:

    Title   Code   Cat
    ----------------------
      A        t     x
      B        u     y
      C        t     x
      D        u     z
    

    So the question is:

    how do I filter on OccupationsCategories to get only the rows marked with <---. I don't want all related OccupationsCategories.

    Thursday, October 20, 2011 6:37 AM
  • Hi Peter;

    The following code snippet should do what you want.

    var results = from o in ObjectContext.Occupations
    	   let CodeU = o.OccupationsCategories.Any(c => c.Code == "u" && c.Valid == 1)
    	   select new
    	   {
    		Title = o.Title,
    		Code = CodeU 
    		   ? o.OccupationsCategories.Where( c => c.Code == "u" ).FirstOrDefault( ).Code : 
    		   o.OccupationsCategories.Where( c => c.Code == "t" ).FirstOrDefault( ).Code,
    		Cat = CodeU 
    		   ? o.OccupationsCategories.Where( c => c.Code == "u" ).FirstOrDefault( ).Cat 
    		   : o.OccupationsCategories.Where( c => c.Code == "t" ).FirstOrDefault( ).Cat
    	   };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Alan_chen Tuesday, November 1, 2011 8:29 AM
    Thursday, October 20, 2011 4:43 PM