Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> sub query as the input to a database function
 

Pages: 1
LLBLGen Pro Runtime Framework
sub query as the input to a database function
Page:1/1 

  Print all messages in this thread  
Poster Message
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# 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.

Code:

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


Code:

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  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 05-Feb-2008 16:08:35.  
I figured it out. The code that accomplished this isbelow.

Code:

                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;





Brandt  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.