Dynamics CRM - Writing FetchXml Queries
While browsing the online Dynamics CRM SDK a while back I noticed that FetchXml had a XSD schema. So I grabbed the xsd.exe tool from Visual Studio, generated a cs file to see if I can use that to create FetchXml queries. Here are my findings.
To generate the cs file
xsd.exe /c /n:ExamplNamespace FetchXml.xsd
You'll notice that the resulting code file has an object array called Items, if have a look at the metadata attributes on that field you'll see which elements are supported. For example the "fetch" class Items array will support "entity" and "order"; how do we use it?
fetch sample = new fetch
{Items = new object[]
{new entity { name = "account" },
new order { attribute = "name", descending = false }
}
};
Dig a little deeper and you'll see that you are able to generate more complex queryes. For example:
Get the first 10 accounts ordered by account name along with the primary contact.
fetch f = new fetch
{mapping = fetchMapping.logical, // not required
aggregate = false, // not required
count = "10", // not required
distinct = false, // not required
page = "1",Items = new object[] {
new entity {
name = "account", Items = new object[] {
new linkentity {
name = "contact", // link entity schema name
from = "contactid", // link entity key schema name
to = "primarycontactid", // parent link entity key schema name
Items = new object[] {
new attribute { name = "contactid" },
new attribute { name = "fullname" },
new order { attribute = "fullname", descending = false }
}
},
new attribute { name = "accountid" }, // or new allattributes { }
new attribute { name = "name" }
}
},
new order { attribute = "name", descending = false }
}
};
Now that we have defined the query we need to create a string representation of it so that the crm web service can understand it. To do that we will use the XmlSerializer to serialize this fetch instance.
XmlSerializer xs = new XmlSerializer(typeof(fetch));
StringWriter sw = new StringWriter();
xs.Serialize(sw, f);
sw.Close();
string fetchXml = sw.ToString();string result = sdk.Fetch(fetchXml);