Retrieve a table row using the SDK for .NET

You will typically retrieve a row based on the results of a query and the query results should include a unique identifier for the row.

Note

In the following examples the accountid variable represents the Guid identifier for an account row.

You have some options to define the data returned when you retrieve a row. You will use the ColumnSet class to define which column (attribute) values you require.

Important

When retrieving rows you should only request the column values you need by setting the specific columns using the ColumnSet class constructor. Although ColumnSet class constructor provides an overload that accepts a boolean allColumns parameter, you should not use this in production code. More information: Do not retrieve Entity all columns via query APIs

If you need to return related rows you can include a query with your retrieve request to define which related rows to return.

Basic Retrieve

You can retrieve individual rows using either the IOrganizationService.Retrieve method or by setting the Target property of the RetrieveRequest class to a reference row and use the IOrganizationService.Execute method.

This example shows using the IOrganizationService.Retrieve method.

Entity entity = svc.Retrieve("account", accountid, new ColumnSet("name"));
Console.WriteLine("account name: {0}", entity["name"]);

This example shows using the RetrieveRequest and RetrieveRequest classes with the IOrganizationService.Execute method.

RetrieveRequest request = new RetrieveRequest()
{
  ColumnSet = new ColumnSet("name"),
  Target = new EntityReference("account", accountid)
};
var response = (RetrieveResponse)svc.Execute(request);
Entity entity = response.Entity;
Console.WriteLine("account name: {0}", entity["name"]);

Note

Most of the time you should use the IOrganizationService.Retrieve method.

Use RetrieveRequest with the IOrganizationService.Execute method for special circumstances as described below. More information:

When you retrieve an individual row you can also include a query to include related rows by setting the RelatedEntitiesQuery property of the RetrieveRequest.

You can define a query using any of the classes derived from QueryBase and associate it with a specific table row relationship. Add a collection of pairs of queries and relationships to the RelatedEntitiesQuery property using a RelationshipQueryCollection.

The following example includes task and contact rows related to the account row that is being retrieved.


var relationshipQueryCollection = new RelationshipQueryCollection();

var relatedTasks = new QueryExpression("task");
relatedTasks.ColumnSet = new ColumnSet("subject", "description");
var taskRelationship = new Relationship("Account_Tasks");
relationshipQueryCollection.Add(taskRelationship, relatedTasks);


var relatedContacts = new QueryExpression("contact");
relatedContacts.ColumnSet = new ColumnSet("fullname", "emailaddress1");
var contactRelationship = new Relationship("account_primary_contact");
relationshipQueryCollection.Add(contactRelationship, relatedContacts);

var request = new RetrieveRequest()
{
  ColumnSet = new ColumnSet(true),
  RelatedEntitiesQuery = relationshipQueryCollection,
  Target = new EntityReference("account", accountid)
};

RetrieveResponse response = (RetrieveResponse)svc.Execute(request);

Entity retrievedAccount = response.Entity;

Console.WriteLine("Account Name: {0}",retrievedAccount["name"]);

var tasks = retrievedAccount.RelatedEntities[new Relationship("Account_Tasks")];

Console.WriteLine("Tasks:");
tasks.Entities.ToList().ForEach(x => {
  Console.WriteLine(" Task Subject: {0}",x["subject"]);
});

Entity primaryContact = retrievedAccount
  .RelatedEntities[new Relationship("account_primary_contact")]
  .Entities.FirstOrDefault();

Console.WriteLine("Primary Contact Fullname: {0}",primaryContact["fullname"]);

The results of the sample could look like the following:

Account Name: City Power & Light (sample)
Tasks:
 Task Subject: Task 1
 Task Subject: Task 2
Primary Contact Fullname: Scott Konersmann (sample)

More information: Query data using the SDK for .NET

Retrieve with an alternate key

If you have configured a table to use an alternate key, you can use this alternate key to define an EntityReference and pass this value as the RetrieveRequest.Target property.

For example, if you define the account accountnumber column to be an alternate key, you can retrieve an account using the value of that column.

RetrieveRequest request = new RetrieveRequest()
{
ColumnSet = new ColumnSet("name"),
Target = new EntityReference("account", "accountnumber", "0001")
};
var response = (RetrieveResponse)svc.Execute(request);
Entity entity = response.Entity;

Console.WriteLine(entity["name"]);

If your alternate key is a composite of several columns (attributes), you would define a KeyAttributeCollection. The following example is for an account that has an alternate key that includes both the accountnumber and sic attributes.

var keyCollection = new KeyAttributeCollection();
keyCollection.Add("accountnumber", "0001");
keyCollection.Add("sic", "7372");

RetrieveRequest request = new RetrieveRequest()
{
ColumnSet = new ColumnSet("name"),
Target = new EntityReference("account", keyCollection)
};
var response = (RetrieveResponse)svc.Execute(request);
Entity entity = response.Entity;

Console.WriteLine(entity["name"]);

Note

Alternate keys are usually used only for data integration scenarios

Retrieve records from elastic tables

If you are retrieving elastic table data stored in partitions be sure to specify the partition key when retrieving that data. More information: Retrieve a record in an elastic table

Access Formatted values

The method to access formatted values on a retrieve operation is the same you will use when accessing them in the results of a query. More information: Access formatted values

See also

Create table rows using the SDK for .NET
Update and delete table rows using the SDK for .NET
Associate and disassociate table rows using the SDK for .NET