by Andreas

LINQ – How to build a dynamic WHERE clause (C#)

LINQ is brilliant, and if you know how (and not to mention where, when and on what) to use it it will really improve the readability of your code and save you a hell of a lot of time. One thing that is missing though, is a direct implementation of the “in” statement, known from SQL. If you want to run a query which relies on runtime values, you would want to build the where clause dynamically. The equivalent of this SQL statement:

SELECT * FROM USERS WHERE USERID IN (12, 14, 32, 86)

in LINQ should have been like this (the userIds array is simulating dynamic runtime values):

var userIds = new List<long>() { 12, 14, 32, 86 };
var result = from u in dbContext.Users
                    where u.UserId in userIds
                    select u;

But it’s not. So while we wait for the LINQ team to implement it, here’s a solution to get around this challenge. Use the Contains method of the dynamic array this way:

var userIds = new List<long>() { 12, 14, 32, 86 };
var result = from u in dbContext.Users
                    where userIds.Contains(u.UserId)
                    select u;

This will not only compile, but it will also give you the same result as the SQL statement above.

Tags: ,
  • Steven

    Hey, just thought I’d say this was exactly what I was looking for!

    Why the regular “in” statement isn’t already implemented beats me.. Thanks!