Using SQL Server View in Visual Studio LightSwitch 2011

There are two issue resulting from SQL Server Views in VS LightSwitch 2011 inferred keys;

1. Columns appearing as key that you do not want  to be inferred as keys.
2. Forcing columns to be keys that are not inferred as keys.

You will see the results of these issues with SQL Server Views in different ways for me there were two forms:

1. Strange Paging on child grids, you may see less row than are set on the pager i.e. page size was set to 10 but on pages you see 5, 6, 7 rows or more.
2. Single item per page.
3. Single item returned when there should be many.

Removing Inferred Columns

Figure 1 – View from Northwind

As you can see in Figure 1 view from the Northwind database as imported in to LightSwitch you can see there are two columns that may cause you problems.

To take these out of the inferred key on this view we can apply a CAST see below:

CAST(dbo.Products.Discontinued AS BIT)
CAST(dbo.Categories.CategoryName AS NVARCHAR(15))

Figure 2 – Adding CAST to Column

So when we “Update Datasource” we get what we see in Figure 3

Figure 3 – View after “Update Datasource”

Now we have remove the two offending columns from the inferred keys.

Forcing Columns to be Inferred

Here we need to do the opposite and the function we use for that is ISNULL (Transact-SQL) for this sample we will add the Supplier and Customer ID’s to the inferred keys, here the replacement value I am using is 0 because the two columns are INT but if a column was NVARCHAR(50) then the cast would be ISNULL(TextColumn, N‘’)

ISNULL(dbo.Products.SupplierID, 0)
ISNULL(dbo.Products.CategoryID, 0)

In Figure 4 we can see the result;

Figure 4 – After Including the Supplier and Customer IDs in the Inferred Keys

Conclusion

This way you can for the keys you want to used in your view, there will be a small hit in the queries for using either of these techniques in your queries but hopefully not too much of a hit.

Note: These view may or may not have issues, but I have had various problems in LightSwitch relating both issues and used one the two techniques to override the default behaviour.

Note: Also I did not come up with these methods they are scattered around the LightSwitch Forums but for my own use and easy reference I have added them here.

Love Telerik Open Access ORM

Just watched the Q1 2012 “What’s New in Data Tools – OpenAccess ORM” and they now have a a Dynamic Data Wizard see screen show of the slide.

Find it here Telrik OpenAccess ORM, Note this works with Most Databases and all Free databases

This is cool!

Basic Auditing for Dynamic Data with Entity Framework 4.x

This is my first article of 2012 and I thought I had published an article on this previously but apparently not so I will now rectify that oversight.

The first this we need out audit fields, these are added to every entity we need to audit, next we need an Interface to allow us to fine entities with Audit fields.

Figure 1 – Audit Fields

public interface IAuditable
{
    String CreatedByUserID { get; set; }
    String CreatedDateTime { get; set; }
    String UpdatedByUserID { get; set; }
    String UpdatedDateTime { get; set; }
}

Listing 1 – IAuditable interface

We then need to add this to each entity that will be audited, this is just applied to you metadata classes on the Partial Class NOT the buddy class.

[MetadataType(typeof(AppAlterMetadata))]
public partial class AppAlter : IAuditable
{
    internal class AppAlterMetadata
    {
        public Object ID { get; set; }

        // other field deleted for brevety

        // auditing fields
        public Object CreatedByUserID { get; set; }
        public Object CreatedDateTime { get; set; }
        public Object UpdatedByUserID { get; set; }
        public Object UpdatedDateTime { get; set; }
    }
}

Listing 2 – the interface applied to each entity that requires auditing

Now for the code that does the auditing automatically,

public partial class MyEntities
{
    /// <summary>
    /// Called when [context created].
    /// </summary>
    partial void OnContextCreated()
    {
        // Register the handler for the SavingChanges event. 
        this.SavingChanges += new EventHandler(context_SavingChanges);
    }

    /// <summary>
    /// Handles the SavingChanges event of the context control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    private static void context_SavingChanges(object sender, EventArgs e)
    {
        var objects = ((ObjectContext)sender).ObjectStateManager;

        // handle auditing
        AuditingHelperUtility.ProcessAuditFields(objects.GetObjectStateEntries(EntityState.Added));
        AuditingHelperUtility.ProcessAuditFields(objects.GetObjectStateEntries(EntityState.Modified), InsertMode: false);
    }

    /// <summary>
    /// Auditing helper utility class
    /// </summary>
    internal static class AuditingHelperUtility
    {
        internal static void ProcessAuditFields(IEnumerable<Object> list, bool InsertMode = true)
        {
            foreach (ObjectStateEntry item in list)
            {
                var appUserID = GetUserId();
                // deal with insert and update entities
                var auditEntity = item.Entity as IAuditable;
                if (auditEntity != null)
                {

                    if (InsertMode)
                    {
                        auditEntity.CreatedByUserID = appUserID;
                        auditEntity.CreatedDateTime = DateTime.Now;
                    }

                    auditEntity.UpdatedByUserID = appUserID;
                    auditEntity.UpdatedDateTime = DateTime.Now;
                }

            }
        }
    }

    public static String GetUserId()
    {
        return System.Web.HttpContext.Current.User.Identity.Name;
    }
}

Listing 3 – the Audit code

Lets break this down into three sections

Section 1

Here we wire-up the SavingChanges handler in the OnContextCreated() partial method to do this we first need to create a partial class from out entities for you look in the EDMX code behind file you will see something like this;

Figure 2 – Entities classes

so we add a new class to the the project, make sure it has the same namespace as the EDMX code behind file (this is pretty much the same as for out metadata classes) and then we add the partial class same as the MyEntities (this will be the name you gave it when creating but it is there in the code behind you can’t miss it) class, see Listing 3.

The method is wired up with this line of code:

this.SavingChanges += new EventHandler(context_SavingChanges);

Section 2

Now in the context_SavingChanges method we simply get the ObjectStateManager  which has all the objects that are being added, updated and deleted, here we are only interested in the Added and Modified items. All we do is call our helper with each collection of objects.

Section 3

Looking at Listing 3 you will see the AuditingHelperUtility and it’s ProcessAuditFields method, here we first of all cast the each entity to the IAuditable interface and check for null if it isn't then set the appropriate properties and exit.

Finally

This can be expanded to cover many different requirements, I have maintained a separate audit table using this method with the addition of a little reflection.