IN condition in LINQ to replace multiple OR conditions

Question

I have an entity framework query as shown below. Here I am trying to find entities for which there is no fax information for primary and secondary users. I am using two person_role_cd conditions using “OR”.

What is the best LINQ way to make the same logic with something like SQL “IN” condition? For example, p.person_role_cd IN (“Primary”, “Secondary” )

query = query
.Where(l => l.loan_documents
 .FirstOrDefault(d => d.document_type_cd == “Application”)
 .loan_persons.Any(p => 
(
                                p.fax_no != null       
                                &&
                                 (
                                           p.person_role_cd == “Primary”
                                           ||
                                            p.person_role_cd == “Secondary”
                                  )
                                  )
                                  ) == false
                               );

@DStanley, nice find. The answer *could* differ with Linq to Entities though. — BradleyDotNET, Dec 28 '16 at 23:19
@BradleyDotNET `Contains` is fairly universally translated to `IN` in my experience, so long as the list is relatively small. Have you found instances where it doesn't work? — D Stanley, Dec 28 '16 at 23:21
@DStanley Hard to say on that exact method; I just know that once I start closing over local variables bad things tend to happen. — BradleyDotNET, Dec 28 '16 at 23:31

BradleyDotNET · Answer 1 · 2016-12-28T23:31:54.003

Caveat: Doing stuff like this causes problems with Linq to Entities

That said, the first thing that comes to mind is use a List with Contains (which is basically what the provided SQL does)

List<string> roles = new List<string>() { "Primary", "Secondary" };
query = query
.Where(l => l.loan_documents
.FirstOrDefault(d => d.document_type_cd == “Application”)
.loan_persons.Any(p => p.fax_no != null && roles.Contains(p.person_role_cd));

Couldn't quite tell where your negation belongs, left as exercise to the reader. The reason it may not work with Linq to Entities is that translating List.Contains (especially off of a local variable) to SQL is the kind of task it likes to give up on. If your types are literally two items, I would just keep the condition as is (apart from considering an enum for my types instead of strings)

score 1 · Accepted Answer · answered Dec 28 '16 at 23:18

1

First create an array with all strings you want to check against.

var targetList = new[] {"Primary", "Secondary"};

Then Replace your OR condition with

targetList.Contains(p.person_role_cd);

answered Dec 28 '16 at 23:18

Sateesh Pagolu

9,282
2
30
48

IN condition in LINQ to replace multiple OR conditions

2 Answers2