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

http://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 = "http://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 = "http://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 http://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 http://msftdbprodsamples.codeplex.com/releases/view/94486.

You can read more about the QueryFeed workflow in the attached Introducing the OData QueryFeed Activity document.