.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

join 3 table in LINQ

Posted By:      Posted Date: October 10, 2010    Points: 0   Category :ASP.Net
 

hi

i use Default membership provider for my membership

i add a profile table for some more information from user and it related with UserId with aspnet_Users

to get data from my database i use  Entity Data Model

now i want to write a Linq to get data from all 3 tables

can you help me to know how to write this guerry

thanks

 




View Complete Post


More Related Resource Links

LINQ to Entity - using join for multiple table

  

I have my SQL query which needs to be conerted to Linq to Entity,

select pfr_sa.SID,pfr_sa.SourceGroupID,tx.txroll_cadaccountnumber,tx.txroll_StreetNumb

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.....
                          

LINQ Sorting After Goup w/ Table Reference

  

Having a little trouble here.  I am taking a table (Articles) and grouping all the results by the key that references another table (ArticleSectionCategories).  I then want to sort these results by the OrderBy field of the referenced table (ArticleSectionCategory.OrderBy).  This is what I am using right now but it is not working.  Any thoughts?

 

                var GroupedArticles = from A in DC.Articles
                                     where A.PackagesId == packageId
                                     group A by A.ArticleSectionCategoriesId into G
                                     select G.OrderBy(C => C.ArticleSectionCategory.OrderBy);


 


select max record to join another table sybase

  
select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

How we Update DataContext's Table Implicitly in Linq using .Net 3.5..?

  
Hi All.. My problem is i am working on Linq,as we know to use any table in linq we have to drag and drop it on our datacontext's Designer File,and at run time we got its value,till here no any problem,main point is if any changes in respective data source's schema has made ,then on datacontext we will not get that changes,for it we have to change this datacontext manually by by again drag and drop,it will not happen implicitly when this changes has been made on the data source. so please tell me how i solve this problem so that datacontext change implicitly without any over head  using .net 3.5 . 

How 2 join Multiple Keys based table???

  
I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

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

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

  
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.  

T-SQL 2005 for same table join?

  
 I have below table with two columns.. Type      Code AB        Company_chris BC        Company_chis DE        Company_chis AB        Company_bob AB         Company_James BC        Company_James AB         Company_mark DE         Coampny_mark BC        Company_scott Unique value in TYPE column : AB , BC, DE Primary Key is :  TYPE and CODE I’m looking output in result query ......... Code                  Type1     Type2     Type3 Company_chris      AB         BC           DE Company_bob      AB         NULL       NULL Company_mark      AB         NULL       DE Company_scott      NULL      BC        NULL   Any t-sql 2005? Thanks.

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

how to return records in squence of inner join table?

  
Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

Join Filter for n to n Table

  
Hi all, We have a merge publication system in SQL 2008 R2. Publication is Enterprise Edition, Client's are Express Edition. Basically, we have 3 tables Roles, UserRoles,Users. Role : RoleID, RoleName columns User : UserID, UserName columns UserRole : UserID,RoleID columns   I want to filter these tables by HOST_NAME() function, which is equal to UserID for each subscription. IF i set Dynamic Filtering for User table (UserID=HOSTNAME()) , i receive only 1 row in User table in subscription database, ok. And also i set same Filter to UserRole table, and i can get only needed RoleID's to UserRole table, ok. But when i want to receive only necessary Roles to subscription database Role table, it does not work. I tried to use a Subquery, but i understood that it is a static filtering method. I tried Join filter for Role table, but i always receive all roles to Role table. I tried "SELECT <published_columns> FROM [dbo].[Role] INNER JOIN [dbo].[UserRole] ON [Role].[RoleID] = [UserRole].[RoleID]". But it receives all roles to Role table. I tried several Join filters for these tables but can't find a solution. How can i handle this?   Best regards.      

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

join - delete table(s)

  
I have two table:Parent has the key Id, UserIdChild has PaymentId, UserIdTo view the data using DropDownList.I want to delete information that is selected in the DropDownList (SelectedValue).My code:        products_to_delete var p = from and db.tbl_Payments                                 WHERE p.PaymentType.Contains (DropDownList1.SelectedValue)                                 Join in and db.tbl_PaymentDetails                                 He p.ID equals s.PaymentId                                 select p;        db.tbl_Payments.DeleteAllOnSubmit (products_to_delete);        db.SubmitChanges ();This code onl

3 table join how to question

  
I have an sql statement I want to convert to LINQ (VB.Net).  I cannot figure it out and was hoping for some help.SELECT COUNT(*) AS Expr1, SiteInfo.SiteName FROM Advice INNER JOIN InformixSource ON Advice.DSSERVER = InformixSource.PODServerName INNER JOIN SiteInfo ON InformixSource.SiteNum = SiteInfo.SiteNum GROUP BY SiteInfo.SiteName, SiteInfo.SiteNum ORDER BY SiteInfo.SiteNum  

Inline table valued function, full outer join and cross apply

  
Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb
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