In prior posts I’ve covered the LightSwitch presentation tier and the logic tier. The presentation tier is primarily responsible for human interaction with the application using data from a LightSwitch logic tier. The logic tier comprises one or more LightSwitch data services whose primary job is to process client requests to read and write data from external data stores. This post explores how the logic tier access data and what the supported data storage services are.

The following diagram shows the relevant pieces of each tier that participate in data access.

Figure 1: LightSwitch Tiers

In Visual Studio LightSwitch, when you define a data source by creating a new table or connecting to an external data source, LightSwitch creates a corresponding data service and configures its data provider. For application-defined data, LightSwitch creates a special data source called “ApplicationData” (sometimes referred to as the intrinsic data source). In addition to creating a data service for the intrinsic data source, LightSwitch also creates and publishes the SQL database.

Figure 2 below shows a LightSwitch application with two data sources: the intrinsic “ApplicationData” and an attached “NorthwindData”. LightSwitch will create the corresponding “ApplicationDataService” and “NorthwindDataService” and will publish the database for the intrinsic database.

Figure 2: Data Sources in Project Explorer

Each data service has a data provider that corresponds to the kind of data storage service. LightSwitch supplies the data provider for Microsoft SQL Server and for Microsoft SharePoint, but others can be plugged in to support other data storage services. The following table summarizes the supported data providers and data storage services in LightSwitch 1.0.

Table 1: Supported Data Access Providers

‡ Provider specific.

So let’s begin our tour of data access and storage in LightSwitch. We’ll cover the following areas.

• LightSwitch Data Types
• Using Microsoft SQL Server and SQL Azure
• Using Microsoft SharePoint
• Using a WCF RIA DomainService as a Data Adapter
• Using an Alternate Entity Framework Provider

LightSwitch Data Types

Before we look at the various data providers that you can use with LightSwitch, it will help to understand the data types that LightSwitch supports. In the sections following, we will see how LightSwitch maps data types from other data source and data access providers to the built-in LightSwitch data types.

Table 2 below lists the simple data types that LightSwitch supports. The data types shown in italic fonts (Date, EmailAddress, Image, PhoneNumber) are referred to as semantic types. These do not represent a distinct value type, but provide specific formatting, visualization and validation for an existing simple data type. The set of simple data types is fixed but the set of semantic types is open for extensibility.

Table 2: LightSwitch Simple Data Types

Nullable Data Types

Each of the LightSwitch simple and semantic data types has a corresponding nullable data type. The data type is represented in the LightSwitch modeling language as datatype?. In the design experience, the q-mark notation is not displayed. Instead a “Required” property indicates a non-nullable value. In code, LightSwitch uses the corresponding nullable VB and C# data types, for example "Integer?" or "int?". The one exception is that both required and non-required string values are represented as the CLR String type. If a string is required, LightSwitch validates that it is non-null and non-empty.

Using Microsoft SQL Server and SQL Azure

This section applies to data accessed from SQL Server, SQL Server Express, or SQL Azure, including the intrinsic “ApplicationData” database created for a LightSwitch application. When you publish a LightSwitch application, you specify the connection string that will be used to access data in SQL Server or SQL Azure. Your production SQL connection string should have the following properties set. (See ConnectionString Property for more information.) 

Unsupported Features

LightSwitch does not support accessing SQL stored procedures. The LightSwitch designer ignores all other database objects that may be configured such as triggers, UDFs or SQL CLR. (It is possible to create a custom data adapter that accesses SQL stored procedures. This is covered below in “Using a WCF RIA DomainService as a Data Adapter”.)

LightSwitch does not support certain SQL data types as noted in the table below. When attaching to an external data source, LightSwitch omits columns with unsupported data types.

SQL Data Type Mapping

When you attach to an existing SQL Server or SQL Azure, LightSwitch maps the SQL column types to LightSwitch data types according to the following table. A non-nullable SQL type is mapped to a LightSwitch “Required” data type. LightSwitch also imports the length, precision and scale of certain data types.

Table 3: SQL Data Type Mapping

SQL Data Provider

The LightSwitch logic tier uses the Entity Framework data access provider for SQL Server. In the data service implementation, LightSwitch uses an Entity Framework ObjectContext to move entity instances to and from SQL Server. The LightSwitch developer doesn’t see the Entity Framework objects or API directly. He writes the logic tier business logic in terms of the LightSwitch Entity and Data Workspace API—without concern for the underlying data access mechanism.

LightSwitch supports transactions over SQL Client. The default isolation level for query operations is IsolationLevel.ReadCommitted. The default isolation level for updates is IsolationLevel.RepeatableRead. LightSwitch doesn’t enlist in a distributed transaction by default. You can control that behavior by creating an ambient transaction scope; if available, LightSwitch will compose with it. For more details, see Transaction Management under the Logic Tier architecture post.

