none
how to get result from 2 tables with FK By Linq To Entites? RRS feed

  • Question

  • I have  photos table and CustomerInPhotoes table,

    I want to display all the photos that's relates to customerID,

    In SQL its really simple:

     

    select * from Photos inner join CustInPhotoes ON Photos.PhotoId = CustInPhotoes.PhotoId
    where CustInPhotoes.Id=73763
    
    but when I try to write it in linq sentenct , I got wrong resaults.
    I try this:
    List<Photos> PhotoesList = (from photo in _Db.Photoes
                                                from cust in _Db.CustInPhotoes
                                                where cust.ID == custId
                                                select photo).ToList();
    this gives me all the records on the tables photos multyply by the num of records related to the customer,
    so if customer has 3 pictures and thers is 4 pictures on the table I'll get 12 records back.
    thanks in advance!


     


    • Edited by Gooloosh Tuesday, November 29, 2011 8:48 AM
    Tuesday, November 29, 2011 8:45 AM

Answers

  • Hi,

    There are several ways to do this. Here you have some examples that should work:

    //Using the join statement
    List<Photos> PhotoesList = (from photo in _Db.Photoes
                                  join cust in _Db.CustInPhotoes on photo.PhotoId equals cust.PhotoId
                                where cust.ID == custId
                                select photo).ToList();
    
    //Using navigation properties of Photo
    //Requirement: You have a navigation property in Photo that has the name CustInPhotoes 
    List<Photos> PhotoesList = (from photo in _Db.Photoes
                                where photo.CustInPhotoes.Any(row => row.ID == custId)
                                select photo).ToList();
    
    //Using navigation property of CustInPhoto
    //Requirement: You have a navigation property to photo in your CustInPhoto object
    List<Photos> PhotoesList = (from cust in _Db.CustInPhotoes
                                where cust.Id == custId
                                select cust.Photo).ToList();
    

    I hope this helps you a bit on the way!


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by Gooloosh Tuesday, November 29, 2011 2:18 PM
    Tuesday, November 29, 2011 2:11 PM

All replies

  • Hi,

    There are several ways to do this. Here you have some examples that should work:

    //Using the join statement
    List<Photos> PhotoesList = (from photo in _Db.Photoes
                                  join cust in _Db.CustInPhotoes on photo.PhotoId equals cust.PhotoId
                                where cust.ID == custId
                                select photo).ToList();
    
    //Using navigation properties of Photo
    //Requirement: You have a navigation property in Photo that has the name CustInPhotoes 
    List<Photos> PhotoesList = (from photo in _Db.Photoes
                                where photo.CustInPhotoes.Any(row => row.ID == custId)
                                select photo).ToList();
    
    //Using navigation property of CustInPhoto
    //Requirement: You have a navigation property to photo in your CustInPhoto object
    List<Photos> PhotoesList = (from cust in _Db.CustInPhotoes
                                where cust.Id == custId
                                select cust.Photo).ToList();
    

    I hope this helps you a bit on the way!


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    • Marked as answer by Gooloosh Tuesday, November 29, 2011 2:18 PM
    Tuesday, November 29, 2011 2:11 PM
  • I choose the second option.

    works great!!!

    thanks for the all options it;s good to enrich the knowledge

     

    Thank you very much!

    Tuesday, November 29, 2011 2:21 PM