Development With A Dot

Blog on development in general, and specifically on .NET

  • New Features in SharePoint Designer 2013 Workflows

    SharePoint Designer 2013 workflows brought along new and quite welcome features. I will list my favorite ones.

    First, the ability to define stages (blocks of instructions) and to jump (yes, goto-style!) between them. This permits a better organization of our code and because of conditional jumps, even includes the ability to reuse code and to loop between stages.


    Next one is stage-level loops. We have two flavors: one where the loop count is predefined from an Integer variable and the other where a condition is used to determine its end.


    Then we have a new type of variables, Dictionary, and three associated actions: build a dictionary with values, get an item from a dictionary and count the number of items in it. Each Dictionary item can be of a different type, and it is even possible to have items of type Dictionary. A Dictionary can be indexed by its key or by position, which is very useful to use inside loops.


    It is now possible to start list and site workflows, but only SharePoint 2010 are supported. Workflows can be started synchronously, in which case, the originating workflow will wait for the result, or asynchronously, aka, fire and forget. Depending on the workflow selected, it may be necessary to specify values for its parameters.

    Also new is the ability to assign tasks and start approval processes.



    A perhaps not so used one is the capability to start a document translation process. The name is misleading, since it can also be used to translate list fields. The result translation is stored in another list.

    Finally, we have the ability to call an HTTP web service. This will mostly used to call REST-style web services, but nothing prevents us from calling SOAP, since we can build a SOAP request using the string utility actions that the Designer offers. We can specify both request contents and headers, and retrieve the result, headers and HTTP status code. The problem is, SharePoint Designer workflows can only process results coming as JSON, not XML, but there are a number of translation web services that can be used to turn XML into JSON.



    All in all, very useful additions! Smile


  • Looping Through List Items in SharePoint 2013 Designer Workflows

    SharePoint 2013 Designer workflows now has two new interesting options: the ability to call HTTP web services and the option to loop over some code a number of times. This, together with the new REST API, which supports querying lists and returning data in JSON, allows iterating through list items in a workflow, something that was not possible before.

    In order to demonstrate this, let’s create a new Site Workflow in SharePoint Designer, that will iterate through the Tasks list:


    Call it Process Tasks, for example, and make sure you select SharePoint 2013 as the platform type.

    In the workflow designer, let’s start by creating a new stage, call it Retrieve Tasks:


    In it, we add a new Set Workflow Variable action which creates a new String variable called url with the value “http://sp2013/_api/web/lists/getbytitle('Tasks')/items”. This uses the new REST API, and you can pass in additional options, such as for ordering by the DueDate field in descending order:

    http://sp2013/_api/web/lists/getbytitle('Tasks')/items?$orderby=DueDate desc

    or filtering:

    http://sp2013/_api/web/lists/getbytitle('Tasks')/items?$filter=DueDate gt DateTime’2014-07-31T00:00:00’

    Next, we add a Dictionary variable (Build a Dictionary action), call it requestHeaders, and initialize it as this:


    Both “Accept” and “Content-Type” entries are of the String type and they both contain the value “application/json;odata=verbose”, SharePoint REST API understands this and sets the response content type appropriately as JSON. If we don’t pass these values, the output would come as XML.

    Following, add an Call an HTTP Web Service action and set its properties. The request will be the url variable:


    Response content will go to a new variable called responseContent:


    Response headers will go to a new variable called responseHeaders:


    And the same goes for the response code (variable responseCode):


    Then we set the request headers to be the requestHeaders variable we created just now, by clicking on the properties for the Call an HTTP Web Service action:


    Now, create a new stage, call it Process Tasks, and, at the end of the initial stage, add a Go to Process Tasks action.

    On the Process Tasks stage, add a Get an Item from a Dictionary action, set the item as d/results, the source variable reponseContent and the output to a new variable of type Dictionary called list. Then count items from this list variable using a Count Items in Dictionary action and store the result in a new Integer variable called count. This variable will tell us how many times we have to loop. Finally, create a new Integer variable called index and set it to 0 (Set Workflow Variable), this will be the loop index.

    Next, add a loop (Loop n Times), call it Loop Task Items, and set the loop variable to count. Inside the loop, get value d/results([%Variable: index%]) using a Get an Item from a Dictionary action from responseContent and store it in a new Dictionary variable called item. Get some fields (Get an Item from a Dictionary) from the item variable, such as Title and DueDate  (mind you, these will be task item fields) and store them in appropriate variables and do whatever you want with them, like logging its contents (Log). Time to increment the loop counter: add a Do Calculation action and in it increment the index variable into a new Integer variable called indexPlusOne. Then set the index variable to be the indexPlusOne (Set Workflow Variable). Finally, exit the loop and set the workflow status (Set Workflow Status action) to Finished. At the end of the stage, select Go to End of Workflow.

    That’s it. Your workflow should look like this:


    The new functionality makes SharePoint Designer workflows much more powerful than before. I will continue to talk about it in the following posts.


  • NHibernate Pitfalls: XML Mappings

    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

    If you are still using XML mappings – .hbm.xml files –, there’s nothing wrong with that, but you may run into problems.

    First, you will need to add these files as embedded resources in your project, if you are calling Configuration.AddAssembly(), Configuration.AddClass() or you are specifying the name of the assembly in the .config file like this:

       1: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
       2:     <session-factory>
       3:         <property name="connection.driver_class">NHibernate.Driver.Sql2008ClientDriver</property>
       4:         <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
       5:         <property name="connection.connection_string_name">MyConnection</property>
       6:         <mapping assembly="MyAssembly.MyModel" />
       7:     </session-factory>
       8: </hibernate-configuration>

    These methods will look for .hbm.xml files as embedded resources in that assembly:


    Alternatively, you can have the files on the filesystem, but you will have to call Configuration.AddDirectory(), Configuration.AddFile() or Configuration.AddInputStream(), passing the appropriate parameters.

    In either case, the name of each .hbm.xml must end with .hbm.xml (of course!) and must be composed of the name of the associated class including its namespace.

    If you don’t do this, NHibernate will not find your mappings, which will result in runtime errors.


  • NHibernate Pitfalls: Loading Foreign Key Properties

    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

    When saving a new entity that has references to other entities (one to one, many to one), one has two options for setting their values:

    • Load each of these references by calling ISession.Get and passing the foreign key;
    • Load a proxy instead, by calling ISession.Load with the foreign key.

    So, what is the difference? Well, ISession.Get goes to the database and tries to retrieve the record with the given key, returning null if no record is found. ISession.Load, on the other hand, just returns a proxy to that record, without going to the database. This turns out to be a better option, because we really don’t need to retrieve the record – and all of its non-lazy properties and collections -, we just need its key.

    An example:

       1: //going to the database
       2: OrderDetail od = new OrderDetail();
       3: od.Product = session.Get<Product>(1);    //a product is retrieved from the database
       4: od.Order = session.Get<Order>(2);        //an order is retrieved from the database
       6: session.Save(od);
       8: //creating in-memory proxies
       9: OrderDetail od = new OrderDetail();
      10: od.Product = session.Load<Product>(1);    //a proxy to a product is created
      11: od.Order = session.Load<Order>(2);        //a proxy to an order is created
      13: session.Save(od);

    So, if you just need to set a foreign key, use ISession.Load instead of ISession.Get.


  • NHibernate Pitfalls: Fetch and Paging

    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

    NHibernate allows you to force loading additional references (many to one, one to one) or collections (one to many, many to many) in a query. You must know, however, that this is incompatible with paging. It’s easy to see why.

    Let’s say you want to get 5 products starting on the fifth, you can issue the following LINQ query:

       1: session.Query<Product>().Take(5).Skip(5).ToList();

    Will product this SQL in SQL Server:

       1: SELECT
       2:     TOP (@p0) product1_4_,
       3:     name4_,
       4:     price4_
       5: FROM
       6:     (select
       7:         product0_.product_id as product1_4_,
       8: as name4_,
       9:         product0_.price as price4_,        
      10:         ROW_NUMBER() OVER(
      11:     ORDER BY
      12:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      13:     from
      14:         product product0_) as query
      15:     WHERE
      16:         query.__hibernate_sort_row > @p1
      17:     ORDER BY

    If, however, you wanted to bring as well the associated order details, you might be tempted to try this:

       1: session.Query<Product>().Fetch(x => x.OrderDetails).Take(5).Skip(5).ToList();

    Which, in turn, will produce this SQL:

       1: SELECT
       2:     TOP (@p0) product1_4_0_,
       3:     order1_3_1_,
       4:     name4_0_,
       5:     price4_0_,
       6:     order2_3_1_,
       7:     product3_3_1_,
       8:     quantity3_1_,
       9:     product3_0__,
      10:     order1_0__
      11: FROM
      12:     (select
      13:         product0_.product_id as product1_4_0_,
      14:         orderdetai1_.order_detail_id as order1_3_1_,
      15: as name4_0_,
      16:         product0_.price as price4_0_,
      17:         orderdetai1_.order_id as order2_3_1_,
      18:         orderdetai1_.product_id as product3_3_1_,
      19:         orderdetai1_.quantity as quantity3_1_,
      20:         orderdetai1_.product_id as product3_0__,
      21:         orderdetai1_.order_detail_id as order1_0__,
      22:         ROW_NUMBER() OVER(
      23:     ORDER BY
      24:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      25:     from
      26:         product product0_
      27:     left outer join
      28:         order_detail orderdetai1_
      29:             on product0_.product_id=orderdetai1_.product_id
      30:         ) as query
      31: WHERE
      32:     query.__hibernate_sort_row > @p1
      33: ORDER BY
      34:     query.__hibernate_sort_row;

    However, because of the JOIN, what happens is that, if your products have more than one order details, you will get several records – one per order detail – per product, which means that pagination will be broken.

    There is an workaround, which forces you to write your LINQ query in another way:

       1: session.Query<OrderDetail>().Where(x => session.Query<Product>().Select(y => y.ProductId).Take(5).Skip(5).Contains(x.Product.ProductId)).Select(x => x.Product).ToList()

    Or, using HQL:

       1: session.CreateQuery("select od.Product from OrderDetail od where od.Product.ProductId in (select p.ProductId from Product p skip 5 take 5)").List<Product>();

    The generated SQL will then be:

       1: select
       2:     product1_.product_id as product1_4_,
       3: as name4_,
       4:     product1_.price as price4_
       5: from
       6:     order_detail orderdetai0_
       7: left outer join
       8:     product product1_
       9:         on orderdetai0_.product_id=product1_.product_id
      10: where
      11:     orderdetai0_.product_id in (
      12:         SELECT
      13:             TOP (@p0) product_id
      14:         FROM
      15:             (select
      16:                 product2_.product_id,
      17:                 ROW_NUMBER() OVER(
      18:             ORDER BY
      19:                 CURRENT_TIMESTAMP) as __hibernate_sort_row
      20:             from
      21:                 product product2_) as query
      22:         WHERE
      23:             query.__hibernate_sort_row > @p1
      24:         ORDER BY
      25:             query.__hibernate_sort_row);

    Which will get you what you want: for 5 products, all of their order details.


  • Getting the Topmost Hierarchical Parent in T-SQL


    It is normal in databases to have hierarchical tables, that is, tables that are related with themselves, forming a parent-child relation. For example, consider this:


    The parent_id column points to the parent record, which, in some cases, will not exist.

    So, imagine we have a number of records, such as:

       1: INSERT INTO dbo.list (id, parent_id) VALUES (1, NULL)
       2: INSERT INTO dbo.list (id, parent_id) VALUES (2, 1)
       3: INSERT INTO dbo.list (id, parent_id) VALUES (3, 2)
       4: INSERT INTO dbo.list (id, parent_id) VALUES (4, 3)

    How can we find the id of the topmost parent? In this case, it will always be 1, of course.

    In SQL Server, we have two options:

    1. A Common Table Expression (CTE);
    2. A recursive function.

    Let’s see how to implement each.

    Common Table Expression Approach

    We need to write a CTE that starts with some record and goes all the way up until it finds the parent. Let’s wrap it in a nice scalar function:

       1: CREATE FUNCTION dbo.GetTopmostParentCTE
       2: (
       3:     @id INT
       4: ) 
       5: RETURNS INT
       6: AS
       7:     BEGIN
       8:         DECLARE @parentId INT
      10:         ;WITH cte AS 
      11:         (
      12:             SELECT, a.parent_id
      13:             FROM dbo.list AS a 
      14:             WHERE = @id
      15:             UNION ALL
      16:             SELECT, b.parent_id 
      17:             FROM dbo.list AS b
      18:             INNER JOIN cte AS c
      19:             ON c.parent_id =
      20:         )
      22:         SELECT TOP 1 @parentId = id
      23:         FROM cte
      24:         WHERE parent_id IS NULL
      26:         RETURN @parentid
      27:     END
      28: GO

    I won’t explain here how CTEs work, they have been around for quite some time, and there are several posts how there for that.

    Recursive Function Approach

    The other approach is using a recursive function. The gotcha here is that when we create a function, it is compiled, and if it has a reference to itself – which doesn’t exist first – it will fail. Therefore, we need to first create a dummy function and then change it to do what we want:

       1: CREATE FUNCTION dbo.GetTopmostParent
       2: (
       3:     @id INT
       4: )
       5: RETURNS INT
       6: AS
       7: BEGIN
       8:     RETURN
       9:     (
      10:         SELECT 0
      11:     )
      12: END
      13: GO
      15: ALTER FUNCTION dbo.GetTopmostParent
      16: (
      17:     @id INT
      18: )
      19: RETURNS INT
      20: AS
      21: BEGIN
      22:     RETURN
      23:     (
      24:         SELECT CASE WHEN parent_id IS NULL THEN id ELSE dbo.GetTopmostParent(parent_id) END
      25:         FROM dbo.list
      26:         WHERE id = @id
      27:     )
      28: END
      29: GO


    You can get results from the two functions by running the following T-SQL queries:

       1: SELECT dbo.GetTopmostParent(4)
       2: SELECT dbo.GetTopmostParentCTE(4)

    Interesting, both execution plans are exactly the same:


    I can’t really recommend one over the other, since from my tests, both took the same amount of time (you will need far more records than the ones from my sample to tell that).

    So, any thoughts from database gurus out there?


  • Entity Framework Pitfalls – Deleting Orphans

    The idea for this post came from Jimmy Bogard, of AutoMapper fame.

    Entity Framework does not really have the notion of parents and children, only of relations. Some of these relations can be mandatory, others can be optional. It is possible to specify cascade delete rules, so that when an entity is removed from the database, all entities that depend on it are also removed. However, what happens if we remove a related (child) entity from it’s parent’s children collection? Consider this code:

       1: using (var ctx = new OrdersContext())
       2: {
       3:     var customer = ctx.Customers.First();
       4:     customer.Orders.Remove(customer.Orders.First());
       6:     ctx.SaveChanges();
       7: }

    What we are doing here is removing the association between an Order and its Customer. Because an Order cannot exist without a Customer, we are turning it into an orphan. It is basically the same as:

       1: var order = ctx.Orders.First();
       2: order.Customer = null;

    Do note that we are not deleting anything, just removing the related (parent) reference. In this case, Entity Framework will, of course, fail, complaining about the missing required relation.

    Entity Framework has really no workaround for this – at least, not that I know of – so you have to delete the child entities explicitly:

       1: var customer = ctx.Customers.First();
       2: var order = customer.Orders.First();
       4: customer.Orders.Remove(order);
       5: ctx.Orders.Remove(order);
       7: ctx.SaveChanges();

    As a side note, NHibernate handles this very well, by setting the cascade option to delete-orphan.