.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

LINQ-to-SQL: How do you join more than 2 tables?

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :ASP.Net
 
Hi there, I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:  SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17'; can be expressed as: var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 }; But how would I join 3 or more tables using LINQ? For example: SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37'; I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement. Any help will be greatly appreciated! Thanks,- K.  


View Complete Post


More Related Resource Links

Entity Framework - how to join tables without LINQ and with only string?

  

Hi all,

I have a question about Entity Framework. Please answer if you know answer on this. I have such query :

    String queryRaw =
        "SELECT " +
        "p.ProductName AS ProductName " +
        "FROM ProductEntities.Products AS p " +
        "INNER JOIN CategoryEntities.Categories AS c " +
        "ON p.CategoryID = c.CategoryID ";
   
    ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryRaw, entityContext);
   
    GridView1.DataSource = query;
    GridView1.DataBind();



Particularly I want to join few tables in one query, but I can NOT use LINQ and can NOT use ObjectQuery with objects mapped to DB fields inside my query. Because each entity creates dynamically. So this is what i can NOT use :

http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12

http://msdn.microsoft.com/en-us/library/bb896339%28v=VS.90%29.aspx

The question is can I use something like this instead of using objects?

query.Join ("INNER JOIN CategoryEntities.Category ON p.CategoryID = c.CategoryID ");

Join Two Tables and Prepare Report

  

Hello,

            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.


How do I join those two tables and get that field value in the existing select query.?


TIA


Linq join fields for return data

  

Hello,
  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Cat_GUID,
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....
                          

"HOW TO JOIN THREE PIVOT TABLES"

  
Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

any alternative for join tables?

  
 Hi guys, is  there any alternative for joining tables in ado.net? The problem is i have 2 tables: Transaction pkTransactionID TransactionDate FKTransactionCategory : refers to PKCAtegoryID   Category PKCAtegoryID CategoryName   I need to show a joined information from 2 tables in a gridview and be able to update data in tables. I can join them using linq to entity and show them in gridview. But i need to handle update and insert by myself then. Is there any way to make mapping so i can handle the data in those 2 tables as one table and bind it to bindingsource? Thanks in advance.      

Join two tables

  
I have following SQL command   SELECT     software_communication_comments.observation_comment,child_communication_observation_outcomes.communication_outcome_status, software_community_comments.observation_comment ,child_community_observation_outcomes.community_outcome_status From     child_observation,Child_Basic_Information  ,group_table ,software_communication_comments ,child_communication_observation_outcomes ,software_community_comments,child_community_observation_outcomes  where (child_observation.observation_ID = '2'  and   child_observation.child_ID = Child_Basic_Information.child_ID and group_table.group_ID = Child_Basic_Information.Group_ID) AND (child_communication_observation_outcomes.child_observation_ID = '2' AND software_communication_comments.comment_ID =  child_communication_observation_outcomes.communication_obser_outcomes_ID) AND ( child_community_observation_outcomes.child_observation_ID = '2' AND software_community_comments.comment_ID =  child_community_observation_outcomes.community_obser_outcomes_ID   ) This is working well if both table (child_community_observation_outcomes and child_communication_observation_outcomes) have records belongs to child_observation_ID =2 ,if one table dont have a relevent record it doesn't display anything. I need to ret

How to MAP Tables in Linq .dbml file

  
I am using VS 2010I've created LiNQ .dbml file via   .. Model>Add New Item>Data>LINQ to SQL ClasesI've put table on designer surface using drag & drop.I've done all this from one tutorial. There Is Mapping with arrows between tables in tutorial. How can i do this.. ???Can Any one Help Me Please..Thanks

Get Distinct value from a Group-Join syntax Linq

  
Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 

LINQ left outer join question

  
I can do this in other ways, but am looking for the "LINQ" way of doing this: Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId) I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).  The SQL is: select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=... (from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing) but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId) Any help would be appreciated,Reuven

How does Linq to Sql Determine which tables to write to

  
I was wondering how Linq to Sql determines which tables to write to when given primary/foreign Key relationships.  I can (hopefully) better explain using an example.  I have the following tables: Customer CustomerStatus CustomerType CustomerAddresses Therefore I have a Customer Entity, a CustomerStatus entity, a CustomerType Entity and a List of CustomerAddresses.  In my code, I load up the Customer Entity and the List of CustomerAddresses because that's all I need.  For some reason Linq to Sql is trying to insert a new Customer Status record.  I have no idea why its doing this.  Can anyone please explain to me how linq to sql determines which table(s) will be written to based on the entities above? Thanks

