CODEWITHSUNDEEP
c#linq
Beatles1692
Beatles1692

Reputation: 5320

System.Linq.Dynamic : Working with list (IEnumerable) parameters

I am using System.Linq.Dynamic to be able to write dynamic queries but I could not figure it out that how I can pass list (IEnumerable) parameters to a query : Here's what I want to achieve :

SELECT * FROM People WHERE Role IN ('Employee','Manager')

And here's the Linq equivalent of the same query :

from person in People where (new string[]{"Employee","Manager"}).Contains(person.Role)

So I thought to myself that I could write this query using dynamic Linq as :

People.Where("@0.Contains(Role)","(new string[]{\"Employee\",\"Manager\"})")

This version is not working neither:

People.Where("(new string[]{"Employee","Manager"}).Contains(Role)")

So here's the question : How can I apply Dynamic Linq Library to be able to work with list and or IEnumerable parameters such as the above scenario ?

Upvotes: 1

Views: 864

Answers (3)

nathfy
nathfy

Reputation: 717

Old question I know, but took me an age to fix. Referencing it here for others using the latest Dynamic-linq: https://github.com/zzzprojects/System.Linq.Dynamic.Core/issues/314

So:

List<string> ids = new List<string>(){ 1, 2, 3 };
List<string> stringIds = new List<string>(){ "test", "find", "something" };

var queryResults = context.someEntity.AsNoTracking();
queryResults = queryResults.Where("intColName in @0", ids);
queryResults = queryResults.Where("stringColName in @0", strings);

var results = await queryResults.ToListAsync();

This generate sql like:

select cols where intColName in (1, 2, 3) AND stringColName IN (N'test', N'find', N'something')

Upvotes: 1

reckface
reckface

Reputation: 5858

The Dynamic linq project does not support 'contains' natively, I had the same requirement and had to download the source and modify it to support it.

I have lost the ability to keep up with any nuget updates, but the solution now works for our needs. I can't find where I found this, but this is how I did it.

Edit the dynamic.cs file and add the following to around line 566:

interface IEnumerableSignatures
{
    bool Contains(object selector); // Add this
    void Where(bool predicate);
    //...
// Then around line 628 add a new keyword:

static readonly string keywordOuterIt = "outerIt";
static readonly string keywordIt = "It";
//...
// above ParameterExpression It; add
ParameterExpression outerIt;

// In ParseIdentifier add
if (value == (object)keywordOuterIt) return ParseOuterIt();

//Then add that method
Expression ParseOuterIt()
{
    if (outerIt == null)
        throw ParseError(Res.NoItInScope);
    NextToken();
    return outerIt;
}

// In ParseAggreggate, add:
outerIt = it;

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
{   
    // add this section
    if (signature.Name == "Contains")
        args = new Expression[] { instance, args[0] };
    else
    {
        args = new Expression[] { instance, Expression.Lambda(args[0], innerIt) };
    }
}

// In CreateKeyWords()

d.Add(keywordOuterIt, keywordOuterIt); // Add this

I don't know if we can upload source here, but I've been maintaining my own copy of Dynamic.cs, and trying to keep it up to date with the version on nuget. I'll be happy to upload it if you want. I just don't remember where I got all this, because searching for contains on Dynamic linq mostly yields the wrong results - pointing to string contains, not IEnumerable.contains.

Upvotes: 2

user2160375
user2160375

Reputation:

Dynamic LINQ doesn't support Contains by default.

You can do it in old way:

var roles = new[] { "Employee", "Manager" };
var predicate = new StringBuilder();
for (var i = 0; i < roles.Length; i++)
{
    string role = roles[i];
    predicate.AppendFormat("Role = @{0}", i);
    if (i < roles.Length) predicate.Append(" OR ");
}

People.Where(predicate.ToString(), role.Cast<object>().ToArray());

Here is question about that: link. In mentioned question there is also other replacements.

Upvotes: 1

Related Questions