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
January 21, 2013 at 4:02 pm
Don't forget the Expansion helper for .Expand expressions is not out of the box with .NET data services clients so you can't use it in linqpad without adding reference to vcac library DynamicOps.Repository.dll
LikeLike
January 22, 2013 at 8:25 am
Good point Phil. I didn't specify that some of this was DynamicOps secret sauce with regards to C#/VB Expressions where the Expand Helper is used to provide hard types instead of strings which really saves any errors occurring. When querying the vCAC database in these languages DynamicOps.Repository.dll is more than likely loaded into a workflow project.
These VB Expressions are common examples that can be used within the Design Center utility as is.
The linqpad queries work without the need for modification or adding any references.
LikeLike
March 14, 2013 at 4:08 pm
Very nice.
I am kind of new to this and have a couple of simple questions.
Is there a reference guide for writing windows workflow vb expression?
Given a VM name or ID and I want to find the HostID of the host where the VM is on. How to write a VB expression to achieve that?
LikeLike
March 14, 2013 at 5:02 pm
C# expressions will be available in DotNet 4.5 which will make things easier & will/should be available in the next release of VCAC.
http://msdn.microsoft.com/en-us/magazine/hh781025.aspx
Its VB so:
http://msdn.microsoft.com/en-us/library/sh9ywfdk(v=vs.80).aspx
http://msdn.microsoft.com/en-us/vstudio/bb688088.aspx
Actually a HostId is easy as it is a property of the VirtualMachine table, if you want to get to the Host object to see its name too you can try this:
c#
machine = VirtualMachines.Expand(v=> v.Host).Where(v=> v.VirtualMachineId == VirtualMachineId && 1==1).FirstOrDefault()
vb
machine = VirtualMachines.Expand(Function(v) v.Host).Where(Function(v) v.VirtualMachineId = VirtualMachineId And 1=1).FirstOrDefault()
Then you can get a value from machine.Host.HostName
LikeLike
June 10, 2013 at 3:28 pm
Hi Tom,
quick question, how do I get a list of all the Users as a linq statement in C#? I thought it might be something like this, but it's not working.
User users;
users = (
from b in mgmtCtx.Users.Expand(user => users.UserName)
select b).ToList();
thanks,
George
LikeLike
June 10, 2013 at 3:32 pm
I think I may have gotten it.
List users;
users = (
from b in mgmtCtx.Users
select b).ToList();
LikeLike
July 23, 2013 at 2:45 pm
Hi Tom, Question for you. Is it possible to add a user to a group with C#?
Thanks,
George
LikeLike
March 31, 2014 at 8:52 am
I like this blog because it includes various conceptual points that help us.
LikeLike
July 2, 2014 at 4:18 pm
Any way we can pull a list of provisioning groups an user belongs to ?
LikeLike