HOW TO CROSS JOIN 2 TABLES...(AND KEEP ALL COLUMNS)

  
TABLE 1SELECT I.STUNA    ,S.SNO         ,SUM(B.AMT * Y.YEAR) AS AMT  FROM STUDENT S  LEFT JOIN INFO I ON S.SNO = I.SNO  LEFT JOIN BONUS B ON S.SNO = B.SSNO  LEFT JOIN YEAR  Y ON S.SNO = Y.SSNO  STUNA       SNO      AMTJOHN         A        10LISA         B        20ALLEN        C       100TABLE 2SNO   AMTA     1B     2C     3D     5I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)STUNA       SNO      AMTJOHN         A        11LISA         B        22ALLEN        C       103EDDIE        D         5I TRIED USE THIS QEURY SELECT I.STUNA    ,S.SNO         ,SUM(B

Left Outer Join in LINQ + Entities

  
HiI have found quite a bit of info on the net, but I can't get it to work. Here's the normal SQL query:SELECT cl.ID AS ClientID, cl.Name, cl.Surname, CLC1.PHONECODE + ' ' + CLC1.PHONENUMBER AS WorkNum, CLC2.PHONECODE + ' ' + CLC2.PHONENUMBER AS HomeNum, CLC3.PHONECODE + ' ' + CLC3.PHONENUMBER AS CellNum, CLC1.PREFERRED AS WorkNumPref, CLC2.PREFERRED AS HomeNumPref, CLC3.PREFERRED AS CellNumPref, veh.ID AS VehicleID, veh.MAKE, veh.MODEL, veh.YEAR, veh.REGNO, ws.ID AS WorkshopID, ws.JOBTYPE, ws.SERVICEADVISOR, ws.TIMEFINISHED, ws.TECHNICIAN, fs.ID as FollowupID, fs.TYPE, fs.DESCRIPTION, fs.SCHEDULEDATE, fs.WRONGNUMBERFLAG FROM CLIENTS AS cl LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC1 ON cl.ID = CLC1.CLIENTID AND CLC1.TYPE = 'W' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC2 ON cl.ID = CLC2.CLIENTID AND CLC2.TYPE = 'H' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC3 ON cl.ID = CLC3.CLIENTID AND CLC3.TYPE = 'C' INNER JOIN CLIENTVEHICLES AS clveh ON cl.ID = clveh.CLIENTID INNER JOIN VEHICLES AS veh ON clveh.VEHICLEID = veh.ID INNER JOIN WSBOOKINGS AS ws ON clveh.ID = ws.CLIENTVEHICLEID INNER JOIN FOLLOWUPSCHEDULE AS fs ON fs.WSBOOKINGID = ws.ID WHERE fs.ID = 2 There is a multiple join between CLIENTS and CLIENTCONTACTNUMBERS so it would return a single row of data. If you were to do a normal join on CLIENTS and C

left outer join linq

  
 Hi   I try to get this exsisting linq query to use a left outer join without success, can anyone show me how its supposed to be?   Dim product = (From f In db.tbl_Formats _ Join p In db.tbl_Products On f.FormatID Equals p.FormatID Where p.NodeId = iNodeID _ Select p, f.FormatName).SingleOrDefault   The FormatID is not always present and I therefore need a left outer join on the Formats table.   Best Regards

linq - delete two tables not work

  
BurnbetDataContext db = new BurnbetDataContext(); var q = (from c in db.GetTable<tbl_Payment>() where c.UserId == "fc44c835-8e80-45ee-af84-68339a09c053" select c).Single<tbl_Payment>(); db.GetTable<tbl_Payment>().DeleteOnSubmit(q); db.SubmitChanges(); In App_Code I BurnbetDataContext.I have two tables and tbl_PaymentDetails tbl_PaymentAssociation is:Parent Child Class -->>> UserId UserId ClassData in Table tbl_PaymentDetails (child) does not delete.      

Join linq queries with same field name

  
Hi everyone-I'm trying to join three Linq queries together.  One of the fields (rating) is the same name for each so when I add b.rating as you'll see below, I get a squiggly that says "Anonymous type member or property 'rating' is alread declared.   Dim customerCategoryA= (From a In dc.CategoryARatings _                                  Where a.userID = userID _                                  Select New With {a.productID, a.rating, a.ratingComment}) Dim customerCategoryB = (From b In dc.CategoryBRatings _                                 Where b.userID = userID _                                 Select New With {b.productID, b.rating}) Dim DataForGridview = From p In Products _   

Join two fact tables

  

Hi,

 

I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.

 

Fct_LoanSales

AccountNumber                       char

Join Tables For Definitions (Data Table/Definition Table)

  

Hi,

I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Table_Spec_Data
Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Table_Name_Data
Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)

 

SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend