Reputation: 9738

Linq IN Clause in Where

I want to know how to use IN clause in Linq. Here is my Code :-

int empCount = ctx.tblEmpTran
                .Count(
                    e => e.Id == Id &&
                    e.Month == selectedMonth &&
                    e.Year == selectedYear &&
                    e.employeeId.contains()
                );

The Following query is supposed to be in IN

SELECT A.Id FROM dbo.EmployeeDetail A WHERE A.CompanyId = 1 AND A.COLS > 0

In the above code, contains method do not popup in intellisense.

Upvotes: 0

Answers (4)

hazimdikenli

Reputation: 6019

considering that you have a tblEmployeeDetail in the same DbSet and them having a relation through employeeId you can write you query like.

var q = from e in ctx.tblEmployeeDetail where e.Transactions.Any(t => t.Month == selectedMonth &&
                    t.Year == selectedYear);
int empCount = q.Count();

this is pseudo-code but this is how you would use the LINQ effectively, (Exists is better than In check)

Upvotes: 0

Matteo Mosca

Reputation: 7448

This is because you are trying to convert from SQL to Linq, and you couldn't try a worse approach.

You should try to write your LINQ query starting from what you need it to do, forgetting SQL altogether.

In Linq there is no "IN" operator, to achieve the same thing you take your collection and check if it Contains the value.

So in your scenario you should simply generate your collection of valid values and then in your query do:

myCollection.Contains(e.employeeId)

It is "collection CONTAINS value" the logic, not "value is found IN collection". Again if you insist to start from SQL when using Linq you will always incur in this kind of problems.

Check Albahari tutorial on how to approach Linq correctly and your productivity will skyrocket.

Upvotes: 2