在LINQ的右表中最多包含一行的左连接

debugcn 发表于 Dev

用户名

朋友，我有一个客户表和一个关系表，用于保存向其发送调查链接的客户。同一份调查可能已多次发送给同一位客户。或者，某些客户可能没有发送任何调查链接。我的目标是将所有客户带一次，如果向其中一些客户发送了问卷，我只想带一个创建日期最大的客户。记录数必须等于客户数。我写了查询，但是很难翻译linq或。你能帮我吗。

这是我写的查询

SELECT *
FROM dbo.Customer c
 LEFT JOIN dbo.SurveyCustomers sc ON sc.SurveyCustomerId =
(
SELECT A.SurveyCustomerId
FROM
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
          AND sc1.STATUS = 1
          AND sc1.IsActive = 1
    ORDER BY sc1.CreatedDate DESC
) A
)
WHERE c.IsActive = 1
  AND c.STATUS = 1;

客户表

CREATE TABLE [dbo].[Customer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CustomerTitle] [varchar](500) NOT NULL,
[CustomerEmail] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[Status] [bit] NOT NULL)

SurveyCustomer

CREATE TABLE [dbo].[SurveyCustomers](
[SurveyCustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SurveyId] [int] NOT NULL FOREIGN KEY,
[CustomerId] [int] NOT NULL FOREIGN KEY,
[GuidId] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[Status] [bit] NOT NULL,
[IsActive] [bit] NOT NULL)

塞尔坎·阿尔斯兰

您可以OUTER APPLY用来获得相同的结果。

SELECT *
FROM dbo.Customer c
 OUTER APPLY 
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
    ORDER BY sc1.CreatedDate DESC
) A
WHERE c.IsActive = 1
  AND c.STATUS = 1;

和linq等效：

Customers.SelectMany(c => SurveyCustomers
                        .Where(sc1 => 
                            sc1.Status == true 
                            && sc1.IsActive == true
                            && sc1.CustomerId == c.CustomerId
                            && sc1.SurveyId == 1207 )
                        .OrderByDescending(sc1 => sc1.CreatedDate)
                        .Take(1).DefaultIfEmpty(), 
                    (c, sc) => new { c, sc })
.ToList()

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。