Design time sharing the LightSwitch database with a worker process (part 1/2)

Introduction

When Integrating LightSwitch applications with other enterprise applications, a very frequent requirement will be to share, in design time, the data layer of the LightSwitch application with other application types (WCF services, console applications, …)

We presume that the other application types want to integrate via Entity Framework.

Another assumption is that the LightSwitch application is in the lead when it comes to the generation of the database scheme. So, what we want is that the other application types gets, in design time, easy access to the different domain types (customers, orders, …) as well as to the entity framework DbContext.

Basically, all this is at first glance pretty simple and nothing more than adding an Entity Framework model in the project type where you want the connection to the LightSwitch database. Nonetheless, we will run into some small inconveniences, for which I believe I have sort of solution.

In a next post we will focus on another technique for doing the same type of design time integration.

The Sample Solution

We could start with following visual studio solution structure:

 

image

We have a LightSwitch project (in folder LightSwitch) and a Worker process (in folder WorkerProcess), which wants to connect to the LightSwitch sql server database.

Obviously, in runtime that connection would be to the sql server database, whereas in design time it will connect to the localDb database.

The most straightforward approach would be to add an Entity framework model directly in the worker process project, but we want a better layer separation.

So, add two .Net 4.5 class libraries and call them App.Data and App.Domain.

 

image

 

The leval of layering we are envisioning is about separating the domain classes (Customers, Orders) and the machinery necessary for connecting entity framework to the database (the DbContext).

The reason why we ideally want to separate these is because there are scenarios where we want to give access other layers in our application either access to the domain classes or to the data layer. A typical example is a WPF application which will need client side (in the assembly containing the Xaml markup) access to the domain classes (we might want to bind a Customer domain class to the xaml based screen via the ViewModel. So, it’s quite convenient to add the domain assembly to the WPF assembly, but NOT the data layer assembly. A WPF application will typically connect via WCF layer to the server. The WCF layer will call in the end (often via business layer) the data layer.

adding an entity framework model to the App.Data project

Start with adding an Entity data model to the App.Data project and call it LightSwitchModel.edmx, this makes clear that the data are coming from the LightSwitch application:

image

Since LightSwitch keeps the database model always in sync with the localDb database, we want that the entity framework model is generated from the database:

image

 

Specify as server name: (localdb)\v11.0 (use a back slash!)

image

 

Find the database in your lightswitch project folder. It’s typically in the \bin\data folder of the LightSwitch project:

image

Specify “LightSwitchEntities” as the name of the connectionstring.

Select the tables you want to include:

image

and check “Pluralize or singularize generated object names.

Let’s test things so far.

In order to test it we have to provide some code in the console application where we call the data layer:

 

using App.Data;
using App.Domain;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WorkerWithDedicatedEFConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            using (LightSwitchEntities ctx = new LightSwitchEntities())
            {
                ctx.Customers.Add( new Customer{ LastName= "test by worker process", FirstName="test"});
                ctx.SaveChanges();
            }

        }
    }
}

 

This will only work if we provide also the correct connectionstring in the app.config of the console application. The easiest way to proceed is to copy the connection string from the app.project into the app.config of the console application:

<connectionStrings>
    <add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\v11.0;initial catalog=D:\myTFSWorkSpace\MyTestApp\MyLightSwitchApp\Bin\Data\ApplicationDatabase.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

 

F5 the console app, and the effect should be that a record is added to the customer table.

Ok, Let’s step back now a bit, and take a closer look to the connection string in the app.config. As you can see, the connectionstring refers to a file on disk:

initial catalog=D:\myTFSWorkSpace\MyTestApp\MyLightSwitchApp\Bin\Data\ApplicationDatabase.mdf

That’s a problem !

Why?  Well, If I check in my project in source control  (or simply zip it and send it to someone else), there is no guarantee that the project will start on someone else’s machine, because the path used in that connection string is absolute ! Obviously, one can always update the connection string but for me visual studio solutions should be F5-able.  So, let’s first fix this.

Make the connection string path independent

Wouldn’t it be much nicer if we could specify the connection string as :

|DataDirectory|\APPLICATIONDATABASE.MDF

For being able to do that we need to do a few tweaks. First we add a new class:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace App.Data
{
       public class LightSwitchDbContext : DbContext
    {
        public LightSwitchDbContext(string nameOrConnectionString):base(nameOrConnectionString)
        {
            //you could consider using a debug compile directive in such a way it's only called when the debug configuration is active.
            //in release mode you probably go to a real server (not localdb)

            string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
            string path = (System.IO.Path.GetDirectoryName(executable));
            string dbRoot = Path.Combine(Directory.GetParent(path).Parent.Parent.FullName, @"LightSwitchApp\Bin\Data");

            AppDomain.CurrentDomain.SetData("DataDirectory", dbRoot);

        }
    }
}

 

In fact, this class extends the normal DbContext with the ability to use the “DataDirectory” variable in the connection string. Such a variable can be specified as a kind of property on the AppDomain.

In order to use now this new LightSwitchContext class, we need to patch the …

Enitty framework generates automatically via a T4 template following boilerplate code:


 

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace App.Data
{
    using App.Domain;
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class LightSwitchEntities : DbContext
    {
        public LightSwitchEntities()
            : base("name=LightSwitchEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Customer> Customers { get; set; }
        public virtual DbSet<Order> Orders { get; set; }
    }
}

 

What we want now is that the class no longer inherits from dbContext but from LightSwitchDbContext. We can enforce this by replacing in LightSwitchModel.Context.tt DbContext with LightSwitchDbContext.

So try to locate in the tt file “DbContext” and replace it.

 

image

 

Great now we can specify our connection string as follows:

<add name="LightSwitchEntities" connectionString="metadata=res://*/LightSwitchModel.csdl|res://*/LightSwitchModel.ssdl|res://*/LightSwitchModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\v11.0;AttachDbFileName=|DataDirectory|\APPLICATIONDATABASE.MDF;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Separate the edmx file over the data and domain layer

Our second and last refinement is to separate the generated classes in the edmx file between a data and domain layer.

Step 1: close the solution and go to the file explorer and move following files to the app.domain folder:

domain

 

Step 2: reload the solution and make sure you have now following structure:

solutionstructure

 

Step 3: open LightSwitchModel.tt

Update the inputFile location :

const string inputFile =  @”..\\App.Data\\LightSwitchModel.edmx”;

Step 4: open LightSwitchModelContext.tt (in the app.data project) and add a using statement to App.Domain

using

 

 

 What if you change the LightSwitch database scheme?

What you want is that a change in the LightSwitch database scheme is reflected in the edmx. This can be accomplished by right clicking on the different .tt files in the app.data and app.domain class and run the custom tool:

customtool

Conclusion

Sharing the database connection with other project types like worker processes, WPF, WCF is basically  as simple as just adding an edmx file.  But applying the above tweaks can save you some headaches.