CODEWITHSUNDEEP
c#linq-to-entities
user8295448
user8295448

Reputation:

how to select record based on max value and where clause in LINQ to Entities

i have following records in my table tbl_JobMaster

ID  CIN   JobNumber Version
1   ABC   100           1.0     
2   ABC   100           2.0 
3   ABC   200           1.0     
4   ABC   200           2.0
5   ABC   200           3.0
6   XYZ   300           1.0

i want list of records based on CIN column and MAX(Version) and unique JobNumber

for e.g. CIN=ABC so output should be as follows

ID  CIN   JobNumber Version
2   ABC   100           2.0 
5   ABC   200           3.0

I tried following code but it isn't working

var result=(from job in entity.tbl_JobMaster
            where job.CIN=="ABC" && job.Version==entity.tbl_JobMaster.Where(p=>p.ID==job.ID).Max(p=>p.Version)
            select job).Distinct();

Upvotes: 0

Views: 703

Answers (3)

Lightbringer
Lightbringer

Reputation: 819

Here's an example using LINQ extension methods:

var selection = entity.tbl_JobMaster
  .Where(job => job.CIN == "ABC")
  .GroupBy(job => job.JobNumber)
  .Select(group => group
    .OrderByDescending(job => job.Version)
    .First()
);

Upvotes: 1

Evan Huang
Evan Huang

Reputation: 1275

First group by the CIN and JobNumber

var temp = from job in entity.tbl_JobMaster
           where job.CIN == "ABC"
           group job by new { job.CIN, job.JobNumber } into g
           select g;

Then order the items in groups by Version descending, and select the first one

var result = temp.Select(o => o.OrderByDescending(t => t.Version).First());

Upvotes: 0

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

Your query should be like below

var result = (from job in entity.tbl_JobMaster
    where job.CIN == "ABC" && job.Version == entity.tbl_JobMaster.Where(p => p.CIN == job.CIN && p.JobNumber == job.JobNumber).Max(p => p.Version)
    select job).Distinct();

Upvotes: 0

Related Questions