Question 90567 in PostgreSQL Question posted in
The official documentation can be found here.

How to get the 'distinct on' in LINQ expression? – Postgresql

Could somebody explain how rewrite this SQL query to linq expression. What I’m trying to achieve: there is a jobs table which may have rows be active or not (is active bool column), also there is batch table which works as history for jobs runned. If user create new job in history table will be no records for it and I should get a null batch id but not null jobId in my app. Also it crucial i must get only max value from batch table for each job. i.e there is only one job with Id 1 in batch table there are PK with id 1 2 3 4. I need only one row with 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

I can’t figure out how to write distinct on part. There is a issue on github with example but I don’t understand how to integrate it to my code.
My code:

(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;}
}

This code generates:

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
Tags:

2

Answers


  1. 0 
    If I understand your query correctly

    enter image description here

    then
    You can do this.

       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());

    enter image description here

    Login or Signup to reply.
  2. 0

    you can use DistinctBy() method where it works to eliminate duplicate records based on parameter.

    for example if you want to remove duplicate records without params:

    (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()

    if you want to remove duplicates with specific primitive param

    (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)

    if you want remove duplicates by passing complex params

    (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})

    for more go through LINQ's Distinct() on a particular property

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search