What is the syntax for an inner join in LINQ to SQL

Question

I m writing a LINQ to SQL statement  and I m after the standard syntax for a normal inner join with an ON clause in C    How do you represent the following in LINQ to SQL   select DealerContact   from Dealer  inner join DealerContact on Dealer DealerID   DealerContact DealerID

User · Answer

try instead this   var dealer   from d in Dealer              join dc in DealerContact on d DealerID equals dc DealerID              select d

User · Answer

Use Linq Join operator   var q    from d in Dealer          join dc in DealerConact on d DealerID equals dc DealerID          select dc

User · Answer

You create a foreign key  and LINQ-to-SQL creates navigation properties for you  Each Dealer will then have a collection of DealerContacts which you can select  filter  and manipulate   from contact in dealer DealerContacts select contact   or  context Dealers Select d   gt  d DealerContacts    If you re not using navigation properties  you re missing out one of the main benefits on LINQ-to-SQL - the part that maps the object graph

User · Answer

To extend the expression chain syntax answer by Clever Human   If you wanted to do things  like filter or select  on fields from both tables being joined together -- instead on just one of those two tables -- you could create a new object in the lambda expression of the final parameter to the Join method incorporating both of those tables  for example   var dealerInfo   DealerContact Join Dealer                                 dc   gt  dc DealerId                                d   gt  d DealerId                                 dc  d    gt  new   DealerContact   dc  Dealer   d                               Where dc d   gt  dc d Dealer FirstName     Glenn                                  amp  amp  dc d DealerContact City     Chicago                              Select dc d   gt  new                                 dc d Dealer DealerID                                dc d Dealer FirstName                                dc d Dealer LastName                                dc d DealerContact City                                dc d DealerContact State       The interesting part is the lambda expression in line 4 of that example     dc  d    gt  new   DealerContact   dc  Dealer   d        where we construct a new anonymous-type object which has as properties the DealerContact and Dealer records  along with all of their fields     We can then use fields from those records as we filter and select the results  as demonstrated by the remainder of the example  which uses dc d as a name for the anonymous object we built which has both the DealerContact and Dealer records as its properties

User · Answer

var list    from u in db Users join c in db Customers on u CustomerId equals c CustomerId where u Username    username    select new  u UserId  u CustomerId  u ClientId  u RoleId  u Username  u Email  u Password  u Salt  u Hint1  u Hint2  u Hint3  u Locked  u Active c ProfilePic   First      Write table names you want  and initialize the select to get the result of fields

User · Answer

OperationDataContext odDataContext   new OperationDataContext                var studentInfo   from student in odDataContext STUDENTs                           join course in odDataContext COURSEs                           on student course id equals course course id                           select new   student student name  student student city  course course name  course course desc      Where student and course tables have primary key and foreign key relationship

User · Answer

var results   from c in db Companies               join cn in db Countries on c CountryID equals cn ID               join ct in db Cities on c CityID equals ct ID               join sect in db Sectors on c SectorID equals sect ID               where  c CountryID    cn ID   amp  amp   c CityID    ct ID   amp  amp   c SectorID    company SectorID   amp  amp   company SectorID    sect ID                select new   country   cn Name  city   ct Name  c ID  c Name  c Address1  c Address2  c Address3  c CountryID  c CityID  c Region  c PostCode  c Telephone  c Website  c SectorID  Status    ContactStatus c StatusID  sector   sect Name      return results ToList

User · Answer

Try this         var data   from t1 in dataContext Table1 join                   t2 in dataContext Table2 on                   t1 field equals t2 field                   orderby t1 Id select t1  ToList

User · Answer

Inner join two tables in linq C   var result   from q1 in table1              join q2 in table2              on q1 Customer Id equals q2 Customer Id              select new   q1 Name  q1 Mobile  q2 Purchase  q2 Dates

User · Answer

var Data   from dealer in Dealer join dealercontact in DealerContact on dealer ID equals dealercontact DealerID select new  dealer Id  dealercontact ContactName     ToList

User · Answer

It goes something like   from t1 in db Table1 join t2 in db Table2 on t1 field equals t2 field select new   t1 field2  t2 field3    It would be nice to have sensible names and fields for your tables for a better example      Update  I think for your query this might be more appropriate   var dealercontacts   from contact in DealerContact                      join dealer in Dealer on contact DealerId equals dealer ID                      select contact    Since you are looking for the contacts  not the dealers

User · Answer

from d1 in DealerContrac join d2 in DealerContrac on d1 dealearid equals d2 dealerid select new  dealercontract

User · Answer

And because I prefer the expression chain syntax  here is how you do it with that   var dealerContracts   DealerContact Join Dealer                                    contact   gt  contact DealerId                                   dealer   gt  dealer DealerId                                    contact  dealer    gt  contact

User · Answer

Actually  often it is better not to join  in linq that is  When there are navigation properties a very succinct way to write your linq statement is   from dealer in db Dealers from contact in dealer DealerContacts select new   whatever you need from dealer or contact     It translates to a where clause   SELECT  lt columns gt  FROM Dealer  DealerContact WHERE Dealer DealerID   DealerContact DealerID

User · Answer

Use LINQ joins to perform Inner Join   var employeeInfo   from emp in db Employees                    join dept in db Departments                    on emp Eid equals dept Eid                     select new                                          emp Ename                      dept Dname                      emp Elocation

User · Answer

var q  from pd in dataContext tblProducts join od in dataContext tblOrders on pd ProductID equals od ProductID orderby od OrderID select new   od OrderID   pd ProductID   pd Name   pd UnitPrice   od Quantity   od Price      ToList

User · Answer

basically LINQ join operator provides no benefit for SQL  I e  the following query  var r   from dealer in db Dealers    from contact in db DealerContact    where dealer DealerID    contact DealerID    select dealerContact    will result in INNER JOIN in SQL  join is useful for IEnumerable lt   because it is more efficient    from contact in db DealerContact     clause would be re-executed for every dealer But for IQueryable lt   it is not the case  Also join is less flexible

User · Answer

var data  from t in db your tableName t1             join s in db yourothertablename t2  on t1 fieldname equals t2 feldname            where condtion   tolist

User · Answer

One Best example  Table Names    TBL Emp and TBL Dep  var result   from emp in TBL Emp join dep in TBL Dep on emp id dep id select new    emp Name   emp Address  dep Department Name     foreach char item in result        to do

[c#] What is the syntax for an inner join in LINQ to SQL?

Examples related to c#

Examples related to .net

Examples related to sql

Examples related to linq-to-sql

Examples related to join