Recently, a client requested that we develop a page for their website that consisted of a grid that they wanted populated with specific data from multiple records in CRM. Initially, this seemed like a very simple possible solution utilizing joining of tables by foreign keys, but the mindset of ‘Keep it Simple’ was looming over the development process.
When I sat down for the documentation process, it was discovered that there were two solutions to this problem, one sloppy and meant for speed and the other which utilized the Xrm/Crm SDK. The former, would have been to fill the page with absurd amounts of query statements that would have been prone to a vast array of errors or null references if data was not perfect every single time for each record. The latter includes utilizing the EntityAlias property on the LinkEntity Class in the XRM SDK.
Need Dynamics CRM or D365 Hosting?
Cobalt has 3 different hosting packages and 15+ years of hosting and Dynamics expertise.
Normalizing your database to reduce redundancy
In this situation we find ourselves asking the question ‘Why wouldn’t one record have all the data required for a row in this grid already’? Well, the short answer is: In order to have a single entry contain all of the required information would mean adding extra rows that may be used other records, this would cause our database to inefficient and cumbersome to manage. The true answer is because following Normal Forms is critical to the longevity of your solution/platform.
The goal of normalizing a database can be reduced to a single rhetorical question ”Is this information stored twice”? If the answer is yes, then the data can be stored more efficiently. The most valuable benefit of normalizing a database is the ability to modify a category or shared value and have it effect multiple, sometimes millions, of records.
An example would be the following:
Table: Users
UserID (PK) | Username | Password | FirstName | LastName
Table: Chirps
ChirpID (PK) | UserID (FK -> UserID – User Table) | ChirpMessage | DateTime
Table: Likes – Composite Table
ChirpID (FK -> ChirpID – Chirp Table) | UserLikeID (FK -> UserID – User Table) | DateTime
Table: Followers – Composite Table
UserID (FK -> UserID – User Table) | ChirpFollowerID (FK -> UserID – User Table) | DateTime
The above table is at the least in Third Normal Form, as there is no duplicate data and only foreign key references in which direct access to another table’s information is provided. The tables have no functional or transitive dependencies.
Let’s say for this example we are using the above table and a client wants their webpage to have a grid populated with Chirp Users with the following information:
UserID | Username | Total Followers | Most Recent Chirp Message | First Name
In an inefficient redundant database, a table could exist with all of this information already and the problem would be solved, but this is not an ideal nor proper way to engineer a product and its related database.
A developer could in theory fill each of these fields with several queries that pull information for each of these fields with joins to each table and populating the related information with the results. Allow theoretically correct, this leads to spaghetti code in your software as one page would be responsible for querying several different tables on each page load. This is not the most ideal solution to this grid issue.
Solution
Within the Xrm SDK, there exists a property on the LinkEntity class called EntityAlias. The LinkEntity’s EntityAlias allows a developer to use a query expression to link one entity to a related entity in the database to another, bind the Entity with the Alias of that Table, and specify which Column set they would like to grab back from that related table and set a property value to the original object/query result.
An Example would be the following:
Entity user = new Entity(‘User’);
User.Attributes[“Username”] = “ChirpUser01”;
User.Attributes[“FirstName”] = “Alex”;
User.Attributes[“LastName”] = “Green”;
User.Attributes[“Password”] = EncryptRandomPassword();
Guid userID = _orgService.Create(user, null);
Entity user2 = new Entity(‘User’);
User2.Attributes[“Username”] = “ChirpUser02”;
User2.Attributes[“FirstName”] = “Bradford”;
User2.Attributes[“LastName”] = “Towle”;
User2.Attributes[“Password”] = EncryptRandomPassword();
Guid userID = _orgService.Create(user2, null);
Entity chirp = new Entity(“Chirp”);
Chirp.Attributes[“UserID”] = user.ID;
Chirp.Attributes[“ChirpMessage”] = “Hi, I am messaging on Chirp!”;
Chirp.Attributes[“Date”] = new CrmDateTime(DateTime.Now, TimeZoneInfo.Local);
Guid chirpID = _orgService.Create(chirp, null);
Entity follower = new Entity(“Follower”);
Follower.Attributes[“UserID”] = user2.ID;
Follower.Attributes[“FollowerID”] = user1.ID;
Follower.Attributes[“Date”] = new CrmDateTime(DateTime.Now, TimeZoneInfo.Local);
Guid FollowerID = _orgService.Create(follower, null);
//Query for information to bind to grid
QueryExpression query = new QueryExpression();
Query.EntitName = “User”;
Query.ColumnSet = new ColumnSet();
Query.ColumnSet.Columns.Add(“UserID”);
Query.ColumnSet.Columns.Add(“Username”);
Query.ColumnSet.Columns.Add(“FirstName”);
//Link to related table
Query.LinkEntities.Add(new LinkEntity(“User”, “Chirp”, “UserID”, “UserID”, JoinOperator.Inner));
Query.LinkEntities[0].EntityAlias = “Chirp”;
Query.LinkEntities[0].Columns.Add( new string[] {Chirp.MostRecentChirpMessage});
//Link to related table
Query.LinkEntities.Add(new LinkEntity(“User”, “Follower”, “UserID”, “UserID”, JoinOperator.Inner));
Query.LinkEntities[1].EntityAlias = “Follower”;
Query.LinkEntities[1].Columns.Add(new string[]{Follower.Total});
EntityCollection entityCollection = _orgService.RetrieveMultiple(query);
//grabbing property values of collection
//Query for users based on criteria provided by client
//build table with specified column set
DataTable table = new DateTable();
Table.Columns.Add(“UserID”);
Table.Columns.Add(“Username”);
Table.Columns.Add(“Total Followers”);
Table.Columns.Add(“Most Recent Chirp Message”);
Table.Columns.Add(“First Name”);
Table.Columns.Add(“Follower Count”);
DataRow newRow;
//null check your collection of users
If(users != null && users.Count > 0)
{
Foreach(var user in users)
newRow = table.NewRow();
newRow[“UserID”] = user.UserID;
newRow[“Username”] = user.Username;
newRow[“Total Followers”] = user.GetPropertyValue(“Follower.Total”);
newRow[“Most Recent Chirp”] = user.GetPropertyValue(“Chirp.MostRecentChirpMessage”);
newRow[“First Name”] = user.FirstName;
newRow.EndEdit();
table.Rows.Add(newRow);
}
General Thoughts on the Solution
The Link Entity class in the Xrm framework contains powerful methods that will allow developers to access and build methods that will communicate with CRM to achieve solutions similar to the problem stated above. While this may seem like an obvious solution to some CRM developers, it is always good to practice reducing the amount of code needed in your software by using all the possible routes provided by Microsoft’s SDK.
Considering a CRM Migration?
Our team at Cobalt has completed hundreds of secure, seamless CRM migration projects. If you’re considering a move in the next few years, take a look at the best of what we’ve learned through 26+ years of CRM experience:
