LINQ for CRM how to use C# list in where clause

Question

I need to use a C# list in LINQ where clause. I can do query with one value in where clause, please guide how to use a C# list in LINQ query.

Below is my code:

var usersList= new List<string>();

usersList.Add("User1");
usersList.Add("User2");

(from u in UserSet
where u.FullName.Equals("any user from usersList")
select u.FullName).Take(3).Dump();

Basically how to tell LINQ to check every value in usersList

Edit: I m doing this LINQ for CRM

Thanks

What value do you want to check against? also what is `UserSet`? — AD8, Jun 28 '18 at 06:44
Possible duplicate of [Linq select objects in list where exists IN (A,B,C)](https://stackoverflow.com/questions/14257360/linq-select-objects-in-list-where-exists-in-a-b-c) — Caramiriel, Jun 28 '18 at 06:45
Possible duplicate of [Dynamics CRM SDK - IN operator for linq with OrganizationServiceContext](https://stackoverflow.com/questions/22326423/dynamics-crm-sdk-in-operator-for-linq-with-organizationservicecontext) — Henrik H, Jun 28 '18 at 13:55

TheGeneral · Answer 1 · 2018-06-28T07:04:02.400

6

You can use Contains

var usersList= new List<string>();
usersList.Add("User1");
usersList.Add("User2");

...

(from u in UserSet
where usersList.Contains(u.FullName)
select u.FullName).Take(3)

Enumerable.Contains Method (IEnumerable, TSource)

Determines whether a sequence contains a specified element by using the default equality comparer.

Update

I cant see a way to use Contains with a in memory list. However if the user list is not too big you could do it after the fact in memory

var results  = (from u in UserSet
               select u.FullName);

var results (from r in results
             where usersList.Contains(r)
             select r).Take(3);

There has to be a better way though.

edited Jun 28 '18 at 07:04

answered Jun 28 '18 at 06:45

TheGeneral

69,477
8
65
107

TheGeneral, apologies I did not mentioned using it in LINQ for CRM. Tried and got error NotSupportedException: Invalid 'where' condition. An entity member is invoking an invalid property or method. – user576510 Jun 28 '18 at 06:51
@user576510 updated, this may not be the best approach however it should work, for small lists, im stuck trying to find a better solution though – TheGeneral Jun 28 '18 at 07:04

score 2 · Answer 2 · answered Jul 04 '18 at 12:18

You cannot use Contains method with LINQ for Dynamics CRM. The General is correct, you can bring the entire entity set into memory and then perform your contains method. However, this is inefficient - especially when the entity sets increase after the system has gone live.

I have made the assumption that you are using early bound context, that is why you want to use LINQ, if this is correct then an option would be to do something like the following:

var usersList= new [] {
 "User1",
 "User2"
};

var systemUserQuery = new QueryExpression{
    EntityName = SystemUser.EntityLogicalName,
    ColumnSet = new ColumnSet("fullname","domainname" ....), //explicitly retrieve attributes
    Criteria = {
        Conditions = {
            new ConditionExpression("fullname", ConditionOperator.In, userList)
        }
    }
};

var usersResponse = organizationService.RetrieveMultiple(systemUserQuery); 
var systemUsers = usersResponse.Entities.Select(s => (SystemUser)s).ToArray();

This does not use LINQ in the expression to retrieve the data, however the query is executed remotely, and you do not need to retrieve the entire entity set. In the statement var systemUsers = usersResponse.Entities.Select(s => (SystemUser)s).ToArray(); The object type Microsoft.Xrm.Sdk.Entity will be cast as the Early Bound Type SystemUser and you can continue to use the objects as you any other Early Bound object.

When using the ConditionOperator.In operator, the value supplied will need to be an array. You can declare the userList variable as List<string> as in your example. However, you will need to cast it before adding the object into the query.

Just a note the line ColumnSet = new ColumnSet("fullname","domainname" ....) is where you select the attributes to retrieve in the request. If you do not specify the attribute here the property will be the default value after the cast.

LINQ for CRM how to use C# list in where clause

2 Answers2