如何转换在SQL多个内部连接到LINQ？ [英] How do I convert multiple inner joins in SQL to LINQ?

问题描述

我有LINQ到SQL的基础知识了，但我一直在努力试图让连接来正常工作。我想知道如何转换以下为LINQ到SQL（最好使用方法链，因为这是我的preferred格式）。

  SELECT c.CompanyId，c.CompanyName，
            p.FirstName +''+ p.LastName为AccountCoordinator，
            p2.FirstName +''+ p2.LastName作为的AccountManager
从dbo.CompaniesÇ
INNER JOIN dbo.Persons p
ON c.AccountCoordinatorPersonId = p.PersonId
INNER JOIN dbo.Persons P2
ON c.AccountManagerPersonId = p2.PersonId
 

解决方案

使用查询语法：

 从dbo.CompaniesÇ
加入p在dbo.Persons上c.AccountCoordinatorPersonId等于p.PersonId
加入P2在dbo.Persons上c.AccountManagerPersonId等于p2.PersonId
新选择
{
    c.CompanyId，
    c.CompanyName，
    AccountCoordinator = p.FirstName +''+ p.Surname，
    的AccountManager = p2.FirstName +''+ p2.Surname
}
 

使用方法链接：

  dbo.Companies.Join（dbo.Persons，
                   C =＆GT; c.AccountCoordinatorPersonId，
                   P =＆GT; p.PersonId，
                   （C，P）=＆GT;新
                   {
                       公司= C，
                       AccountCoordinator = p.FirstName +''+ p.Surname
                   }）
             。加入（dbo.Persons，
                   C =＆GT; c.Company.AccountManagerPersonId，
                   P2 =＆GT; p2.PersonId，
                   （C，P2）=＆GT;新
                   {
                       c.Company.CompanyId，
                       c.Company.CompanyName，
                       c.AccountCoordinator，
                       的AccountManager = p2.FirstName +''+ p2.Surname
                   }）;
 

I've got the basics of LINQ-to-SQL down, but I've been struggling trying to get JOINs to work properly. I'd like to know how to convert the following to LINQ-to-SQL (ideally using method chaining, as that is my preferred format).

SELECT      c.CompanyId, c.CompanyName,
            p.FirstName + ' ' + p.LastName as AccountCoordinator,
            p2.FirstName + ' ' + p2.LastName as AccountManager
FROM        dbo.Companies c
INNER JOIN  dbo.Persons p
ON          c.AccountCoordinatorPersonId = p.PersonId
INNER JOIN  dbo.Persons p2
ON          c.AccountManagerPersonId = p2.PersonId

解决方案

Using query syntax:

from c in dbo.Companies
join p in dbo.Persons on c.AccountCoordinatorPersonId equals p.PersonId
join p2 in dbo.Persons on c.AccountManagerPersonId equals p2.PersonId
select new
{
    c.CompanyId,
    c.CompanyName,
    AccountCoordinator = p.FirstName + ' ' + p.Surname,
    AccountManager = p2.FirstName + ' ' + p2.Surname
}

Using method chaining:

dbo.Companies.Join(dbo.Persons, 
                   c => c.AccountCoordinatorPersonId,  
                   p => p.PersonId,  
                   (c, p) => new 
                   {  
                       Company = c,  
                       AccountCoordinator = p.FirstName + ' ' + p.Surname  
                   })
             .Join(dbo.Persons,  
                   c => c.Company.AccountManagerPersonId,  
                   p2 => p2.PersonId,  
                   (c, p2) => new 
                   {  
                       c.Company.CompanyId,  
                       c.Company.CompanyName,  
                       c.AccountCoordinator,  
                       AccountManager = p2.FirstName + ' ' + p2.Surname 
                   });

这篇关于如何转换在SQL多个内部连接到LINQ？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！