The Anatomy of a LightSwitch Application Part 4 – Data Access and Storage

The Anatomy of a LightSwitch Application Part 4 – Data Access and Storage

Rate This
  • Comments 10

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.

image001[4]

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.

image002[4]

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

Data Storage Service

Data Provider

Supported Features

Not Supported

Microsoft SQL Server

SQL Server 2005

SQL Server 2008

SQL Server 2008 R2

including Express versions

SqlClient for the Entity Framework

 

Referential integrity

Transactions

SQL authentication

Integrated Windows authentication

SQL Server Compact

Stored Procedures

Microsoft SQL Azure

SqlClient for the Entity Framework

 

Referential integrity

Transactions

SQL authentication

Stored Procedures

Integrated Windows authentication

Azure Federated authentication

Microsoft SharePoint

SharePoint 2010

OData client DataServiceContext

Lists

Relationships

Attachments

Transactions

Other (RIA)

Requires a custom WCF RIA DomainService..

Default queries

Parameterized queries

Transactions

Entity operations

Custom operations

Complex types

Query “Includes”

‡ 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

LightSwitch Type

VB Type

C# Type

Range

Remarks

Binary

Byte()

byte[]

Each byte is 0 to 255

Variable length array of bytes; MaxLength specifies the maximum number of bytes

Boolean

Boolean

bool

True or False

 

Byte

Byte

byte

0 to 255

 

Date

Date

DateTime

Jan 1, 0001 AD (CE) to
Dec 31, 9999 AD (CE)

A DateTime treated as date only; LightSwitch truncates any time portion

DateTime

Date

DateTime

00:00:00 Jan 1, 0001 AD (CE) to
23:59:59 Dec 31, 9999 AD (CE)

 

Decimal

Decimal

decimal

±1.0e−28 to ±7.9e28

Fixed decimal point value with 28-29 significant digits; suitable for financial and monetary values; stored with specific precision and scale

Double

Double

double

±5.0e−324 to ±1.7e308

Floating decimal point with 15-16 digits precision; suitable for scientific numbers

EmailAddress

String

string

 

A String treated as an email address

Guid

Guid

Guid

{00000000-0000-0000-0000-000000000000} to
{FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}

A 128-bit integer used as a unique ID

Image

Byte()

byte[]

 

A Binary treated as an image

Int16

Short

short

-32,768 to 32,767

A signed 16-bit integer

Int32

Integer

int

-2,147,483,648 to 2,147,483,647

A signed 32-bit integer

Int64

Long

long

-9,223,372,036,854,775,808 to

9,223,372,036,854,775,807

A signed 64-bit integer

Money

Decimal

decimal

 

A Decimal treated as a monetary value

PhoneNumber

String

string

 

A String treated as a phone number

SByte

SByte

sbyte

-128 to 127

A signed 8-bit integer

Single

Single

float

±1.5e−45 to ±3.4e38

Floating decimal point with 7 digits precision

String

String

string

A sequence of zero or more Unicode characters

A variable length character string; MaxLength specifies the maximum number of characters

TimeSpan

TimeSpan

TimeSpan

±10675199.02:48:05.4775807

A time interval in days, hours, minutes, seconds, and fractions of a second
When stored in SQL the range is 00:00:00.0000000 to 23:59:59.9999999

 

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.) 

 

Keyword

Value

Remarks

Data Source

ServerName

The name of the SQL Server, SQL Express instance, or “tcp:” address of the SQL Azure database

Initial Catalog

DatabaseName

The SQL database name

User ID

SQLLoginName

The SQL Server login account

Password

SQLLoginPassword

The SQL Server login password

Integrated Security

False

Integrated security is not supported with SQL Azure.

Pooling

True

Enables connection pooling (True recommended)

Connect Timeout

30

Timeout in seconds to wait for a connection to the server

User Instance

False

Don’t redirect SQL Express to a user instance (False recommended)

Encrypt

True

Use SSL encryption for data sent between SQL Server and LightSwitch (True recommended)

TrustServerCertificate

False

Bypass SSL certificate verification (False recommended)

 

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

Imported Column Type

LightSwitch Type

Supported Attributes

binary(n)

Binary

Required, MaxLength=n

image

Binary

Required, MaxLength=Max

timestamp

Binary

Required, MaxLength=8

varbinary(n)

Binary

Required, MaxLength=n

bit

Boolean

Required

tinyint

Byte

Required

date

Date

Required

datetime

DateTime

Required

datetime2(n)

DateTime

Required

smalldatetime

DateTime

Required

decimal(p,s)

Decimal

Required, Precision=p, Scale=s

money

Decimal

Required, Precision=19, Scale=4

numeric(p,s)

Decimal

Required, Precision=p, Scale=s

smallmoney

Decimal

Required, Precision=10, Scale=4

float

Double

Required

uniqueidentifier

Guid

