sub query as the input to a database function

Posts   
 
    
Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 05-Feb-2008 15:51:57   

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

Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 05-Feb-2008 16:08:35   

I figured it out. The code that accomplished this isbelow.


                IPredicateExpression predicate = new PredicateExpression(NetworkDeviceFields.NetworkDeviceGuid == NetworkDeviceInterfaceFields.NetworkDeviceGuid);
                predicate.AddWithAnd(NetworkDeviceInterfaceFields.NetworkDeviceInterfaceGuid == NetworkDeviceInterfaceIpFields.NetworkDeviceInterfaceGuid);

                IRelationCollection relations = new RelationCollection(NetworkDeviceInterfaceEntity.Relations.NetworkDeviceInterfaceIpEntityUsingNetworkDeviceInterfaceGuid, JoinHint.Inner);

                IFieldInfo fieldInfo = new FieldInfo("IpAddress", string.Empty, typeof(System.String), false, false, true, true, 0, 0, 0, 0);

                EntityField2 ipField = NetworkDeviceInterfaceIpFields.IpAddress;
                ipField.SetExpression(new DbFunctionCall("dbo.strconcat({0})", new object[] { NetworkDeviceInterfaceIpFields.IpAddress }));

                EntityField2 field = new EntityField2(fieldInfo);
                field.SetExpression(new ScalarQueryExpression(ipField, predicate, relations, false));
                field.SetAggregateFunction(AggregateFunction.None);

                return field;