Advanced Linq - Dynamic Linq query library: Add support for 'Contains' extension

This post explains how to extend the Dynamic Linq library to support the "Contains" extension method. This post also can serve as a base for further extension of the Dynamic Linq Library and adding support to more extension methods.

Linq is an amazing programming model, brought to life by the Microsoft.NET Framework 3.5, that introduces data queries as a first-class concept into any Microsoft programming langage.

Linq allows you to build strongly type queries to access data in any source for which a Linq support library is available, including MS SQL, data objects (through IEnumerable and IQueryable), Entity Framework and more.

Even though the regular, strongly typed approach to building queries is definitively recommended, in some cases arises the need for dynamically building queries. For this purpose Microsoft freely provides an open source dynamic Linq query library. Cases for using dynamic queries could be for example: you might want to provide business intelligence UI within your application that allows an end-user business analyst to use drop-downs to build and express their own custom queries/views on top of data. Another example is the creation of RESTfull service interfaces that accept query string parameters that could be converted to dynamic linq queries.

Please refer the the Scott Guthrie post for more information on the Dynamic Linq Query library:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

I found this library very usefull and used it in several scenarios. However this library does not support all of the Linq extensions. I had the need for building dynamic queries with the "Contains" extension which was not supported by the Microsoft Dynamic Linq library. This post explains how to extend the Dynamic Linq library to support the "Contains" extension method. 

Sample Code

Here is the sample application we use to illustrate our tallks (You can download the source code here) : 

We define a class "Contact". For the purpose of demonstration, we do not connect to a data source, but have a static "GetContactList" method that return a collection of users. 

public class Contact
{
 public string FirstName { get; set; }
 public string LastName { get; set; }
 public DateTime BirthDate { get; set; }
 public string Country { get; set; }

 public Contact(string firstName, string lastName, DateTime birthDate, string country)
 {
 FirstName = firstName;
 LastName = lastName;
 BirthDate = birthDate;
 Country = country;
 }

 public override string ToString()
 {
  return string.Concat(FirstName, " ", LastName, " ", BirthDate, " ",Country);
 }
 
 public static List‹contact› GetContactsList()
 {
  var result = new List‹contact›();
 
  result.Add(new Contact("Zephr", "Austin", new DateTime(1967, 11, 07), "Afghan"));
  result.Add(new Contact("Odette", "Bean", new DateTime(1993, 05, 18), "Uzbekistan"));
  result.Add(new Contact("Maggie", "Mcson", new DateTime(2001, 06, 12),"Kiribati"));
  ...
  ...
 
  return result;
 }
}

 

The Case – Use of the Dynamic Linq Library and missing support for the “Contains”  extension method

 

Let's take a first example of simple Linq query to get the contacts located in Austria. Using regular Linq, you write the following query:

var query = from c in Contact.GetContactsList() where c.Country == "Austria" select c;

Or the equivalent query, using extension methods and lambda 

var query = Contact.GetContactsList().Where(c => c.Country == "Austria");

We will keep the second syntax as the dynamic linq library does not support the first syntax.

We can then iterate the query result and retrieve relevant contact information:

foreach (var contact in query)
{
 Console.WriteLine(contact.ToString());
} 

Doing the same query using dynamic Linq: 

query = Contact.GetContactsList().AsQueryable().Where("Country == @0", "Austria");

Please not the extra "AsQueryable()" call because the dynamic extension applies on collections implementing IQueryable. Then Iterating the results is just the same as with regular Linq.

The Dynamic Linq library also supports more complex queries. For instance here is the Dynamic Linq query to retrieve all contacts in Austria, born in 1957: 

query = Contact.GetContactsList().AsQueryable().Where("Country == @0 && BirthDate.Year == 1957", "Austria");

 

Everythings seems to magically work so far. Let’s try now a more complex scenario: when want to retrieve the list of contacts located in given list of countries. Let’s say : all contacts located in either Austria or Poland. For such a purpose, a common solution is to use the “Contains” extension method:

query = Contact.GetContactsList().Where(c => new List<string>>() { "Austria", "Poland" }.Contains(c.Country));

 

In Linq to SQL, the result pseudo SQL projection would be:

select * from contact c where c.Country in (‘Austria’, ‘Poland’);

 

Let’s try to write the same query in Dynamic Linq (using parameters to pass the input list of countries):

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

 

This result at runtime to the following exception: “No applicable aggregate method 'Contains' exists”,

Emphasizing the fact the Dynamic Linq library does not have support for the “Contains” extension method. The following section will explains how to add support for “Contains”.

 

Add support for the “Contains” extension method

