How to get the 'distinct on' in LINQ expression?-CodePudding

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

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

CodePudding user response：

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