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

2018-12-31 08:16发布

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

18条回答
几人难应
2楼-- · 2018-12-31 08:23

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.

查看更多
看风景的人
3楼-- · 2018-12-31 08:24

And because I prefer the expression chain syntax, here is how you do it with that:

var dealerContracts = DealerContact.Join(Dealer, 
                                 contact => contact.DealerId,
                                 dealer => dealer.DealerId,
                                 (contact, dealer) => contact);
查看更多
无与为乐者.
4楼-- · 2018-12-31 08:24

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}
查看更多
零度萤火
5楼-- · 2018-12-31 08:26

try instead this,

var dealer = from d in Dealer
             join dc in DealerContact on d.DealerID equals dc.DealerID
             select d;
查看更多
后来的你喜欢了谁
6楼-- · 2018-12-31 08:28
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.

查看更多
旧人旧事旧时光
7楼-- · 2018-12-31 08:30

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<> because it is more efficient:

from contact in db.DealerContact  

clause would be re-executed for every dealer But for IQueryable<> it is not the case. Also join is less flexible.

查看更多
登录 后发表回答