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