Required

smallint

Int16

Required

int

Int32

Required

bigint

Int64

Required

real

Single

Required

char(n)

String

Required, MaxLength=n

nchar(n)

String

Required, MaxLength=n

ntext

String

Required

nvarchar(n)

String

Required, MaxLength=n

text

String

Required

varchar(n)

String

Required, MaxLength=n

xml

String

Required, MaxLength=Max

time(n)

TimeSpan

Required

datetimeoffset

not supported

 

geography

not supported

 

geometry

not supported

 

hierarchyid

not supported

 

sql_variant

not supported

 

 

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

LightSwitch Type

Supported Attributes

Generated SQL Column Type

Binary

Required, MaxLength=n

varbinary(n) [null]

Boolean

Required

bit [null]

Date

Required

date [null]

DateTime

Required

datetime [null]

Decimal, Money

Required, Precision=p, Scale=s

decimal(p,s) [null]

Double

Required

float [null]

Int16

Required

smallint [null]

Int32

Required

int [null]

Int64

Required

bigint [null]

String, PhoneNumber, EmailAddress

Required, MaxLength=n

nvarchar(n) [null]

 

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

SharePoint List Column Type

LightSwitch Type

Remarks

Yes/No

Boolean

 

Date & Time

DateTime

 

Date Only

DateTime

A Date Only column is not imported as Date type, but can be changed to Date in the designer.

Currency

Double

 

Number

Double

 

Id

Int32

Id columns are imported as read-only and hidden.

Choice Menu

String

LightSwitch imports the set of choice values specified in SharePoint.

Hyperlink

String

No built-in Hyperlink control to view the Url

Multiple Lines of Text

String

No built-in HTML viewer control to view or edit the data

Picture

String

No built-in Picture viewer control to display the public Url

Single Line of Text

String

LightSwitch imports the max length if one is specified in SharePoint.

Attachment

not supported

 


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

RIA Entity Property Type

LightSwitch Type

Supported Attributes

System.Byte[]

Binary

 

System.Boolean

Boolean

Required

System.Byte

Byte

Required

System.DateTime

DateTime

Required

System.Double

Double

Required

System.Guid

Guid

Required

System.Int16

Int16

Required

System.Int32

Int32

Required

System.Int64

Int64

Required

System.SByte

SByte

Required

System.Single

Single

Required

System.String

String

 

System.TimeSpan

TimeSpan

Required

All others

not supported

 


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

RIA Attribute

LightSwitch Attribute

DisplayAttribute

DisplayName, Description

EditableAttribute

IsReadOnly

EnumDataTypeAttribute

SupportedValue (choice list)

RangeAttribute

Range

RequiredAttribute

Required

ScaffoldColumnAttribute

Hidden

StringLengthAttribute

MaxLength

TimestampAttribute

Hidden

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.

Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
  • What enhancements can we expect to future LightSwitch releases from the new features announced today for Silverlight 5?

    Cheers,

    Roger Jennings

    oakleafblog.blogspot.com

  • What enhancements can we expect to future LightSwitch releases from the new features announced today for Silverlight 5?

    Cheers,

    Roger Jennings

    oakleafblog.blogspot.com

  • Thanks for this description. I am trusting you all to be done well at the end of a day :-). Rogerj: LS is nto about SL, hopefully they can model declarations to be UI swappable by shells at all to something not yet even known :-)

  • I assume that some information on this page is not updated. Table 2 for example is not the same in the help file from Beta 2 IDE. I assume Beta 2 help file as the most recent. I appreciate your comments.

  • I assume you are referring to the Integer types? The table is still correct, however in the IDE Int16, Int32 and Int64 now map to the friendly names Short Integer, Integer, and Long Integer.

  • Very cool...indeed... Interesting how when one explores EF 4.0 the first thing they think about is How do I get more form automation?  With EF 4.0 we can generate DataSources which then allow us to drag the table as a DataGrid or Detail form type in WPF.  Looks like Lightswitch bridges that gap, in that it's all built in.  Of course the big question for Developers is, how to we hook up to existing database tables, this post answered that question.

  • No support for Access accdb?

  • This sounded so exciting .... Until the statement that seems mandatory for any Microsoft RAD tool -- "stored procedures are not supported".  And yes, I know I can write a custom domain service to expose stored procedures.  But, by not including stored procedures as a base capability, this turns into another tool/platform that is viewed a a "toy" or prototype tool.  The perception is that if Microsoft *still* doesn't realize that stored procedures are the basis for enterprise applications, then what other features are missing that would prevent light switch applications from being sustainable in a production environment?

  • How do execute a distinct query? When I create two combobox retrieves data from a table but different display then how?

  • Agree with Joe Rush 100%. No stored procedure support makes it us unusable for us. We have tons of SP's that other apps use and now we have to duplicae all this SQL?

Page 1 of 1 (10 items)