Dynamic Linq Where IN

c# dynamic-linq linq

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

1
2
4/2/2018 7:00:07 AM

Accepted Answer

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"}
});
5
4/2/2018 6:58:14 AM

Popular Answer

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)


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow