Dynamic Linq Where IN

Question

How could I define a where in criteria with Dynamic Linq?

I tired workaround below but it must not work, because it doesn't make sense!

context.Records.Where("@0.Contains(ID)", new object[]{
   new string[] {"1", "2", "3"}
}); // throws error No property or field 'ID' exists in type 'String'

Edit 1:

Thanks to everybody, All you need is to use it or outerIt keyword in your Dynamic.Linq query, so in my example i just need to use outerIt:

context.Records.Where("@0.Contains(outerIt.ID)", new object[]{
   new string[] {"1", "2", "3"}
});

You can find more example and information here: Advanced Linq - Dynamic Linq query library: Add support for 'Contains' extension

You don't need that. Use `context.Records.Where(x => list.Contains(x.ID)` — Camilo Terevinto, Apr 01 '18 at 19:30
I don't see how, your question shows an extremely basic use of linq — Camilo Terevinto, Apr 01 '18 at 19:31
A more updated version which also supports .NET Core can be found at https://github.com/StefH/System.Linq.Dynamic.Core — Stef Heyenrath, Apr 02 '18 at 07:54

score 5 · Accepted Answer · answered Apr 02 '18 at 06:58

Other folks here introduced interesting workarounds but they are not useful if we use Dynamic.Linq library. Oleksandr Nahirniak found this question on Stackoverflow. There is also a blog post about advanced Linq queries with Dynamic.Linq which reviewdhere.

Since version 1.0.4 the library also supports Contains extension as well. It could be done just like below:

 query = Contact.GetContactsList()
                .AsQueryable()
                .Where("@0.Contains(outerIt.Country)", new List<String>() { "Austria", "Poland" });

or

query = Contact.GetContactsList()
               .AsQueryable()
               .Where("@0.Contains(outerIt.Country) && it.BirthDate.Year > @1", new List<string>() { "Austria", "Poland" }, 1955);

there are two keywords it and outerIt. It represent to the list that you have passed as a parameter and outerIt represent the collection itself.

So here is my working example:

context.Records.Where("@0.Contains(outerIt.ID)", new object[]{
   new string[] {"1", "2", "3"}
});

Oleksandr Nahirniak · Answer 2 · 2018-04-01T19:37:24.957

2

If you work with EF you can use direct sql commands, like:

context.Database.ExecuteSqlCommand($"SELECT * FROM Records AS r WHERE r.{dynamicPropertyName} IN @ids", new string[] {"1", "2", "3"});

EDIT

Solved:

System.Linq.Dynamic - Can I use IN clause in WHERE statement

edited Apr 01 '18 at 19:37

answered Apr 01 '18 at 19:29

Oleksandr Nahirniak

1,357
2
9
12

Why write SQL when you can use `Contains` for this? – Camilo Terevinto Apr 01 '18 at 19:30
@CamiloTerevinto How I understood topic starter's question, you don't know (in runtime/dynamic) which property do you want apply for IN operation. In other cases linq more preferable totally. – Oleksandr Nahirniak Apr 01 '18 at 19:36
1

You can do so using dynamic LINQ (by using a couple of `Expression`s) without resorting to writing SQL by hand, which entirely defeats the purpose of using EF in the first place – Camilo Terevinto Apr 01 '18 at 19:37
@CamiloTerevinto oh, yep, make sense, you are right, thanks – Oleksandr Nahirniak Apr 01 '18 at 19:40
The link you provided helped me a lot. – Ali Bahrami Apr 02 '18 at 06:49

mshwf · Answer 3 · 2018-04-01T20:37:14.500

You can write your own extension method In, here's the IEnumerable:

public static class ExtLinq
    {
    public static IEnumerable<TSource> In<TSource, TMember>(this IEnumerable<TSource> source,
            Func<TSource, TMember> identifier, params TMember[] values) =>
         source.Where(m => values.Contains(identifier(m)));
    }

use it like this:

context.Records.In(x => x.ID, 1, 2, 3)

if you want the IN to be executed on server to save data travels, use this IQueryable version, implemented with expressions API:

public static IQueryable<TSource> In<TSource, TMember>(this IQueryable<TSource> source,
   Expression<Func<TSource, TMember>> identifier, params TMember[] values)
        {
            var parameter = Expression.Parameter(typeof(TSource), "m");
            var inExpression = GetExpression(parameter, identifier, values);
            var theExpression = Expression.Lambda<Func<TSource, bool>>(inExpression, parameter);
            return source.Where(theExpression);
        }
static Expression GetExpression<TSource, TMember>(ParameterExpression parameter, Expression<Func<TSource, TMember>> identifier, IEnumerable<TMember> values)
        {
            var memberName = (identifier.Body as MemberExpression).Member.Name;
            var member = Expression.Property(parameter, memberName);
            var valuesConstant = Expression.Constant(values.ToList());
            MethodInfo method = typeof(List<TMember>).GetMethod("Contains");
            Expression call = Expression.Call(valuesConstant, method, member);
            return call;
        }

this query (or similar depening on your table) will be executed on server and return desired data:

SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Records] AS [Extent1]
    WHERE [Extent1].[ID] IN (1, 2, 3)

score 0 · Answer 4 · 2018-04-01T19:41:43.037

0

You can easily get same results by creating a simple extension method like so:

public static class Utils
{
    public static bool In<T>(this T src, params T[] items)
    {
        return items.Contains(src);
    }
}

And then call it like this:

context.Records.Where(record => record.In("1", "2", "3"));

edited Apr 01 '18 at 19:41

answered Apr 01 '18 at 19:32

1

That's correct but I'm using `Dynamic.Linq` library, so I cannot use workaround above. – Ali Bahrami Apr 02 '18 at 06:50

Dynamic Linq Where IN

4 Answers4