public void NextXYearsConversion()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='next-x-years' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE createdon >= '{DateTime.Now:s}' AND createdon < '{DateTime.Today.AddDays(1).AddYears(2):s}'", NormalizeWhitespace(converted));
}
public void ParameterConversion()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
UseParametersForLiterals = true
}, out var parameters);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = @firstname", NormalizeWhitespace(converted));
Assert.AreEqual("Mark", parameters["@firstname"]);
}
public void AndOr()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
<filter type='or'>
<condition attribute='lastname' operator='eq' value='Carrington' />
<condition attribute='lastname' operator='eq' value='Twain' />
</filter>
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = 'Mark' AND (lastname = 'Carrington' OR lastname = 'Twain')", NormalizeWhitespace(converted));
}
public void EqBusinessId()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
context.AddFakeMessageExecutor <WhoAmIRequest>(new WhoAmIHandler());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='parentcustomerid' operator='eq-businessid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE parentcustomerid = '{WhoAmIHandler.BusinessUnitId:D}'", NormalizeWhitespace(converted));
}
public void JoinFilter()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<attribute name='name' />
<filter>
<condition attribute='name' operator='eq' value='data8' />
</filter>
</link-entity>
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname, account.name FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid AND account.name = 'data8' WHERE contact.firstname = 'Mark'", NormalizeWhitespace(converted));
}
private void AddConnection(ConnectionDetail con)
{
_metadata[con] = new AttributeMetadataCache(con.ServiceClient);
_objectExplorer.AddConnection(con);
// Start loading the entity list in the background
EntityCache.TryGetEntities(con.ServiceClient, out _);
_metadata[con].MetadataLoading += MetadataLoading;
//_metadata[con].LoadAllAsync();
}
public AutocompleteTests()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var a = metadata["account"];
var c = metadata["contact"];
var n = metadata["new_customentity"];
_autocomplete = new Autocomplete(new[] { a, c, n }, metadata);
}
public void Distinct()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch distinct='true'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT DISTINCT firstname, lastname FROM contact", NormalizeWhitespace(converted));
}
public void Order()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<order attribute='firstname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact ORDER BY firstname ASC", NormalizeWhitespace(converted));
}
public void Filter()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = 'Mark'", NormalizeWhitespace(converted));
}
public void Joins()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<attribute name='name' />
</link-entity>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname, account.name FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid", NormalizeWhitespace(converted));
}
/// <summary>
/// Gets metadata details for the given connection
/// </summary>
/// <returns></returns>
protected AttributeMetadataCache GetMetadataCache(SqlConnectionStringBuilder conStr)
{
if (conStr == null)
{
return(null);
}
var server = conStr.DataSource.Split(',')[0];
if (_metadataCache.TryGetValue(server, out var metadata))
{
return(metadata);
}
metadata = new AttributeMetadataCache(ConnectCDS(conStr));
_metadataCache[server] = metadata;
return(metadata);
}
public void EqUserId()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='account'>
<attribute name='name' />
<filter>
<condition attribute='ownerid' operator='eq-userid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.SqlCalculations
}, out _);
Assert.AreEqual("SELECT name FROM account WHERE ownerid = CURRENT_USER", NormalizeWhitespace(converted));
}
public void EqBusinessId()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='parentcustomerid' operator='eq-businessid' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.Literals
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE parentcustomerid = '{WhoAmIHandler.BusinessUnitId:D}'", NormalizeWhitespace(converted));
}
public void Distinct()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch distinct='true'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT DISTINCT firstname, lastname FROM contact", NormalizeWhitespace(converted));
}
/// <summary>
/// Get the metadata for the supplied attribute
/// </summary>
/// <param name="service">The organization service</param>
/// <param name="entityLogicalName">The logical name of the entity</param>
/// <param name="logicalName">The logical name of the attribute</param>
/// <param name="ignoreCache">A value indicating whether to ignore the cache</param>
/// <returns>The metadata for the supplied attribute</returns>
public static AttributeMetadata GetAttributeMetadata(IOrganizationService service, string entityLogicalName, string logicalName, bool ignoreCache = false)
{
lock (LockAttributeMetadataCache)
{
string key = $"{entityLogicalName}.{logicalName}";
AttributeMetadata attributeMetadata;
if (ignoreCache || DateTime.Now > AttributeMetadataCacheExpiry)
{
AttributeMetadataCache.TryRemove(key, out attributeMetadata);
AttributeMetadataCacheExpiry = DateTime.Now.AddMinutes(CacheValidMinutes);
}
attributeMetadata = AttributeMetadataCache.GetOrAdd(key, x => ((RetrieveAttributeResponse)service.Execute(new RetrieveAttributeRequest {
EntityLogicalName = entityLogicalName, LogicalName = logicalName, RetrieveAsIfPublished = true
})).AttributeMetadata);
return(attributeMetadata);
}
}
public void NextXYearsConversion()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='next-x-years' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
ConvertFetchXmlOperatorsTo = FetchXmlOperatorConversion.Literals
}, out _);
Assert.AreEqual($"SELECT firstname, lastname FROM contact WHERE createdon >= '{DateTime.Now:s}' AND createdon < '{DateTime.Today.AddDays(1).AddYears(2):s}'", NormalizeWhitespace(converted));
}
public void ParameterConversion()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='firstname' operator='eq' value='Mark' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions {
UseParametersForLiterals = true
}, out var parameters);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE firstname = @firstname", NormalizeWhitespace(converted));
Assert.AreEqual("Mark", parameters["@firstname"]);
}
public void JoinFilterOr()
{
var metadata = new AttributeMetadataCache(_service);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<link-entity name='account' from='accountid' to='parentcustomerid'>
<filter type='or'>
<condition attribute='name' operator='eq' value='Data8' />
<condition attribute='name' operator='eq' value='Microsoft' />
</filter>
</link-entity>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(_service, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT contact.firstname, contact.lastname FROM contact INNER JOIN account ON contact.parentcustomerid = account.accountid AND (account.name = 'Data8' OR account.name = 'Microsoft')", NormalizeWhitespace(converted));
}
public void CustomOperator()
{
var context = new XrmFakedContext();
context.InitializeMetadata(Assembly.GetExecutingAssembly());
var org = context.GetOrganizationService();
var metadata = new AttributeMetadataCache(org);
var fetch = @"
<fetch>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter>
<condition attribute='createdon' operator='last-x-days' value='2' />
</filter>
</entity>
</fetch>";
var converted = FetchXml2Sql.Convert(org, metadata, fetch, new FetchXml2SqlOptions(), out _);
Assert.AreEqual("SELECT firstname, lastname FROM contact WHERE createdon = lastxdays(2)", NormalizeWhitespace(converted));
}
public AutocompleteTests()
{
var metadata = new AttributeMetadataCache(_service);
var a = metadata["account"];
var c = metadata["contact"];
var n = metadata["new_customentity"];
typeof(AttributeMetadata).GetProperty(nameof(AttributeMetadata.IsValidForUpdate)).SetValue(a.Attributes.Single(attr => attr.LogicalName == "primarycontactidname"), false);
typeof(AttributeMetadata).GetProperty(nameof(AttributeMetadata.AttributeOf)).SetValue(a.Attributes.Single(attr => attr.LogicalName == "primarycontactidname"), "primarycontactid");
typeof(AttributeMetadata).GetProperty(nameof(AttributeMetadata.IsValidForUpdate)).SetValue(c.Attributes.Single(attr => attr.LogicalName == "fullname"), false);
var dataSources = new Dictionary <string, AutocompleteDataSource>
{
["local"] = new AutocompleteDataSource
{
Name = "local",
Entities = new[] { a, c, n },
Metadata = metadata
}
};
_autocomplete = new Autocomplete(dataSources, "local");
}
/// <summary>
/// Handles the entity retrieve multiple message.
/// </summary>
/// <param name="context">The context.</param>
public override void HandleRetrieveMultipleMessage(PluginExecutionContext context)
{
base.HandleRetrieveMultipleMessage(context);
var query = context.PluginContext.InputParameters["Query"];
if (query != null)
{
var mapper = new GenericMapper(context);
EntityCollection collection = new EntityCollection();
string fetchXml = string.Empty;
if (query is QueryExpression qe)
{
var convertRequest = new QueryExpressionToFetchXmlRequest();
convertRequest.Query = (QueryExpression)qe;
var response = (QueryExpressionToFetchXmlResponse)context.Service.Execute(convertRequest);
fetchXml = response.FetchXml;
}
else if (query is FetchExpression fe)
{
fetchXml = fe.Query;
}
if (!string.IsNullOrEmpty(fetchXml))
{
context.Trace($"Pre FetchXML: {fetchXml}");
var metadata = new AttributeMetadataCache(context.Service);
var fetch = Deserialize(fetchXml);
mapper.MapFetchXml(fetch);
//Store page info before converting
int page = -1;
int count = -1;
if (!string.IsNullOrEmpty(fetch.page))
{
page = Int32.Parse(fetch.page);
fetch.page = string.Empty;
}
if (!string.IsNullOrEmpty(fetch.count))
{
count = Int32.Parse(fetch.count);
fetch.count = string.Empty;
}
var sql = FetchXml2Sql.Convert(context.Service, metadata, fetch, new FetchXml2SqlOptions {
PreserveFetchXmlOperatorsAsFunctions = false
}, out _);
sql = mapper.MapVirtualEntityAttributes(sql);
context.Trace($"SQL: {sql}");
if (page != -1 && count != -1)
{
collection = this.GetEntitiesFromSql(context, mapper, sql, count, page);
}
else
{
collection = this.GetEntitiesFromSql(context, mapper, sql, -1, 1);
}
}
context.Trace($"Records Returned: {collection.Entities.Count}");
context.PluginContext.OutputParameters["BusinessEntityCollection"] = collection;
}
}
public SharedMetadataCache(ConnectionDetail connection, IOrganizationService org)
{
_connection = connection;
_innerCache = new AttributeMetadataCache(org);
}
