how to write this query into linq

Sandeep Kumar

4y1.4kLINQ

select P.Id,p.Name,pp.Weightage from Person p inner join

(Select * from
(
SELECT *
FROM [SL_Project_Management].[dbo].[PersonWiseActionHistory] where Id in ( select MAX(Id) from [SL_Project_Management].[dbo].[PersonWiseActionHistory] group by PersonId) )ss
where convert(nvarchar(10),EffectFrom,103)=convert(nvarchar(10),'24/06/2022',103) and ActionTypeId=1)pp

on p.Id=pp.PersonId

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

PhamNgoc Khiem4y ago
You can refer to it. I think this way can solves your problem
var result = PersonList.Join(
                    PersionHistoryList.GroupBy(g=> new { g.PersonId } ).Select(s=> new { s.Key.PersonId, Id = s.Max(mx => mx.Id) })
                    .Join(PersionHistoryList, (r1) => { return new { r1.PersonId, r1.Id }; }, (r2) => { return new { r2.PersonId, r2.Id };
                    }, (r1, r2)=> new {
                        r1.PersonId,
                        r2.Weightage,
                        r2.ActionTypeId,
                        r2.EffectFrom
                    })
                    .Where(w=> w.ActionTypeId == 1 &&  !string.IsNullOrWhiteSpace(w.EffectFrom)
                    && DateTime.Parse(w.EffectFrom) == DateTime.Parse("06/25/2022")),
                (p) => p.Id,
                (ss) => ss.PersonId,
                (p, ss)=> {
                return new
                {
                    p.Id,
                    p.Name,
                    ss.Weightage
                };
            });
Sandeep Kumar4y ago
kindly give me solution ,pls
Sandeep Kumar4y ago
i have used select in place of select many

bit id not able to find
Sachin Singh4y ago
just use select in place of selectmany and check you get the ids or not.
Sandeep Kumar4y ago
pls help me
Sandeep Kumar4y ago
there is a error showing
Sachin Singh4y ago
You can use somthing like this

var Ids= _context.PersonWiseActionHistory.GroupBy(x=> x.PersonId).selectMany(x=>x.OrderByDescending(x=>x.Id).FirstOrDefault().Id
);
var list1=_context.PersonWiseActionHistory.Where(x=>Ids.Contains(x.Id)).ToList();
var res=(from p in _Context.Person
Join b in list1
on p.Id equals b.PersonId
where Convert.ToDateTime(p.EffectFrom).Date()= Convert.ToDateTime("24/06/2022").Date()
&& ActionTypeId=1
select new { p, b}
Sandeep Kumar4y ago
please help it is so urgent