How to get all populated columns from CRM using `QueryExpression` object?

Question

I've asked a similar question before here and the answer solved my problem. However, now I'm getting the same problem as before but the answer doesn't work anymore due to change in conditions.

Here's the query I'm running. I've checked that someField is set to something (it's a look-up field referring to an other entity by a guid). I get no entries in result (unless I switch the condition to "not equal"). That's probably due to the fact that the field isn't brought in.

When i breakpoint the execution and check Attributes, I see 21 fields out of a total of 30 (some of them might be empty, sure, but this one, i.e. someField, is not) but the one I'm interested in, isn't there!

QueryExpression query = new QueryExpression
{
  EntityName = "entity",
  ColumnSet = new ColumnSet{ AllColumns = true },
  // Here I tried the code addition #1 below
  Criteria =
  {
    Filters =
    {
      new FilterExpression
      {
        Conditions =
        {
          new ConditionExpression("someField", ConditionOperator.Equal, guid)
        }
      }
    }
  }
};
// Here I tried the code addition #2 below
EntityCollection result = Service.RetrieveMultiple(query);

What do I miss and how can I resolve it?

I tried using LinkEntities as discussed in this blog but it didn't really work out. I'm not even sure if it was a meaningful approach. It looked as follows.

  LinkEntities =
  {
    new LinkEntity
    {
      Columns = new ColumnSet { AllColumns = true },
      LinkFromEntityName = "entity",
      LinkFromAttributeName = "otherEntityId",
      LinkToEntityName = "entity2",
      LinkToAttributeName = "entity2Id"
    }
  },

I also tried to employ the solution suggested on MSDN. The same result.

request.LinkEntities.Add(
  new LinkEntity(
    "entity", "entity2", "otherEntityId", "entity2Id", JoinOperator.Inner));
request.LinkEntities[0].Columns.AddColumns("entity2Id");
request.LinkEntities[0].EntityAlias = "blobb";

Once again - the solution provided by @JamesWood isn't working anymore, since I've got the administrator access and the regarded field is not empty.

Answer 1

Query Expression just gives you columns with values as null values are not added to the property dictionary an so then are not transferred to the client. That's why you always have to check for Contains before accessing a property (or user GetAttributeValue<T>(String)).

If you always want to know which fields are existing on an entity, you have to retrieve the metadata of the entity using the RetrieveEntityRequest. See MSDN for it.

Answer 2

I believe the issue here is in your FilterExpression and/or the actual data itself.

From what you have said here:

I get no entries in result (unless I switch the condition to "not equal")

When you set the filter to "equal", nothing is equal to that Guid so you get no results.

When you set the filter to "not equal", everything is not equal to that condition so you start getting some results. When you do get results the lookup is not included because it is null the record (and hence why the "equal" filter doesn't work).

As I'm not certain about the exact issue I suggest taking these steps:

1. For that record type, delete all the records.
2. Make one new record, populate the lookup field.
3. Write a query that returns all fields from that entity (e.g. AllColumns = true) without specifying a FilterExpression.
4. This query should give you one result, inspect the attributes collection to make sure the lookup is populated (check all the attributes to make sure you haven't just got the wrong name).
5. Then extend the query to have a FilterExpression.

There is an example of filtering with a lookup here.

As a side note; you don't actually have to specify in the ColumnSet columns you are going to use for filtering.

E.g. in SQL you could do:

SELECT firstname FROM contact WHERE lastname = 'wood'

Answer 3

Here is a method to search for, and return back the first entity for your SELECT * FROM entity WHERE someField = "1234".

private static Entity GetEntityByGuid(
  IOrganizationService service, 
  String entityLogicalName, 
  String fieldToConstrainOn, 
  Guid valuetoConstrainTo)
{
  var qe = new QueryExpression(entityLogicalName)
  {
    ColumnSet = new ColumnSet(true)
  };

  qe.Criteria.AddCondition(
    fieldToConstrainOn, ConditionOperator.Equal, valuetoConstrainTo);

  return service.RetrieveMultiple(qe).Entities.FirstOrDefault();
}

You can call it like so (assuming that new_entity is the logical name of your custom entity, and new_somefield is a foreign key to some other entity with the id of "ed5081c1-f77b-4f58-a8bf-a8fbe3fe27be") :

var entity = GetEntityByGuid(
  service, "new_entity", "new_somefield", 
  new Guid("ed5081c1-f77b-4f58-a8bf-a8fbe3fe27be"));

And to access new_somefield's name:

String name = entity.GetAttributeValue<EntityReference>("new_somefield").Name;