Generating the Intrinsic SQL Database

As we’ve noted above, a LightSwitch application has a default application database called “Application Data“. This database provides the data storage for entity types defined by your LightSwitch application – as opposed to entity types that are imported from an external storage service.

At design-time, LightSwitch uses a local SQL Server Express database on the developer’s machine for the intrinsic database. In production, LightSwitch uses Microsoft SQL Server Express, Microsoft SQL Server or Microsoft SQL Azure for the application database. Runtime data access is the same as for any other attached SQL Server or SQL Azure database, as described above.

LightSwitch deploys the intrinsic database schema when you publish the application. It will also attempt to upgrade an existing database schema when you upgrade the application.

Note: LightSwitch does not deploy design-time or test data to production. It just deploys the schema. If you have requirements to publish data along with your application, you’ll need to do that as a first-time install or first-time run step.

SQL Schema Generation

LightSwitch maintains a model of the application data entities and relationships. From this model it generates a database storage schema that can be used to create tables and relationships in SQL Server or SQL Azure.

When you use the “Create Table” command in the LightSwitch designer, you are actually defining an entity type and a corresponding entity set. From the entity set, LightSwitch infers the database table and columns. The column types are mapped from the entity properties according to the following table.

Table 4: Generated SQL Column Types

The following LightSwitch data types are not supported when defining entity properties on the intrinsic database: Byte, Guid, SByte, Single, and TimeSpan. These were omitted to keep the design experience simple. They are supported, however, when attaching to an external database.

A LightSwitch “Required” property translates into a SQL “NOT NULL” column.

The LightSwitch designer handles keys and foreign keys for entity relationships. For every entity type, it creates an auto-increment primary key property named “Id” of type Int32. LightSwitch also automatically creates hidden foreign-key fields for entity properties that reference [1] entity or [0..1] entities. These are generated as SQL columns with an appropriate foreign-key constraint.

In general, there is full fidelity in converting data between the LightSwitch data types and the SQL column types. The one exception is DateTime. LightSwitch chose to generate DateTime properties as SQL datetime instead of as datetime2 so that our generated SQL would be compatible with SQL Server 2005. This means that the range and precision of date/time values that LightSwitch handles (via the System.DateTime type) is greater than what the SQL column can store. See SQL date and time types for more details. This is only a problem if your application needs to deal with dates prior to January 1, 1753, which isn’t typical for most business applications.

Schema Versioning

After you’ve built and deployed your Application Data for the first time, you may need to make updates to the application and to its data schema. LightSwitch allows you to deploy over the top of an existing Application Database – to a degree. At publish time LightSwitch will read the schema of the existing published database and attempt to deploy just the differences, for example adding tables, relationships, and adding or altering columns.

There are a number of changes that LightSwitch will not deploy because the changes could result in data loss. Here are some of the breaking changes that could be made in the LightSwitch Entity designer that could result in being unable to deploy the schema updates.

• Renaming an entity property
• Changing an entity property’s data type to something that is incompatible
• Rearranging the order of entity properties
• Adding a required property
• Adding a 1-many relationship
• Adding a 1-0..1 relationship
• Changing the multiplicity of a relationship (however changing from 1-many to 0..1-many is allowed)
• Adding a unique index

 LightSwitch doesn’t prevent you from making a breaking change during iterative development. In some cases you may be warned that a change may cause data loss for your design-time test data. If you don’t mind losing your test data, you can accept the warning and make the change. But beware that if you’ve already deployed the data schema once, such a change may prevent you from successfully deploying a schema update because the scripts that LightSwitch uses to update the data schema will fail and roll-back if the potential for data loss is detected.

There are also a certain schema changes that may cause data loss but are intentionally allowed. These include:

• Deleting an entity property. The column will be dropped and any existing data will be lost. 
• Deleting an entity. Any foreign-key constraints will be dropped, the table will be dropped, and any existing data will be lost.
• Deleting a relationship. Any existing foreign-keys and foreign-key constraints will be removed. There is the potential for data loss.

The Membership Database

If you use Windows Authentication or Form Authentication in your application, LightSwitch will also deploy the necessary SQL tables to store membership information for users and roles. These tables are included with the intrinsic database. Internally, LightSwitch uses the ASP.NET SQL Membership provider which requires these tables to be present.

• dbo.aspnet_Applications
• dbo.aspnet_Membership
• dbo.aspnet_Profile
• dbo.aspnet_Roles
• dbo.aspnet_SchemaVersions
• dbo.aspnet_Users
• dbo.aspnet_UsersInRoles

In addition to Users and Roles, LightSwitch applications have the concept of Permissions. Permissions are defined statically per-application in the LightSwitch application model (LSML). LightSwitch associates Permissions with Roles. A Role acts as an administrative grouping for Permissions. LightSwitch creates one additional table called RolePermissions to track this association.

• dbo.RolePermissions

