DynamicOps or VMWare VCloud Automation Center uses Microsoft’ ADO DotNet DataServices for interacting with it’s ODATAData Model. ODATA is essentially your database structure presented over HTTP in which typical Create, Read, Update, Delete (CRUD) operations can apply. So basically a relational database can be mapped to an object model.

I am often asked about LINQ queries when writing workflows within VCAC, if there is not an available workflow activity within the DynamicOps Cloud Development Kit (CDK) you can use this as a guide. The following post will show how to interact with the vCAC database via the ODATA REST interface & commonly used line queries.

Download Linqpad and connect to the vCAC Service Endpoint, if connecting from the local VCAC instance the url will look like this:

http://localhost/Repository/Data/ManagementModelEntities.svc

 Once connected it will look like this:

            

When querying from the database entities may require expanding, meaning that if there is a relationship with one table to another by default it will be null (or empty collection) unless explicitly requested. The more expands you apply to the query the more intense the underlying SQL query will be on the back end.

Top Linq Queries in Linqpad and Microsoft VB/C# expressions. 

Fetch a single virtual machine
Linqpad:
from vm in VirtualMachines 
where vm.VirtualMachineName == “vm1” 
select vm 

Windows Workflow (Vb Expression):
VirtualMachines.Where(Function (vm) vm.VirtualMachineName = “vm1”).FirstOrDefault() 

C# Expression
VirtualMachines.Where(vm=> vm.VirtualMachineName == “vm1”).FirstOrDefault() 

In this basic example the machine vm1 is being returned by the server with no expanded properties. The FirstOrDefault() is used to return a single entry instead of list. The query to return a list can be altered to return all machines that starts with a v like this:
VirtualMachines.Where(vm=> vm.VirtualMachineName.StartsWith(“v”).ToList()


Fetch a single virtual machine with it’s associated properties
Linqpad:
from vm in VirtualMachines
.Expand(“VirtualMachineProperties”) 
where vm.VirtualMachineName == “vm1” 
select vm 

Windows Workflow (Vb Expression):
VirtualMachines.Expand(Function(vm) vm.VirtualMachineProperties).Where(Function (vm) vm.VirtualMachineName = “vm1”).FirstOrDefault() 

C# Expression
VirtualMachines.Expand(vm => vm.VirtualMachineProperties ).Where(vm=> vm.VirtualMachineName == “vm1”).FirstOrDefault() 



Fetch a single virtual machine from the database by its primary key
Linqpad:
from vm in VirtualMachines 
where vm.VirtualMachineId == new Guid(“623a6210-9982-4bdf-af4a-7d56e7686d86“) && 1 == 1
select vm 

Windows Workflow (Vb Expression):
VirtualMachines.Where(Function (vm) vm.VirtualMachineId = new Guid(““623a6210-9982-4bdf-af4a-7d56e7686d86“) And 1 == 1).FirstOrDefault() 

C# Expression
VirtualMachines.Where(vm=> vm.VirtualMachineId == new Guid(““623a6210-9982-4bdf-af4a-7d56e7686d86“) && 1 == 1).FirstOrDefault() 

In this example the 1==1 (a positive result) is required to stop an error being returned from the service, instead it will return a null result or empty collection.

Fetch a single virtual machine from the database along with its HostReservation
Linqpad:
from vm in VirtualMachines
.Expand(“HostReservation”) 
where vm.VirtualMachineName == “vm1” 
select vm 

Windows Workflow (Vb Expression):
VirtualMachines.Expand(Function(vm) vm.HostReservation).Where(Function (vm) vm.VirtualMachineName = “vm1”).FirstOrDefault() 

C# Expression
VirtualMachines.Expand(vm=> vm.HostReservation).Where(vm=> vm.VirtualMachineName == “vm1”).FirstOrDefault() 

Fetch ALL Compute resources Endpoints and their Credentials
Linqpad:
from host in Hosts
.Expand(“ManagementEndpoint/Credential”)
select host

Windows Workflow (Vb Expression):
Hosts.Expand(Function(host) host.ManagementEndpoint.Credential).ToList() 

C# Expression
Hosts.Expand(host=> host.ManagementEndpoint.Credential).ToList() 

In this example it shows 2 depths of expansions taking place against the host object, the first is the ManagementEndpoint, then looking at the Credential object within the associated ManagementEndpoint


Fetch ALL Compute reservations with associated Cost Profiles & Provisioning Groups
Linqpad:
from host in Hosts
.Expand(“HostReservations/ProvisioningGroup”)
.Expand(“CostProfile”)
select host

Windows Workflow (Vb Expression):
Hosts.Expand(Function(host) host.HostReservations.First().ProvisioningGroup).Expand(Function(host) host.CostProfile).ToList() 

C# Expression
Hosts.Expand(host=> host.HostReservations.First().ProvisioningGroup).Expand(host => host.CostProfile).ToList() 

In this example because a host which has many reservations (1 to many relationship resulting in an array/collection result) the .First() method is required and then the subsequent object can be asked for, in this case the provisioning group.

Enjoy