Compare with list using linq

Question

I have a list cids that I am fetching like this

var cids = _service.Employee.Where(i => i.EmpID == _empID).Select(j => j.ClientID).ToList();

I want to compare this list with Patient Entity and get all the records of patients that matches the clientId in the cid list

Patient Entity is like this

class Patient   
{   
    Int PatientID{ get; set;}   
    Int ClientID{get; set;}   
    string PatientName{get; set;}
}

Right now I am doing it like this

foreach(var item in cids)
{
     var pp = from p1 in _service.Patients
                            where p1.ClientId == item
                            select new PatientDTO
                            {
                                PatientID = p1.PatientID,
                                PatientName = p1.PatientName,

                            };
     prec.Add(pp);
}

Is there a way to do it with Linq without using foreach

Raphaël Althaus · Accepted Answer · 2014-02-13 10:30:51Z

3

You can use Contains on your List (you don't need the ToList, by the way : this would avoid 2 queries on db).

var allPp = _service.Patients.Where(p1 => cids.Contains(p1.ClientId))
                             .Select(m >= new PatientDTO) {
                                   PatientID = m.PatientID,
                                PatientName = m.PatientName
                             });

But the most performant way, in a db world, would be a join

from emp in _service.Employee.Where(i => i.EmpID == _empID)
join patient in _service.Patients on emp.ClientId equals patient.ClientId
select new PatientDTO {
  PatientID = patient.PatientID,
  PatientName = patient.PatientName,
}

edited Feb 13, 2014 at 10:30

answered Feb 13, 2014 at 10:19

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

quetzalcoatl Over a year ago

+1. I don't see why this answer got negative vote. This answer directly responds to the request: the foreach loop was rewritten as Where, and the rest was left untouched. Of course Intersect would be (often, not always) better, but hey, this response has 100% accuracy to the actual question.

ElectricRouge Over a year ago

@RaphaëlAlthaus Thanks, your first query works great. I am getting this error with your second query "The type of one of the expression in the join clause is incorrect. type inference failed in the call Join" Can you please help

Raphaël Althaus Over a year ago

@ElectricRouge is ClientId of the same Type in Employee and Patient ? Or is one of them nullable ?

ElectricRouge Over a year ago

@RaphaëlAlthaus its short in Employee and int in Patient

Raphaël Althaus Over a year ago

@ElectricRouge you get this if you do var pp = <thequery>; var result = pp.ToList();

|

Sachin · Accepted Answer · 2014-02-13 11:20:34Z

3

Use Enumberable.Intersect to fetch common records.

var commonClients = cids.Intersect<int>(_service.Patients.Select(x => x.ClientID));

var person = _service.Patients.Where(x => commonClients.Contains(x.ClientID));

edited Feb 13, 2014 at 11:20

answered Feb 13, 2014 at 10:17

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

1 Comment

Andrei V Over a year ago

Providing an example rather than a link would be preferred.

Collectives™ on Stack Overflow

Compare with list using linq

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related