Analyzing the “Dynamic.cs” class that adds support for Dynamic Linq, you’ll find the definition of the  IEnumerableSignatures interface. This interface lists all IEnumerable extension methods that are supported by the Dynamic Linq library. You should add the “Contains” extension method signature:

interface IEnumerableSignatures
{
 void Contains(object selector);
 void Where(bool predicate);
 void Any();
 void Any(bool predicate);
 void All(bool predicate);
 void Count();
 void Count(bool predicate);
 void Min(object selector);
 void Max(object selector);
 void Sum(int selector);
 void Sum(int? selector);
 void Sum(long selector);
 void Sum(long? selector);
 void Sum(float selector);
 void Sum(float? selector);
 void Sum(double selector);
 void Sum(double? selector);
 void Sum(decimal selector);
 void Sum(decimal? selector);
 void Average(int selector);
 void Average(int? selector);
 void Average(long selector);
 void Average(long? selector);
 void Average(float selector);
 void Average(float? selector);
 void Average(double selector);
 void Average(double? selector);
 void Average(decimal selector);
 void Average(decimal? selector);
}

 

The IEnumerableSignatures interface is then used in the following method:

 

Expression ParseAggregate(Expression instance, Type elementType, string methodName, int errorPos) 
{
 ParameterExpression outerIt = it;
 ParameterExpression innerIt = Expression.Parameter(elementType, "");
 it = innerIt;
 Expression[] args = ParseArgumentList();
 it = outerIt;
 MethodBase signature;
 if (FindMethod(typeof(IEnumerableSignatures), methodName, false, args, out signature) != 1)
 throw ParseError(errorPos, Res.NoApplicableAggregate, methodName);
 Type[] typeArgs;
 if (signature.Name == "Min" || signature.Name == "Max") {
 typeArgs = new Type[] { elementType, args[0].Type };
 }
 else {
 typeArgs = new Type[] { elementType };
 }
 if (args.Length == 0) {
 args = new Expression[] { instance };
 }
 else {
 args = new Expression[] { instance, Expression.Lambda(args[0], innerIt) };
 }

 return Expression.Call(typeof(Enumerable), signature.Name, typeArgs, args);
}

 

There 2 changes to perform to that method.

Fist, the arguments are constructed using the following line:

args = new Expression[] { instance, Expression.Lambda(args[0], innerIt) };

 

this has to be changed specifically for the Contains methods to the following:

args = new Expression[] { instance, args[0] };

Reason is that the signature of the Contains extension methods differs from the others. Let’s take an example: here are the definitions for the “Any” and “Contains” extension methods of the IEnumerable<> interface (classe “System.Linq.Enumerable”):

 

Any<TSource>(this.System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>);
Contains<TSource>(this.System.Collections.Generic.IEnumerable<TSource>, TSource);

 

You can see that the parameter of the “Any” extension method is a lambda expression taking “TSource” (type of innerIt in our Dynamic Linq library) as entry and returning ”bool” .This explains the transformation Expression.Lambda(args[0], innerIt). In the case of the “Contains” extension method, type of the argument is not a Lambda but just “TSource”.

Next change is the following: the Dynamic Linq library transforms the input string to a proper Linq query by parsing the input string. It internally keeps a context to the current collection being processed. This is the use of the field “innerIt”. When processing our list of “Contacts”, the innerIt represent a “Contact”. However, when processing the “Contains” method, the context changes from the list of “Contacts” to the list being the source for the “Contains” method; in our case: the list of string representing country name. Therefore the parsing of the following dynamic Linq expression:

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

will not work: the parser will try to find a property “Country” on type String, where we meant a property “Country” on “Contact”.

In fact the previous query is equivalent to:

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

Where “it” is the keyword defined in the Dynamic Linq library to represent the current element. It represents here the current item in the list {“Austria”, “Poland”}. What we would like here is the following syntax:

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

where “outerIt” represents the englobing context: the list of “Contacts”.  For this we should define an “outerIt” keyword, and use it in the “ParseAggregate” method. The new implementation of the “ParseAggregate” methods is now:

Expression ParseAggregate(Expression instance, Type elementType, string methodName, int errorPos) 
{
 outerIt = it;
 ParameterExpression innerIt = Expression.Parameter(elementType, "");
 it = innerIt;
 Expression[] args = ParseArgumentList();
 it = outerIt;
 MethodBase signature;
 if (FindMethod(typeof(IEnumerableSignatures), methodName, false, args, out signature) != 1)
  throw ParseError(errorPos, Res.NoApplicableAggregate, methodName);
 Type[] typeArgs;
 if (signature.Name == "Min" || signature.Name == "Max") {
  typeArgs = new Type[] { elementType, args[0].Type };
 }
 else
 {
  typeArgs = new Type[] { elementType };
 }
 if (args.Length == 0) {
  args = new Expression[] { instance };
 }
 else {
  if (signature.Name == "Contains")
  args = new Expression[] { instance, args[0] };
  else
  args = new Expression[] { instance, Expression.Lambda(args[0], innerIt) };
 }
 return Expression.Call(typeof(Enumerable), signature.Name, typeArgs, args);
}

 

