postgresql 如何在LINQ表达式中获取"distinct on“？

bxjv4tth 于 2022-12-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(171)

有人能解释一下如何将这个SQL查询重写为linq表达式吗？我试图实现的是：有一个作业表，其中的行可能处于活动状态，也可能处于非活动状态（是活动bool列），也有批处理表作为历史运行的作业。如果用户在历史表中创建新的作业将没有记录，我应该得到一个空批处理ID，但不是空的jobId在我的应用程序。也很重要，我必须只从批处理表为每个作业的最大值。e批处理表中只有一个ID为1的作业，但存在ID为1、2、3、4的PK。我只需要一个ID为4的行。

select distinct on (b.type_id)
t.id as "JobId", b.id , t.interval_minutes 
from types t 
left join batches b on t.id = b.type_id 
where t.is_active 
order by t.id , b.id desc

我不知道如何编写distinct on part。github上有一个issue示例，但我不知道如何将其集成到我的代码中。我的代码：

(from types in Types
join batch in Batches on types.Id equals batch.TypeId into joinBatch
from jBatch in joinBatch.DefaultIfEmpty()           
where types.IsActive
orderby types.Id, jBatch.Id descending    
select new DTO() {
    JobId = types.Id,
    ExecTime = types.IntervalMinutes,
    BatchId = jBatch.Id,
})
//.GroupBy(b => b.BatchId).Select(g => g.First()) //getting here an exception if batchid is null
.Dump();

public class DTO{
    public int JobId {get; set;}
    public int ExecTime {get; set;}
    public long? BatchId {get; set;}
}

此代码生成：

SELECT p.id AS "JobId", p.interval_minutes AS "ExecTime", b.id AS "BatchId"
FROM types AS p
LEFT JOIN batches AS b ON p.id = b.type_id
WHERE p.is_active
ORDER BY p.id, b.id DESC
GO

型

postgresql

来源：https://stackoverflow.com/questions/74780468/how-to-get-the-distinct-on-in-linq-expression

2条答案

按热度按时间

vx6bjr1n1#

If I understand your query correctly

那你就能做到。

public class Types
    {
        public int Id { get; set; }
        public bool IsActive { get; set; }
        public int IntervalMinutes { get; set; }
    }
    public class Batches
    {
        public int Id { get; set; }
        public int TypeId { get; set; }

    }
    public class DTO
    {
        public int JobId { get; set; }
        public int ExecTime { get; set; }
        public long? BatchId { get; set; }
    }
        private void GetDistinctList()
        {
            List<Types> TypesLst = new List<Types>()
            {
                new Types() { Id = 1, IntervalMinutes = 10,IsActive=true },
                new Types() { Id = 2, IntervalMinutes = 15 ,IsActive=true}
            };
            List<Batches> BatchesLst = new List<Batches>()
            {
                new Batches() { Id = 1, TypeId=1 },
                new Batches() { Id = 2, TypeId=1 },
                new Batches() { Id = 3, TypeId=1 },
                new Batches() { Id = 4, TypeId=1 }
            };

            var dtoList = (from types in TypesLst
                 join batch in BatchesLst on types.Id equals batch.TypeId into joinBatch
                 from jBatch in joinBatch.DefaultIfEmpty()
                 where types.IsActive
                 select new DTO()
                 {
                     JobId = types.Id,
                     ExecTime = types.IntervalMinutes,
                     BatchId = jBatch == null ? null : jBatch.Id
                 })
                 .OrderBy(d => d.JobId)
                 .ThenByDescending(z => z.BatchId)
                .GroupBy(b => b.JobId).Select(g => g.FirstOrDefault());

赞(0）回复(0）举报 2022-12-18

ldxq2e6h2#

可以使用DistinctBy（）方法，该方法用于根据参数消除重复记录。
例如，如果要删除重复记录而不使用参数：

(from types in Types
join batch in Batches on types.Id equals batch.TypeId into joinBatch
from jBatch in joinBatch.DefaultIfEmpty()           
where types.IsActive
orderby types.Id, jBatch.Id descending    
select new DTO() {
    JobId = types.Id,
    ExecTime = types.IntervalMinutes,
    BatchId = jBatch.Id,
}).Distinct()

如果你想用特定的原语参数删除重复项

(from types in Types
join batch in Batches on types.Id equals batch.TypeId into joinBatch
from jBatch in joinBatch.DefaultIfEmpty()           
where types.IsActive
orderby types.Id, jBatch.Id descending    
select new DTO() {
    JobId = types.Id,
    ExecTime = types.IntervalMinutes,
    BatchId = jBatch.Id,
}).DistinctBy(p => p.JobId)

如果要通过传递复杂参数来删除重复项

(from types in Types
    join batch in Batches on types.Id equals batch.TypeId into joinBatch
    from jBatch in joinBatch.DefaultIfEmpty()           
    where types.IsActive
    orderby types.Id, jBatch.Id descending    
    select new DTO() {
        JobId = types.Id,
        ExecTime = types.IntervalMinutes,
        BatchId = jBatch.Id,
    }).DistinctBy(p => new {p.JobId, p.BatchId})

型
更多信息请访问LINQ's Distinct() on a particular property

赞(0）回复(0）举报 2022-12-18

我来回答

postgresql 如何在LINQ表达式中获取"distinct on“？

2条答案

相关问题

热门标签

最新问答