WCF RIA Service: Combining Two Tables, Add, Delete, Edit data using WCF RIA Services in lightswitch

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.

1. Add new data 
2. Edit existing data
3. Delete existing data

Helped taken from Michael Washington blog.

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.