Introducing the OData QueryFeed workflow activity
As a continuation of my previous blog post, I created an OData QueryFeed workflow activity sample. The sample represents an end to end data movement application utilizing a number of technologies including
- Using LINQ to project OData entity XML to entity classes.
- Creating custom workflow activities.
- Creating custom workflow designers containing OData schema aware expression editors and WorkflowItemsPresenters. A WorkflowItemsPresenter is used to drop OData Filter activities that format a fully qualified OData filter parameter.
- WPF ComboBox items hosting a button and checkbox.
- Hosting the Windows Workflow designer within Microsoft Excel 2010.
- Running a workflow from within a Microsoft Excel 2010 and Microsoft Word 2010 Addin.
- Using a workflow Tracking Participant to subscribe to activity states.
- Creating ModelItem and Office extension methods.
- Open XML 2.0 that renders OData entity properties as a Word 2010 table.
By providing feedback on this blog, or sending me an email, you can help define any part of the sample. My email address is derrickv@microsoft.com.
Source Code
https://msftdbprodsamples.codeplex.com/releases/view/94486.
User Story
The sample addresses the following user story:
As a developer, I want to create an OData feed activity so that an IT Analyst can consume any AdventureWorks OData feed within a business workflow.
The OData QueryFeed sample activity shows how to create a workflow activity that consumes an OData resource, and renders entity properties in a Microsoft Excel 2010 worksheet or Microsoft Word 2010 document. Using the sample QueryFeed activity, you can consume any OData resource. The sample activity uses LINQ to project OData metadata into activity designer expression items. By setting activity expressions, a fully qualified OData query string is constructed consisting of Resource, Filter, OrderBy, and Select parameters. Executing the activity returns OData entity properties.
Resource
Query string part
/CompanySales
EntitySets LINQ for Resource Items
public IEnumerable<EntitySet> EntitySets
{
get
{
//Arrange: A url to a WCF 5.0 service is given as InArgument_Url
XNamespace xmlns = "https://schemas.microsoft.com/ado/2008/09/edm";
//Act: A LINQ to XML query is constructed that projects EntitySet XML to an EntitySet class
IEnumerable<EntitySet> entitySets = from x in metadata
.Descendants(xmlns.GetName("EntitySet"))
select new EntitySet
{
Name = x.Attributes("Name").Single().Value,
Namespace = x.Attributes("EntityType").Single().Value.Split(new char[] { '.' })[0],
EntityType = x.Attributes("EntityType").Single().Value.Split(new char[] { '.' })[1]
};
//Return:
return entitySets;
}
}
Filter
Query string part
$filter=OrderYear ne 2006 and Sales lt 10000
EntitySetSchema LINQ for Filter Items
public IEnumerable<EntityPropertySchema> EntitySetSchema(string resource)
{
IEnumerable<EntityPropertySchema> entitySchema = null;
if (resource != string.Empty)
{
XNamespace xmlns = "https://schemas.microsoft.com/ado/2008/09/edm";
entitySchema = from p in metadata
.Descendants(xmlns.GetName("EntityType")).Descendants(xmlns.GetName("Property"))
where p.Parent.Attribute("Name").Value == (from e in this.EntitySets where e.Name == resource select e.EntityType).Single<string>()
select new EntityPropertySchema
{
Parent = p.Parent,
Name = p.Attribute("Name").Value,
Type = p.Attribute("Type").Value,
MaxLength = p.Attribute("MaxLength") == null ? "NaN" : p.Attribute("MaxLength").Value
};
}
return entitySchema;
}
OrderBy
Query string part
$orderby=ProductSubCategory asc,Sales desc
EntitySetSchema LINQ for Filter Items
See Filter
Select
Query string part
$select=ProductSubCategory,Sales
EntitySetSchema LINQ for Filter Items
See Filter
Rendering QueryFeed TableParts
The example QueryFeed workflow renders entity properties in any client that supports .NET Framework 4.0. The sample source code at https://msftdbprodsamples.codeplex.com/releases/edit/94486 shows rendering an EntityProperties TablePart in Microsoft Excel 2010 and Microsoft Word 2010. The sample source code shows how to use an extension method to extend a Microsoft Excel or Microsoft Word range.
Excel InsertEntityTable Extension Method
public static void InsertEntityTable(this Range activeCell, IEnumerable<IEnumerable<EntityProperty>> entityProperties, string styleName)
{
int currentColumn = 0;
int currentRow = 1;
Range range;
List<string> propertyNames = (from item in entityProperties select item).First<IEnumerable<EntityProperty>>().Select(n => n.Name).ToList<string>();
int columnCount = propertyNames.Count();
int rowCount = entityProperties.Count();
Globals.ThisAddIn.Application.ScreenUpdating = false;
//Data Columns
foreach (string name in propertyNames)
{
range = activeCell.get_Offset(1, currentColumn);
range.FormulaR1C1 = name;
currentColumn++;
}
currentColumn = 0;
//Data Values
foreach (IEnumerable<EntityProperty> items in entityProperties)
{
//row = new TableRow();
currentRow++;
foreach (EntityProperty item in items)
{
range = activeCell.get_Offset(currentRow, currentColumn);
range.FormulaR1C1 = item.Value;
currentColumn++;
}
currentColumn = 0;
}
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range styleRange = activeCell.Range[activeSheet.Cells[2, 1], activeSheet.Cells[rowCount + 2, columnCount]];
string listObjectName = String.Format("Table{0}", activeSheet.ListObjects.Count);
try
{
activeSheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, styleRange, Type.Missing, XlYesNoGuess.xlYes).Name = listObjectName;
activeSheet.ListObjects[listObjectName].TableStyle = styleName;
}
catch
{
//Handle exception in a production application
}
styleRange.Columns.AutoFit();
Globals.ThisAddIn.Application.ScreenUpdating = true;
}
My next post will show how to edit a QueryFeed workflow using a hosted Windows Workflow designer. A custom designer is included in the source code at https://msftdbprodsamples.codeplex.com/releases/view/94486.
You can read more about the QueryFeed workflow in the attached Introducing the OData QueryFeed Activity document.