By the way: it is important to mention that since “it” and “outerIt” are defined keywords, they become reserved keywords and should not be used as properties or method definitions in your data classes, if you intend to access them as part of dynamic queries. It would result in a runtime error. Since the common used code standards are to use only capitalized names for public properties and methods, it should be alright in most of the cases.

Remains the use of the “outerIt” keyword. Definition of the keyword and class level storage of its current value:

static readonly string keywordOuterIt = "outerIt";
ParameterExpression outerIt;

 

Taking into account the new keyword in « ParseIdentifier »

 

Expression ParseIdentifier() 
{
 ValidateToken(TokenId.Identifier);
 object value;
 if (keywords.TryGetValue(token.text, out value)) {
 if (value is Type) return ParseTypeAccess((Type)value);
 if (value == (object)keywordIt) return ParseIt();
 if (value == (object)keywordOuterIt) return ParseOuterIt();
 if (value == (object)keywordIif) return ParseIif();
 if (value == (object)keywordNew) return ParseNew();
 NextToken();
 return (Expression)value;
 }
 ...
 ...

 

And new method “ParseOuterIt”:

 

Expression ParseOuterIt()
{
 if (outerIt == null)
 throw ParseError(Res.NoItInScope);
 NextToken();
 return outerIt;
}

 

 

Conclusion

 

That’s it ! The Dynamic Linq library is now supporting the « Contains » extension method

You’ll find the modified “Dynamic.cs” class as part of the code sample. You can now write and successfully run queries likes the following:

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);

This post can be used to further extend the Dynamic Linq library and add support for more extension methods.

Hope this can help other who faced the same limitation and help move forward the Dynamic Linq Library!

You can download the source code for the sample project here. 

 

Comments

[www.codinginlondon.com]

I find Linq very useful too... I started using it to do some lightweight data crunching on a C# client. I'm also planning to migrate our layer of stored procedures wrappers from ODBC (C++) to Linq (C#). So far Linq is the easiest way I know to wrap stored procedures calls...

http://www.codinginlondon.com/2010/03/comparing-result-sets-with-linq.html

[Walter Almeida]

Hello Matthieu,
Yes Linq is great, I especially love Linq to objects! But I am not too sure about Linq to SQL. I would suggest you to have a look at Linq to Entities. Linq to SQL has limits and is not a proper ORM as such. Linq to Entities is a proper ORM. Or more precisely: the Entity Framework is an ORM and Linq to Entities is the way to query the Entity Framework (the same way you have a Linq to LLBLGenPro: the ORM I am using). Linq to SQL IMHO is not the most robust, scalable and evolutive solution you could possibly use.

[Thomas Levesque]

Hi Walter,

Great article, thanks ! This is exactly what I was looking for.

Just one remark : you also need to update the CreateKeywords method, to add "outerIt" to the list of known keywords

[Walter Almeida]

Hi Thomas!

Thanks for your comment, and I am happy to hear that this work helped you.

Oh yes you're right, CreateKeywords has to be updated. It is done in the source code attached to the post. However I forgot to mention it in the post itself. So thanks for that!

And if you're doing some work on dynamic linq, I am happy to hear about it if you want to share, as there are not too many people investigating this.

[Sean Brauen]

Mad props for this figuring this out, it really saved me. I've referenced your post as the answer in an ASP.NET forum post I had regarding this very issue.

Thanks again!!

[Walter Almeida]

Thanks for your comment Sean, and for referencing my post. I am glad it helped you!

[Sean Brauen]

Walter,

I had a couple of problems on my end I needed to work through, but now I'm getting this error:

LINQ to Entities does not recognize the method 'Boolean Contains[Guid](System.Collections.Generic.IEnumerable`1[System.Guid], System.Guid)' method, and this method cannot be translated into a store expression.

I'm not sure if I did something wrong or not and was wondering if you could help out. Below is the Where clause I'm using where @0 = List and @1 = Guid, both values do exist.

IS_DELETED==false && @0.Contains(outerIt.URI) && (DESIGNER_COUNTRY_ID.URI.Equals(@1) || SPECIFIC_USER_VARIANT_ID.URI.Equals(@1))

Thanks!

[Walter Almeida]

Hello Sean,

I am not using Linq to entity framework but Linq to Llblgen and it is working fine...

