Dynamics CRM SDK - IN operator for linq with Organ

Question

I'm using my OrganizationServiceContext implementation generated by the svcutil to retrieve entities from CRM:

context.new_productSet.First(p => p.new_name == "Product 1");

Is it possible to retrieve multiple entities with different attribute values at once - (smth like IN operator in SQL)?

Example: I would like to retrieve multiple products ("Product 1", "Product 2", ...) with a single call. The list of product names is dynamic, stored in an array called productNames.

Answer 1

I do not know how to do this with Linq, as far as I know it is not possible.

It can be done with Query Expressions:

String[] productNames = new[] { "test1", "test2" };
QueryExpression products = new QueryExpression(Product.EntityLogicalName);
products.ColumnSet = new ColumnSet("name", "new_att1", "new_att2"); // fields to get 
products.Criteria.AddCondition("name", ConditionOperator.In,
    productNames.Cast<Object>().ToArray()); // filter by array
EntityCollection res = service.RetrieveMultiple(products);
IEnumerable<Product> opportunities = res.Entities
    .Select(product => product.ToEntity<Product>()); // you can use Linq again from here

Answer 2

When using QueryExpression, we can add condtionexpression for where clause. ConditionExpression takes a ConditionOperator enumerator, and we can use ConditionOperator.In. Below is how you initiate a conidtionExpression with an “In” operator, the third argument can be an array or collection.

ConditionExpression ce = new ConditionExpression("EntityName",
        ConditionOperator.In, collectionObject);

Please see below for further explanation.

http://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.query.conditionexpression.conditionexpression.aspx

Answer 3

No, you can't. CRM LINQ provider only allows variables to appear on the left side of expressions, while the right side must contain constants.

i.e.

Product.Where(e => e.Name == desiredName)

Is not supported and won't work (it will complain about using a variable on the right side of the comparison).

If you cannot avoid this kind of query, you have to .ToList() data first (this can lead to a huge result set and will probably turn up to be unconceivably slow):

Product.ToList().Where(e => e.Name == desiredName)

This will work, because now the .Where() is being applied on a List<> instead.

Another approach (I don't have data about performance, though) would be to create many queries, basically fetching the records one at a time:

// ... this is going to be a nightmare ... don't do it ...
var entities = new List<Product>();
entities.Add(Product.Where(e => e.Name == "Product 1"));
entities.Add(Product.Where(e => e.Name == "Product 2"));

Or use a QueryExpression like this (my personal favourite, because I always go late-bound)

var desiredNames = new string[]{"Product 1", "Product 2"};
var filter = new FilterExpression(LogicalOperator.And)
{
    Conditions = 
    {
        new ConditionExpression("name", ConditionOperator.In, desiredNames)
    }
};
var query = new QueryExpression(Product.EntityLogicalName)
{
    ColumnSet = new ColumnSet(true),
    Criteria = filter
};
var records = service.RetrieveMultiple(query).Entities;