Using LINQ with the system:inmation SCI API

The SCI API allows LINQ statements to be used to query objects in system:inmation. LINQ (or Language INtegrated Query) is part of the .NET framework and allows for queries to be made directly in C# applications to different data sources (databases, XML and here, objects in inmation). A direct LINQ statement can be executed with the SCI to query the properties of objects in system:inmation. The query results are then returned as a list of either SytemDbProperty objects or ReadItem objects. To use LINQ to query inmation objects you will need to first install the Nuget package EntityFramework.SqlServerCompact 4.0 with its dependencies.

Example Code

As with other examples using the SCI, a stateless interface is first initialized and security credentials set:

// Create the stateless call interface object and Security Credentials
StatelessInterface sli = new StatelessInterface(new TcpConfig() { HostNameOrIp = "localhost", Port = 6512 });
SecurityCredentials securityCredentials = new SecurityCredentials(){ ProfileName = "so", Password = "inmation" };

An instance of the PropertiesContext class is created. This is part of the EntityFramework.SqlServerCompact package and enables LINQ statements to be translated to SQL statements. This is then used as the basis to query system:inmation objects. The Security Credentials for connection to the Core and a timeout value are entered as arguments.

// Create the PropertiesContext, requires the nuget package EntityFramework.SqlServerCompact 4.0 with its dependencies.
PropertiesContext propertiesContext = new PropertiesContext(securityCredentials, 1000);

The results of LINQ queries can be returned as either a list of SystemDbProperty objects or a list of ReadItem objects. This return object type depends on the execution of the LINQ query statement. To return query results as a list of SystemDbProperty objects, the list is first initialized then the LINQ statement is appended with .ToList(). In the following example, the LINQ statement queries objects where the OPC low limit is equal to ten.

// Initialize query result container
List<SystemDbProperty> systemDbPropResult;
// LINQ Equal statement
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow == 10).ToList();

To return the query as a List of ReadItem objects, the list is first initialized, then the “Where” LINQ statement appended with .ToListOfReadItems()

// Initialize list of ReadItems for query result
List<ReadItem> readItemResult = new List<ReadItem();
// LINQ Equal statement
readItemResult = propertiesContext.Properties.Where(p => p.OpcLimitLow == 10).ToListOfReadItems;

Currently only the "Where" LINQ clause is functional within the system:inmation SCI. All LINQ statements should therefore be constructed around the "Where" clause.

Examples of other LINQ statements to query object properties

Listed below are examples of LINQ statements submitted using the PropertiesContext class in the SCI. All examples are using the systemDbProperty list for the query result. Substitute the .ToIist() appendage with .ToReadItemsList() to return as list of ReadItem objects.

// GreaterThan
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow > 9).ToList();

// GreaterThanOrEqual
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow >= 9).ToList();

// LessThan
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow < 10.6).ToList();

// LessThanOrEqual
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow <= 10.6).ToList();

// NotEqual
systemDbPropResult = propertiesContext.Properties.Where(p => p.OpcLimitLow != 10.6).ToList();

// StartsWith
systemDbPropResult = propertiesContext.Properties.Where(p => p.ObjectName.StartsWith("System")).ToList();

// EndsWith
systemDbPropResult = propertiesContext.Properties.Where(p => p.Path.EndsWith("/System")).ToList();

// Equal String
systemDbPropResult = propertiesContext.Properties.Where(p => p.Path.Equals("/System")).ToList();

// Contains (single value)
systemDbPropResult = propertiesContext.Properties.Where(p => p.ObjectName.Contains("System")).ToList();

// Contains (IN operator string)
List<string> stringValues = new List<string> { "System", "Test01" };
systemDbPropResult = propertiesContext.Properties.Where(p => stringValues.Contains(p.ObjectName)).ToList();

// Contains (IN operator double)
List<double> doubleValues = new List<double> { -12.6, -1, 0, 10, 100.7 };
systemDbPropResult = propertiesContext.Properties.Where(p => doubleValues.Contains(p.OpcLimitLow)).ToList();

// Contains (IN operator int)
List<int> values = new List<int> { -1, 0, 1000 };
systemDbPropResult = propertiesContext.Properties.Where(p => values.Contains(p.ModelLockGranularity)).ToList();

// Contains (IN operator long)
List<long> longValues = new List<long> { -1, 3, 10 };
systemDbPropResult = propertiesContext.Properties.Where(p => longValues.Contains(p.AccessControlHolder)).ToList();

// Equal Boolean
systemDbPropResult = propertiesContext.Properties.Where(p => p.TimeSeriesUsePrimary).ToList();

// Not Equal Boolean
systemDbPropResult = propertiesContext.Properties.Where(p => !p.LocationTracking).ToList();

// OR Operator
systemDbPropResult = propertiesContext
    .Properties
    .Where(p => p.ObjectName.Contains("System") || p.Path.Contains("System")).ToList();

// AND Operator only supported for properties which represent table columns
// (ColumnObjid, ColumnCode, ColumnValueAsString, ColumnValueAsDouble, ColumnPosition)
// For And on Table join is not implemented
systemDbPropResult = propertiesContext.Properties.Where(p => p.ColumnCode == 1 && p.ColumnValueAsString.Equals("System")).ToList();