none
Newb question - How do I get child object values? RRS feed

  • Question

  • I have a web service that return list of employees and 3 tables Survey,SubCategory,Category and associations between these tables.

    I want to get all the categoryNames which is a field in the Category table.

    trying to do this

     

    // get employees

     

    var employees =

     

    EmployeesCL.GetAllEmployees().AsEnumerable();

     

    var query = surveys.Join(

    employees,

    survey => survey.USERID,

    employee => employee.USERID,

    (survey, employee) =>

     

    new

    {

    categoryname = survey.SurveyDetails.Select(sd => sd.Field.SubCategory.Category.CATEGORY1) // this doesn't work

    });

     

     


    How do I do this?

    Thursday, December 10, 2009 11:20 PM

Answers

  • Hello,

    I suspect the employees is a local collection returned from the web service, correct?  If so, the local collection is not supported in the IQueryable LINQ to SQL join query.  Instead, we can translate it into a Contains (T-SQL WHERE IN) query. 

    ===================================================================
    var query = from survey in surveys
                         where employees.Select(employee => employee.USERID).Contains(survey.USERID)
                         select new
                         {
                             categoryname = survey.SurveyDetails.Select(sd => sd.Field.SubCategory.Category.GATEGORY1)
                         }
    ===================================================================

    For detailed information about this approach, please see http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/.

    If it does not solve the problem, please provide us with more detailed information about the data structure of the LINQ to SQL .dbml and the exception that you received.


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 14, 2009 2:33 PM
    Moderator

All replies

  • something like this... ?

    var result = from c in db.Category
    join sc in db.SubCategory on c.id equals sc.categoryid
    select new {
    sc.Name
    };

    -DishanF-
    Friday, December 11, 2009 10:10 AM
  • Hello,

    I suspect the employees is a local collection returned from the web service, correct?  If so, the local collection is not supported in the IQueryable LINQ to SQL join query.  Instead, we can translate it into a Contains (T-SQL WHERE IN) query. 

    ===================================================================
    var query = from survey in surveys
                         where employees.Select(employee => employee.USERID).Contains(survey.USERID)
                         select new
                         {
                             categoryname = survey.SurveyDetails.Select(sd => sd.Field.SubCategory.Category.GATEGORY1)
                         }
    ===================================================================

    For detailed information about this approach, please see http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/.

    If it does not solve the problem, please provide us with more detailed information about the data structure of the LINQ to SQL .dbml and the exception that you received.


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 14, 2009 2:33 PM
    Moderator