locked
Including only specific properties in LINQ-to-SQL joins

    Question

  • I'm running the following query which is joing the Task table to the User table:

    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Task>(o => o.User);
    Domain.LoadOptions = options;
    
    IQueryable<Task> query = 
        from o in Domain.Tasks
        select o;
    This of course eagerly loads all Tasks and all joined Users.    How can I join only a single property, such as User.Username?

    I've tried "options.LoadWith<Task>(o => o.User.Username);" But this throws an exception.  I also need to make sure the query returns Task objects though instead of anonymous objects.
    Thursday, November 26, 2009 12:24 AM

Answers

  • Hello Exsurgo,

     

    Welcome to LINQ Project General forum!

     

    I think the anonymous type is exactly designed for such a scenario.  The LoadWith method cannot help us only join some single properties, instead it loads the whole relationship.   From your description, you are trying to only return the Task objects.  However, we cannot store the UserName data in the Task.User object because the DataContext needs to track the User objects by its primary keys and a User object cannot exist with only the User.UserName property and no other properties.

     

    I have one workaround here to return a Task query but we can also get the User.UserName information.   First I create a new property named UserName for the Task class.  Then I load the UserName information with the help of anonymous type and multiple line lambda expression. 

    =======================================================================================================
    var query = (from task in db.Tasks

                 select new { Task = task, UserName = task.User.UserName }).AsEnumerable().

                 Select(t =>

                 {

                     t.Task.UserName = t.UserName;

                     return t.Task;

                 });

     

    public partial class Task

    {

        public string UserName { get; set; }

    }
    =======================================================================================================

     

    To be honesty, using the anonymous type is easier.  J 

     

    If you have any questions, please feel free to let me now.   BTW, if you have any LINQ to SQL issues, I recommend you directly post at LINQ to SQL forum.

     

     

    Have a great day!

     

     

    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.
    Thursday, November 26, 2009 1:53 AM
    Moderator
  • Hello Jonathan,

     

    If the Task.User.UserName is finally what you want to include, I suggest you convert the IQueryable query into one IEnumerable one and then make the multiple-line lambda expression when all the joins have been performed.   However, if you want the UserName to be the JOIN condition in the LINQ queries, I think we can directly use it like (join … on task.User.UserName equals … ). 

     

    Because the multiple-line lambda expression is not support in the IQueryable LINQ to SQL query, we need to load the entities into memory (IEnumerable collection).   Actually, it’s effect is the same as a foreach loop. 

     

    If you have any questions, please be free to tell me.

     

    Have a nice weekend!

     

     

    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.
    • Marked as answer by Exsurgo Tuesday, December 01, 2009 7:32 PM
    Saturday, November 28, 2009 2:16 AM
    Moderator

All replies

  • Hello Exsurgo,

     

    Welcome to LINQ Project General forum!

     

    I think the anonymous type is exactly designed for such a scenario.  The LoadWith method cannot help us only join some single properties, instead it loads the whole relationship.   From your description, you are trying to only return the Task objects.  However, we cannot store the UserName data in the Task.User object because the DataContext needs to track the User objects by its primary keys and a User object cannot exist with only the User.UserName property and no other properties.

     

    I have one workaround here to return a Task query but we can also get the User.UserName information.   First I create a new property named UserName for the Task class.  Then I load the UserName information with the help of anonymous type and multiple line lambda expression. 

    =======================================================================================================
    var query = (from task in db.Tasks

                 select new { Task = task, UserName = task.User.UserName }).AsEnumerable().

                 Select(t =>

                 {

                     t.Task.UserName = t.UserName;

                     return t.Task;

                 });

     

    public partial class Task

    {

        public string UserName { get; set; }

    }
    =======================================================================================================

     

    To be honesty, using the anonymous type is easier.  J 

     

    If you have any questions, please feel free to let me now.   BTW, if you have any LINQ to SQL issues, I recommend you directly post at LINQ to SQL forum.

     

     

    Have a great day!

     

     

    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.
    Thursday, November 26, 2009 1:53 AM
    Moderator
  • Thanks for this Lingzhi, the solution above will work well.    One other quick question, is there any way to do this without converting the query to IEnumerable first?   I need to dynamically build the query depending on certain parameters, so mulitple joins might or might not be needed.

    Thanks again.

    ~Jonathan

    Friday, November 27, 2009 4:47 PM
  • Hello Jonathan,

     

    If the Task.User.UserName is finally what you want to include, I suggest you convert the IQueryable query into one IEnumerable one and then make the multiple-line lambda expression when all the joins have been performed.   However, if you want the UserName to be the JOIN condition in the LINQ queries, I think we can directly use it like (join … on task.User.UserName equals … ). 

     

    Because the multiple-line lambda expression is not support in the IQueryable LINQ to SQL query, we need to load the entities into memory (IEnumerable collection).   Actually, it’s effect is the same as a foreach loop. 

     

    If you have any questions, please be free to tell me.

     

    Have a nice weekend!

     

     

    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.
    • Marked as answer by Exsurgo Tuesday, December 01, 2009 7:32 PM
    Saturday, November 28, 2009 2:16 AM
    Moderator
  • Great, thanks again Lingzhi.  This has been very helpful.
    Tuesday, December 01, 2009 7:32 PM
  • It's my pleasure!

    Have a nice day!

     

    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.
    Thursday, December 03, 2009 12:35 AM
    Moderator
  • Hello Jonathan,

     

    If the Task.User.UserName is finally what you want to include, I suggest you convert the IQueryable query into one IEnumerable one and then make the multiple-line lambda expression when all the joins have been performed. However, if you want the UserName to be the JOIN condition in the LINQ queries, I think we can directly use it like (join … on task.User.UserName equals … ).

     

    Because the multiple-line lambda expression is not support in the IQueryable LINQ to SQL query, we need to load the entities into memory (IEnumerable collection). Actually, it’s effect is the same as a foreach loop.

     

    If you have any questions, please be free to tell me.

     

    Have a nice weekend!

     

     

    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.

    Thanks for your effort! It helps me out of the problem.
    Thursday, February 10, 2011 9:48 PM