none
Linq joining RRS feed

  • Question

  • Hello All:

     

    I am fairly new to LINQ and need some direction with a query. I have a few SQL tables:

    CUSTOMERS - contains info about customers

    LEADS - contains info about leads

    VENDORS - contains info about vendors

    ADDRESSES - contains addresses for CUSTOMERS/LEADS/VENDORS, it links to the other tables with an id

     

    I need to query ADDRESSES to get the address. Then based on the id from ADDRESSES, I need to query CUSTOMERS/LEADS/VENDORS. I don't know which table the id belongs to, but I it belongs to one of them and one only. Then I need to combine the information from ADDRESSES and the correct CUSTOMERS/LEADS/VENDORS so that it can be used later.

     

    Any help would be greatly appreciated.

     

    Thank You.

    Wednesday, April 28, 2010 11:11 PM

Answers

  • Hi cod4u,

    I don't have the full picture of your constraints. But I suggest two ways you tackle this issue (depending on constraints you have).

     

    If you are designing these tables and can modify their definitions then I suggest you re-create these tables so that Customers,Leads,Vendors have a foreign key to address. That way the other tables are linked to the Addresses table and Addresses will always have a unique key.

    So that tables;

    Customer (CustomerID,AddrID,....)

    Vendors (VendorID,AddrID,.....)

    Leads (LeadID,AddrID,....)

    Addresses (AddrID,.....)

     

    If you cannot do this for whatever reason you will need to add some sort of byte/int field to determine the source table (a flag of somekind anyways).

    If you cannot do the above I find you lose some of the pleasures of using LINQ you can no longer read properties of generated entities in the nice fashion

    var Lead = (from lead in Leads where lead.LeadID==selectThisID).Single(); //you pass selectThisID with an LeadID to get the related entity object

    var LeadAddress = Lead.Address.First(); //here you can now read all of the properties of LeadAddress which are the columns in the Addresses table definition.

    ---------------

    Whilst you lose some of the normal benefits of LINQ there is an alternative if you do not want to alter the tables above.

    If you create a stored procedure in SQL Server that does exactly what you want you can then bring this into C# using LINQ. More detail on this can be found on this blog: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    But if you are limited you can use Stored Procedures to make your life easier with LINQ even though your structure maybe non-conformant. This feature of LINQ I believe is one of the best. It is the best way to re-structure you data before you pass it down to your C# application, then with LINQ you can recieve the result of the Stored Procedure as an object. Normally the nameofyourstoreprocedureResult (note the 'Result' suffix).

    So if you are able to make a query in T-SQL that does what you want....then you already have the ability to use that in your C# application.

     

    Feel free to ask any Q's ill do my best to reply asap.

     

    Cheers & Gluck

     

    Thursday, April 29, 2010 1:50 AM

All replies

  • Hi cod4u,

    I don't have the full picture of your constraints. But I suggest two ways you tackle this issue (depending on constraints you have).

     

    If you are designing these tables and can modify their definitions then I suggest you re-create these tables so that Customers,Leads,Vendors have a foreign key to address. That way the other tables are linked to the Addresses table and Addresses will always have a unique key.

    So that tables;

    Customer (CustomerID,AddrID,....)

    Vendors (VendorID,AddrID,.....)

    Leads (LeadID,AddrID,....)

    Addresses (AddrID,.....)

     

    If you cannot do this for whatever reason you will need to add some sort of byte/int field to determine the source table (a flag of somekind anyways).

    If you cannot do the above I find you lose some of the pleasures of using LINQ you can no longer read properties of generated entities in the nice fashion

    var Lead = (from lead in Leads where lead.LeadID==selectThisID).Single(); //you pass selectThisID with an LeadID to get the related entity object

    var LeadAddress = Lead.Address.First(); //here you can now read all of the properties of LeadAddress which are the columns in the Addresses table definition.

    ---------------

    Whilst you lose some of the normal benefits of LINQ there is an alternative if you do not want to alter the tables above.

    If you create a stored procedure in SQL Server that does exactly what you want you can then bring this into C# using LINQ. More detail on this can be found on this blog: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    But if you are limited you can use Stored Procedures to make your life easier with LINQ even though your structure maybe non-conformant. This feature of LINQ I believe is one of the best. It is the best way to re-structure you data before you pass it down to your C# application, then with LINQ you can recieve the result of the Stored Procedure as an object. Normally the nameofyourstoreprocedureResult (note the 'Result' suffix).

    So if you are able to make a query in T-SQL that does what you want....then you already have the ability to use that in your C# application.

     

    Feel free to ask any Q's ill do my best to reply asap.

     

    Cheers & Gluck

     

    Thursday, April 29, 2010 1:50 AM
  • Hi,

     

    I am writing to check the status of the issue on your side.  Allan's post is very helpful on this case.   

     

    If you need further assistance, please feel free to let us know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    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.
    Wednesday, May 5, 2010 2:01 AM
    Moderator