Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 425
user576510 Avatar asked Jan 28 '23 20:01

user576510


2 Answers

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.

like image 171
TheGeneral Avatar answered Feb 05 '23 01:02

TheGeneral


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.

like image 27
Stefan William-Worrall Avatar answered Feb 05 '23 03:02

Stefan William-Worrall