Post deployment, you can add and remove users, add and remove roles, associate users with roles, and assign permissions to roles. A user gets all the permissions that are associated with the roles that he or she is in.

Design-time Data

During F5, LightSwitch generates a temporary SQL Server Express database to hold test data. This physical database is not deployed with the application, nor is any of the data that is entered during design-time.

LightSwitch attempts to maintain this data between F5 iterations, even as you make changes to the shape of the data. In cases where a data schema change cannot be performed on the design-time database without data loss, LightSwitch will warn you. If you accept the warnings, it will delete the test database and start from scratch with an empty database.

Using SharePoint Lists

LightSwitch allows you to access lists on a SharePoint 2010 site as tabular data.

Unsupported Features

LightSwitch only supports SharePoint 2010 and higher—those versions that support exposing OData. LightSwitch does not support managing SharePoint attachments. All other data types are supported, but there is limited support for displaying or editing some of them, as noted below.

SharePoint Data Type Mapping

LightSwitch maps column types from SharePoint lists to LightSwitch data types according to the following table.

Table 5: SharePoint Data Type Mapping

A SharePoint Lookup column maps to an entity relationship in LightSwitch.

SharePoint Data Provider

All LightSwitch data services use an Entity Framework ObjectContext to manage data in the server data workspace. When there is no Entity Framework data provider available, LightSwitch uses a disconnected ObjectContext and builds its own shim later to move entities in and out of the ObjectContext via an alternate date access provider.

In the case of SharePoint 2010, there is no Entity Framework provider. LightSwitch has an internal adapter that talks to SharePoint via its OData feed, using an instance of System.DataServices.Client.DataServiceContext. Under the hood, LightSwitch does all the necessary query translation and entity type marshaling to go between the EF ObjectContext and the OData DataServiceContext.

The Entity Framework ObjectContext requires entity keys and foreign-keys to manage entity relationships. OData doesn’t handle entity relationships in the same way and does not naturally provide the foreign-key data. LightSwitch compensates for this by traversing the entity relationships to get to the necessary related entity keys and auto-populates the foreign-keys. This is a bit of extra work in the logic tier, but results in a consistent data access model and API.

Using a WCF RIA DomainService as a Data Adapter

LightSwitch lets you plug in a custom data adapter for cases where no other data provider technology is available. Because it requires writing some custom code and entity classes, it works best for custom in-house scenarios or for access to public services that always have the same data schema.

Rather than defining our own protocol for the data adapter, we noted that WCF RIA Services had already done a great job of defining an object that supports query and update to user-defined entity types: the DomainService. LightSwitch uses the same DomainService class as an in-memory adapter—without necessarily exposing it as a public WCF service endpoint.

The solution involves writing a custom DomainService and referencing the DLL from the LightSwitch project. LightSwitch uses .NET reflection to examine the DomainService and to infer the entity types and their read/insert/update/delete accessibilities. From this, LightSwitch generates its own data service and its own entity types so that business logic can be written against it just like with any other data service.

Unsupported Features

LightSwitch does not support the following features of the WCF RIA DomainService:

• Custom Entity Operations
• Domain Invoke Operations
• Multiple assemblies. The DomainService class and the entity classes that it exposes must be defined in the same assembly. The LightSwitch data import feature doesn’t resolve the entity types to a different assembly.

RIA Data Type Mapping

LightSwitch data types from the DomainService entities to LightSwitch data types according to the following table.

Table 6: RIA Entity Data Type Mapping

Data types exposed as nullable types will be imported as required.

LightSwitch also imports some of the metadata attributes specified on the entity types via System.ComponentModel.DataAnnotations. The supported metadata attributes are listed in Table 7.

Table 7: RIA Attribute Mapping

Exposing Stored Procedures

LightSwitch doesn’t support access to SQL stored procedures via the standard Entity Framework provider. However, you can use a custom DomainService to expose stored procedures.

Say for example that you have a SQL database where access to the data table is read-only. All inserts, updates and deletes must go through a stored procedure. You can create a DomainService that exposes access to the table as a query and implements custom Insert/Update/Delete methods to invoke the stored procedures.

Data Provider

Under the hood, LightSwitch does all of the necessary translation between its native entity types and the DomainService entity types. For queries, we also rewrite the query expression tree with the DomainService entity types and hand it off to the DomainSerivce for processing. For updates, we copy the changed LightSwitch entities into a new DomainService change set.

Summary

LightSwitch supports accessing data from a number of different data sources and aggregating that data together. LightSwitch can access data from SQL Server Express, SQL Server and SQL Azure and can also deploying its intrinsic database to any of these. LightSwitch can also access lists from SharePoint 2010, but does not yet have support for arbitrary OData sources.

To extend the data sources that LightSwitch can access, you can use a custom data adapter by creating a WCF RIA DomainService class and the related entity classes.