After a quick search on internet it seems that Linq to EF does not support Contains extension!
So another solution has to be found. I am on holiday right now, I will check more on your issue when I am back next week.

[Walter Almeida]

Hello Sean,

After checking, EF v4 Supports the "Contains" extension method (thanks Frans Bouma for helping me with this!).

So the solution to your problem should be to upgrade to EF v4, you apparently are using the previous version, aren't you?

[Vlad]

Hello Walter, wery intresting post!

And is there any way to make dynamic library support Select method?

For example to use in queries like
var r1 = Query.All().Where(a => a.Roles.Select(b => b.Role.Name).Contains("admin"));
as
var r2 = Query.All().Where("Roles.Select(Role.Name).Contains('admin')");

model (DataObjects.NET) is:

[Serializable]
[HierarchyRoot]
public class User : Entity
{
[Field, Key]
public int Id { get; private set; }

[Field(Length = 100)]
public string Name { get; set; }

[Association(PairTo = "User")]
[Field]
public EntitySet Roles { get; private set; }
}

[Serializable]
[HierarchyRoot]
public class UserInRoles : Entity
{
[Field,Key]
public int Id { get; private set; }

[Field]
public User User { get; set; }

[Field]
public Role Role { get; set; }
}

[Serializable]
[HierarchyRoot]
public class Role : Entity
{
[Field, Key]
public int Id { get; private set; }

[Field(Length = 100)]
public string Name { get; set; }

[Association(PairTo = "Role")]
[Field]
public EntitySet Users { get; private set; }
}

[Walter Almeida]

Hello Vlad,
Thanks for your comment;)
Well, that should be possible, however becoming tricky. You could potentially find other ways to do what you are trying to achieve.

For example what about overriding Equals() in the Role Entity, and returning true when role name is same.

and then write your query like this:
var r2 = Query.All().Where("Roles.Contains(@0)", new Role() { Name = "admin" });

Will work the same in an neater way:)
I haven't tested yet though, you should try. But you get the idea

[yazid]

Hello,

Excellent article, how about if I had a dynamic object. How would I use it. Would it work?

Thx
Yaz

[Axel]

Hi,
Nice article ! Really helpful !
I'm now trying to do the same to handle the "Take" method, but I can't get it working. Maybe you can help ?
Thanks.
Axel

[Andrei]

Good stuff. However, I'm having a problem where it fails if the search item has spaces. I see your example works fine for "Dominican Republic". However, in my code, any time I have a value with a space, it doesn't find it. The only difference is I'm using VB.NET.

[Andrei]

Nevermind. Strangely what I thought was the most common value in my data source doesn't even exist in there! It works great! Thx!

[Stefan]

Thx for this, was looking for it for quite some time!

I have, however, issues when I try this on a Datetime field. Say I want to search for a couple of dates:
I've tried using List as parameter, but not working.
I've tried using List with all values in YYYY-MM-DD format as parameter, but not working, because I cannot seem to convert the column of the queryable object to string.

Any thoughts?

[Stefan]

I see that part of my comment is lost. What I meant was:

I've tried using List of Datetime as parameter, but not working.
I've tried using List of string with all values in YYYY-MM-DD format as parameter, but not working, because I cannot seem to convert the column of the queryable object to string.

[A Facebook User]

Hi Walter
Thanks for the code lesson, i learnt alot!
I do have one question before i implement this if you don't mind.

If i followed your code example to add a StartsWith extension, would it be able to pass extra arguments for case insensitive query. (I tried, but got some parsing errors and then got lost in the code)

here's an example of what i'm trying to achieve

query = Contact.GetContactsList().AsQueryable().Where("@0.StartsWith(outerIt.Country, StringComparison.InvariantCultureIgnoreCase)", new List() { "austria", "poland" });

any help greatly appreciated.
thanks Peter

[priya]

i tried this but i get the following error still.

No property or field 'outerIt' exists in type 'String'

[Alex Harbers]

Priya,

You need to add code to CreateKeywords()

d.Add(keywordOuterIt, keywordOuterIt);

[Nicholas Mayne]

I know this is old, but I am getting the exception

No applicable method 'Contains' exists in type 'Int16'

Any ideas on how to get around that?

[Timothy Chi]

Excellent!!!

[Alan Macdonald]

I can't get this to work. ParseAggregate is throwing the following exception on the last line to Expression.Call:

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.

Has anyone got this working with Entity Framework 6?

My calling code is

public IQueryable Search(IQueryable items)
{
return items.Where("@0.Contains(outerIt." + FieldName + ")", SelectedIds);

}

[Alan Macdonald]

My problem was being caused by the fact my column was Int64? but the array of IDs was Int64s. Changing the array to Int64? so the types matched for the nullability solved it.

Seems to work quite well. Cheers.