I have created the field below which returns a single value representing the primary IpAddress of a network device. I have verified that the field creates the correct subselect. What i would like to do is create a field that returns the result of a database function I created called strconcat that accepts a series of rows and concats them to a single string. I took a look at the documentation for the "Function calls in expressions" and i haven't been able to determine which expression type I would need to use.
IPredicateExpression predicate = new PredicateExpression(NetworkDeviceFields.NetworkDeviceGuid == NetworkDeviceInterfaceFields.NetworkDeviceGuid);
predicate.AddWithAnd(NetworkDeviceInterfaceFields.NetworkDeviceInterfaceGuid == NetworkDeviceInterfaceIpFields.NetworkDeviceInterfaceGuid);
predicate.AddWithAnd(NetworkDeviceInterfaceIpFields.IsPrimary == true);
IRelationCollection relations = new RelationCollection(NetworkDeviceInterfaceEntity.Relations.NetworkDeviceInterfaceIpEntityUsingNetworkDeviceInterfaceGuid, JoinHint.Inner);
IFieldInfo fieldInfo = new FieldInfo("PrimaryIpAddress", string.Empty, typeof(System.String), false, false, true, true, 0, 0, 0, 0);
EntityField2 field = new EntityField2(fieldInfo);
field.SetExpression(new ScalarQueryExpression(NetworkDeviceInterfaceIpFields.IpAddress, predicate, relations, true));
field.SetAggregateFunction(AggregateFunction.None);
return field;
An example of the working query is below.
(SELECT dbo.strconcat(NDII.IpAddress) AS IPAddresses
FROM Infrastructure.NetworkDeviceInterface AS NDI INNER JOIN
Infrastructure.NetworkDeviceInterfaceIp AS NDII ON NDI.NetworkDeviceInterfaceGuid = NDII.NetworkDeviceInterfaceGuid
WHERE (NDI.NetworkDeviceGuid = ND.NetworkDeviceGuid)) AS IpAddresses
SELECT ND.NetworkDeviceGuid, ND.Name, ND.DomainName, ND.Class, ND.Status, ND.SiteId, ND.Project, ND.Critical, ND.Monitored, ND.Location, ND.Description,
ND.AssetTag, ND.SerialNumber, ND.Model, ND.Manufacture, ND.OperatingSystemType, Location.Region.Name AS RegionName,
Location.Country.Name AS CountryName, Location.Country.CountryCode, Location.Region.RegionCode, Location.Address.City, Location.Address.StateProvidence,
(SELECT dbo.strconcat(NDII.IpAddress) AS IPAddresses
FROM Infrastructure.NetworkDeviceInterface AS NDI INNER JOIN
Infrastructure.NetworkDeviceInterfaceIp AS NDII ON NDI.NetworkDeviceInterfaceGuid = NDII.NetworkDeviceInterfaceGuid
WHERE (NDI.NetworkDeviceGuid = ND.NetworkDeviceGuid)) AS IpAddresses FROM Location.Address INNER JOIN
Location.Country ON Location.Address.CountryCode = Location.Country.CountryCode INNER JOIN
Location.Region ON Location.Country.RegionCode = Location.Region.RegionCode INNER JOIN
Location.Site ON Location.Address.AddressGuid = Location.Site.AddressGuid INNER JOIN
Infrastructure.NetworkDevice AS ND ON Location.Site.SiteId = ND.SiteId