时间戳

首页 > 解决方案 > 在 LINQ 的右表中左连接最多一行

c# - 在 LINQ 的右表中左连接最多一行

问题描述

Hifriends，我有一张客户表和一张关系表，用于保存我发送调查链接的客户。同一调查可能已多次发送给同一客户。或者，一些客户可能没有发送任何调查链接。我的目标是带所有客户一次，如果其中一些客户已收到调查问卷，我只想带创建日期最大的那个。记录数必须等于客户数。我写了查询，但是很难翻译 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)

调查客户

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)

标签： c#sqlsql-serverlinqsql-to-linq-conversion

解决方案

对于这种情况，您需要 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 条款：

https://docs.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
                });
        }
    }

问候！

推荐阅读