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

Question

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

这是查询我写的

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)

调查客户

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)

Answer 1

您可以使用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()

Answer 2

你可以这样做：

var finalSurveyCustomers = from customer in Customers
join sc in SurveyCustomers on customer.CustomerId equals sc.SurveyCustomerId into leftCustomers
from leftCustomer in leftCustomers.DefaultIfEmpty()
select new { LeftId = customer.Id, SurveyId = leftCustomer ?? 0 }; // can use int.Max or int.Min

Answer 3

对于这种情况，您需要 GROUP BY 子句：

SELECT c.SurveyCustomerId, MAX( /* THE MAX YOU WANT TO GET */)
FROM dbo.Customer c
LEFT JOIN dbo.SurveyCustomers sc ON sc.SurveyCustomerId = A.SurveyCustomerId
GROUP BY c.SurveyCustomerId
WHERE c.IsActive = 1 AND c.STATUS = 1 /*AND OTHER CONDITIONS */;

进一步阅读 GROUP BY clasue：

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

更新：很抱歉错过了 LINQ 部分：您应该可以执行以下操作：

public class SurveyCustomer
{
public int SurveyCustomerId { get; set; }
public virtual ICollection<Survey> Surveys { get; set; }
}
public class Survey
{
public int SurveyId { get; set; }
}
public class SurveyCustomerReadModel
{
public int SurveyCustomerId { get; set; }
public int LastSurveyId { get; set; }
}
public class SurveyCustomerRepository
{
public List<SurveyCustomer> SurveyCustomers { get; set; }
public IEnumerable<SurveyCustomerReadModel> GetSurveyCustomerReadModel()
{
return this.SurveyCustomers
.Select(sc => new SurveyCustomerReadModel
{
SurveyCustomerId = sc.SurveyCustomerId,
LastSurveyId = sc.Surveys.Max(/* SOME CRITERIA FOR DEFINING THE MAX*/).SurveyId
});
}
}

问候！