LightSwitch version 1.0 doesn’t support aggregate queries (like GROUP
BY, SUM, etc.). In order to get aggregate data into a LightSwitch
application, we will need to create a Custom WCF RIA Service. In order
to create a Custom WCF RIA Service, we need to create a new “Class
Library” project. Visual Studio LightSwitch by itself can only create
LightSwitch applications. You will need to either use Visual Studio or
Visual C#/Basic Express to create the new Class Library project.
In
this article, we will create a application that combines two separate
entities and creates a single one and also we can perform the following
actions with the help of WCF RIA services.
- Add new data
- Edit existing data
- Delete existing data
Create The Application
Create a new LightSwitch project called RIAProductInventory.
Make a Entity called InternalProduct, with the schema according to the image above.
Make a Entity called ExternalProduct, with the schema according to the image above.
Create Editable Grid Screens for each Entity, run the application, and enter some sample data.
The WCF RIA Service
We will now create a WCF RIA Service to do the following things:
- Create a new Entity that combines the two Entities
- Connects to the existing LightSwitch database
Create a New Project (Note: You must have Visual Studio Professional (or higher) to complete this tutorial).
Create a Class Library called WCF_RIA_Project.
Delete the Class1.cs file that is automatically created.
Add a New Item to WCF_RIA_Project.
Add a Domain Service Class called WCF_RIA_Service.
When the Add New Domain Service Class box comes up, uncheck Enable client access. Click OK.
Code and references will be added.
We need additional references, add the following references to the WCF_RIA_Project:
- System.ComponentModel.DataAnnotations
- System.Configuration
- System.Data.Entity
- System.Runtime.Serialization
- System.ServiceModel.DomainServices.Server
(Look in %ProgramFiles%\Microsoft SDKs\RIA
Services\v1.0\Libraries\Server if it isn’t under the .Net tab)
- System.Web
Add the following Using Statements to the class:
using ApplicationData.Implementation;
using System.Data.EntityClient;
using System.Web.Configuration;
Reference The LightSwitch Object Context
Now, we will add code from the LightSwitch project to our WCF RIA Service project. We will add a class that LightSwitch automatically creates, that connects to the database that LightSwitch uses.
We will use this class in our WCF RIA Service to communicate with the LightSwitch database.
Right-click on the WCF_RIA_Project and select Add then Existing Item.
Navigate to ..ServerGenerated\GeneratedArtifacts (in the LightSwitch project)and click on ApplicationData.cs and Add As Link.
We used “add As Link” so that whenever LightSwitch updates this class, our WCF RIA Service is also updated. This is how our WCF RIA Service would be able to see any new Entities (tables) that were added, deleted, or changed.
UPDATE: If you are using LightSwitch in Visual Studio 11, there isn’t a
“ServerGenerated” folder. The Server and ServerGenerated projects have
now been combined into one “Server” project. So instead of navigating
to “ServerGenerated” \ “GeneratedArtifacts”, navigate to “Server” \
“GeneratedArtifacts”.
Create the Domain Service
In the WCF_RIA_Service class, in the WCF RIA project, we now create a class to hold the combined tables.
Note, that we do not put the class inside the WCF_RIA_Service class. To do this would cause an error.
Use the following code:
public class CombinedProducts
{
[Key]
public int ID { get; set; }
public string Name { get; set; }
public int Quantity { get; set; }
}
Note that the “ID” field has been decorated with the “[Key]” attribute. WCF RIA Services requires a field to be unique. The [Key] attribute indicates that this will be the unique field.
Dynamically Set The Connection String
Next, Add the following code to the WCF_RIA_Service class:
#region Database connection
private ApplicationDataObjectContext m_context;
public ApplicationDataObjectContext Context
{
get
{
if (this.m_context == null)
{
string connString =
System.Web.Configuration.WebConfigurationManager
.ConnectionStrings["_IntrinsicData"].ConnectionString;
EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
builder.Metadata =
"res://*/ApplicationData.csdl|res://*/ApplicationData.ssdl|res://*/ApplicationData.msl";
builder.Provider =
"System.Data.SqlClient";
builder.ProviderConnectionString = connString;
this.m_context = new ApplicationDataObjectContext(builder.ConnectionString);
}
return this.m_context;
}
}
#endregion
This code dynamically creates a connection to the database. LightSwitch uses “_IntrinsicData” as it’s connection string. This code looks for that connection string and uses it for the WCF RIA Service.
Create The Select Method
Add the following code to the class:
[Query(IsDefault = true)]
public IQueryable<CombinedProducts> GetAllProducts()
{
var colInternalProducts = from InternalProducts in this.Context.InternalProducts
select new CombinedProducts
{
// Note we turn the ID of the Internal Products to
// A negative number so we don't have duplicates
// with the External products
ID = InternalProducts.Id * (-1),
Name = InternalProducts.Name,
Quantity = InternalProducts.Quantity
};
var colExternalProducts = from ExternalProducts in this.Context.ExternalProducts
select new CombinedProducts
{
ID = ExternalProducts.Id,
Name = ExternalProducts.Name,
Quantity = ExternalProducts.Quantity
};
return colInternalProducts.Union(colExternalProducts);
}
// Override the Count method in order for paging to work correctly
protected override int Count<T>(IQueryable<T> query)
{
return query.Count();
}
This code combines the InternalProducts and the ExternalProducts, and returns one collection using the type CombinedProducts.
Note that this method returns IQueryable so that when it is called by LightSwitch, additional filters can be passed, and it will only return the records needed.
Note that the ID column needs to provide a unique key for each record. Because it is possible that the InternalProducts table and the ExternalProducts table can have records that have he same ID, we multiply the ID for the InternalProducts by –1 so that it will always produce a negative number and will never be a duplicate of the ExternalProducts Id’s that are always positive numbers.
Also notice that the GetAllProducts method is marked with the [Query(IsDefault = true)]
attribute, one method, for each collection type returned, must not
require a parameter, and be marked with this attribute, to be used with LightSwitch.
Consume The WCF RIA Service
Build the solution.
You will get a ton or warnings. you can ignore them.
In the Solution Explorer, right-click on the Data Sources folder and select Add Data Source.
Select WCF RIA Service.
Click Add Reference.
Select the RIA Service project.
You have to wait for the service to show up in the selection box. Select it and click Next.
Check the box next to the Entity, and click Finish.
The Entity will show up.
Create a Screen That Shows The Combined Products
Add a Screen.
Add an Editable Grid Screen, and select the Entity for the Screen Data.
Delete the ID column (because it is not editable anyway).
Run the application.
You will be able to see the combined Products.
However, you will not be able to edit them.
Updating Records In A WCF RIA Service
Add the following code to the WCF RIA Service
public void UpdateCombinedProducts(CombinedProducts objCombinedProducts)
{
// If the ID is a negative number it is an Internal Product
if (objCombinedProducts.ID < 0)
{
// Internal Product ID's were changed to negative numbers
// change the ID back to a positive number
int intID = (objCombinedProducts.ID * -1);
// Get the Internal Product
var colInternalProducts = (from InternalProducts in this.Context.InternalProducts
where InternalProducts.Id == intID
select InternalProducts).FirstOrDefault();
if (colInternalProducts != null)
{
// Update the Product
colInternalProducts.Name = objCombinedProducts.Name;
colInternalProducts.Quantity = objCombinedProducts.Quantity;
this.Context.SaveChanges();
}
}
else
{
// Get the External Product
var colExternalProducts = (from ExternalProducts in this.Context.ExternalProducts
where ExternalProducts.Id == objCombinedProducts.ID
select ExternalProducts).FirstOrDefault();
if (colExternalProducts != null)
{
// Update the Product
colExternalProducts.Name = objCombinedProducts.Name;
colExternalProducts.Quantity = objCombinedProducts.Quantity;
this.Context.SaveChanges();
}
}
}
Build the solution.
Right-click on the WCF_RIA_Service node in the Solution Explorer, and select Update Datasource.
When the wizard shows up click Finish.
Run the application.
You will now be able to update the records.
Following code is used for delete the existing record.
public void DeleteCombinedProducts(CombinedProducts objCombinedProducts)
{
if (objCombinedProducts.id < 0)
{
var colInternalProducts = (from InternalProducts in this.Context.InternalProducts
where InternalProducts.Id == (objCombinedProducts.id * -1)
select InternalProducts).FirstOrDefault();
if (colInternalProducts != null)
{
this.Context.DeleteObject(colInternalProducts);
this.Context.SaveChanges();
}
}
else
{
var colExternalProducts = (from ExternalProducts in this.Context.ExternalProducts
where ExternalProducts.Id == objCombinedProducts.id
select ExternalProducts).FirstOrDefault();
if (colExternalProducts != null)
{
this.Context.DeleteObject(colExternalProducts);
this.Context.SaveChanges();
}
}
}
Following code can be used for Insert new data.
//Insert new record on the bases of type selection
public void AddCombinedProducts(CombinedProducts objCombinedProducts)
{
if (objCombinedProducts.type == "Internal")
{
InternalProduct obj = new InternalProduct();
obj.Name = objCombinedProducts.Name;
obj.Qty = objCombinedProducts.Qty;
this.Context.AddToInternalProducts(obj);
this.Context.SaveChanges();
}
else
{
ExternalProduct obj = new ExternalProduct();
obj.ExtProductName = objCombinedProducts.Name;
obj.Qty = objCombinedProducts.Qty;
this.Context.AddToExternalProducts(obj);
this.Context.SaveChanges();
}
}
Now you can perform all operation by WCF RIA services.
