4

Background: I would like to use System.Linq.Dynamic library to post In/Contains query to MS SQL

Please note I am trying to use System.Linq.Dynamic in generic function so that I could apply customer filter to any class having CustomerId(integer) property. Also CustomerId property could be nullable

All SO posts redirects me to this solution. After implementing the same I keep getting this exception: "No generic method 'Contains' on type 'System.Linq.Enumerable' is compatible with the supplied type arguments and arguments. No type arguments should be provided if the method is non-generic."

And now this is how my code looks like:

public static IQueryable<T> ApplyCustomerFilter<T>(this IQueryable<T> collection, int[] customerIds)
{
    return collection.Where("@0.Contains(outerIt.CustomerId)", customerIds);
}


public class MyUser : IdentityUser
{    
    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }
    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }
    public int? CustomerId { get; set; }
    [ForeignKey(nameof(CustomerId))]
    public virtual Customer Customer { get; set; }
}

Could you please guide me where I am going wrong?

shobhit vaish
  • 951
  • 8
  • 22

1 Answers1

5

Because your MyUser.CustomerId property is nullable - you should pass nullable int array as customerIds in this case. For example:

public static IQueryable<T> ApplyCustomerFilter<T>(
    this IQueryable<T> collection, 
    int?[] customerIds) { // < - note here
        return collection.Where("@0.Contains(outerIt.CustomerId)", customerIds);
}

or convert passed array to array of nullable ints:

public static IQueryable<T> ApplyCustomerFilter<T>(
    this IQueryable<T> collection, 
    int[] customerIds) {
        return collection.Where("@0.Contains(outerIt.CustomerId)",
             customerIds.Cast<int?>()); // <- note here
}

Alternatives proposed by Ivan Stoev in comments (with them customerIds array can be regular int[] array, no need for it to be array of nullables):

"@0.Contains(outerIt.CustomerId.Value)"

And this one will work in both cases (whether CustomerId is nullable or not):

"@0.Contains(Int32(outerIt.CustomerId))"
Evk
  • 98,527
  • 8
  • 141
  • 191
  • Or convert property to `int` - `"@0.Contains(outerIt.CustomerId.Value)"` or `"@0.Contains(int(outerIt.CustomerId))"` – Ivan Stoev Nov 27 '17 at 09:27
  • @IvanStoev last proposal ("@0.Contains(int(outerIt.CustomerId))") seems to be not working. – Evk Nov 27 '17 at 09:30
  • Thank you! That worked like a charm. Exception I was getting was not helpful. – shobhit vaish Nov 27 '17 at 09:31
  • @IvanStoev, works with `Int32(outerIt.CustomerId)`, thanks. – Evk Nov 27 '17 at 09:33
  • @shobhitvaish take a look for alternatives provided by Ivan, I think they are better (especially last one). – Evk Nov 27 '17 at 09:36
  • @Evk Lol :) There are different DynamicLINQ implementations (dialects), it's working in mine (`System.Linq.Dynamic.Core`). Trial and error you know :) And `outerIt` is not supported, but `Contains(int(CustomerId))` works. Anyway, I think now OP has enough options, they can choose whatever works with the package they are using. – Ivan Stoev Nov 27 '17 at 09:39
  • Saved me lot of time. Thanks. – Sachin Parashar Nov 14 '19 at 05:46