Combining Two Tables - WCF RIA Service
LightSwitch operates on one Entity (table) at a time. A Custom Control will allow you to visualize data from two entities at the same time, but inside LightSwitch, each Entity is always separate. This can be a problem if you want to, for example, combine two Entities into one.
LightSwitch wont do that… unless you use a WCF RIA Service. Now you can TRY to use a PreProcess Query, but a PreProcess Query is only meant to filter records in the Entity it is a method for. You cannot add records, only filter them out (this may be easier to understand if you realize that a PreProcess Query simply tacks on an additional Linq statement, to “filter” the linq statement, that is about to be passed to the data source).
In this article, we will create a application that combines two separate entities and creates a single one. Note that this article is basically just a C# rehash of the Eric Erhardt article, but this one will move much slower, and provide a lot of pictures :). We will also cover updating records in the WCF RIA Service.
Also note, that the official LightSwitch documentation covers creating WCF RIA Services in LightSwitch in more detail: http://msdn.microsoft.com/en-us/library/gg589479.aspx
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 SDKsRIA Servicesv1.0LibrariesServer 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 ..ServerGeneratedGeneratedArtifacts (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.
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 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(IQueryable 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.
Happy Coding
|