none
How to perform sub-query in Linq to Sql? RRS feed

  • Question

  • Hi,

    I have a Customer table (with customer_id, first name, last name and so on), and CustomerOrder table (customer_id, order date and so on), there is one-to-many relationship between the tables.

    I want to select customer information plus the latest order date. In sql statement I can do something like

    select customer_id, first_name, last_name,
    <strong>(select top 1 order_date from customer_order where customer_id = customer.customer_id) as last_order_date</strong>
    from customer
    where last_name like '%smith%'
    

    How can I so similar thing in Linq to Sql?

    Thanks

    Hardy


    Welcome to help me with my open source project at http://code.google.com/p/batch-image-watermark-processor/
    Sunday, October 17, 2010 2:36 PM

Answers

  • hi Hardy

     

     

             var result = from n in customer
    
                             where n.last_name.Contains("smith")
    
                             select new { n.customer_id, n.first_name, n.last_name, last_order_date = (from m in customer_order where m.customer_id == n.customer_id select m.order_date).FirstOrDefault() };
    

     

     

    Regards

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. Regards, Alireza
    Sunday, October 17, 2010 2:55 PM
  • Hi Hardy,

    You can also try something like this:

    var results = from p in db.customers
           where p.last_name.Contains("smith")
           select new
           {
             p.customer_id,
             p.first_name,
             p.last_name,
             last_order_date = p.customer_orders.First().order_date
           };
    
    


    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, October 18, 2010 9:23 AM
    Moderator

All replies

  • hi Hardy

     

     

             var result = from n in customer
    
                             where n.last_name.Contains("smith")
    
                             select new { n.customer_id, n.first_name, n.last_name, last_order_date = (from m in customer_order where m.customer_id == n.customer_id select m.order_date).FirstOrDefault() };
    

     

     

    Regards

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. Regards, Alireza
    Sunday, October 17, 2010 2:55 PM
  • Hi Hardy,

    You can also try something like this:

    var results = from p in db.customers
           where p.last_name.Contains("smith")
           select new
           {
             p.customer_id,
             p.first_name,
             p.last_name,
             last_order_date = p.customer_orders.First().order_date
           };
    
    


    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, October 18, 2010 9:23 AM
    Moderator
  • Hi Hardy,

    I am writting to follow up the post. Does the above suggestion work ?

    Please feel free to let me know if you need help.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, October 21, 2010 3:49 AM
    Moderator