Many to Many (or n to m) relationships in real world scenarios are quite common. Unfortunately this cardinality LightSwitch doesn’t handle out of box (maybe in next version we will see). But this task can be done. With help of WCF RIA Services. And resources around the internet, like this one.
Firt of all , reasons I decided to write this post are :
- I was able to (at least I think :)) write code in RIA Services little better (not everything in one .cs file), than it is in samples I found,
- There are some tweaks in UI that I would recommend you to do (but are not required) I incorporated my solution to project I’m still working on so it should work as expected (customer will let us know for sure, if not :)).
Links to other blogs I found :
http://blogs.msdn.com/b/lightswitch/archive/2010/12/16/how-to-create-a-many-to-many-relationship-andy-kung.aspx – oldest one, but I didn’t like the UI,
http://www.paulspatterson.com/technology/lightswitch/microsoft-lightswitch-many-to-many-relationships/ – didn’t tried this one,
http://powerbala.com/2011/04/10/many-to-many-control-for-microsoft-lightswitch/ – I started with this one, because I like the way it’s using checkboxes. (you could start with this one and then return to this post, but you don’t have to).
http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/34/Creating-a-Simple-LightSwitch-RIA-Service-using-POCO.aspx – this page isn’t about n to n , but it’s about creating WCF RIA Service library in your LightSwitch project and I would say this is the correct approach. You just need to create Class library, create POCO class and Domain Service Class and reference some WCF RIA Services libraries and you are ready to go. Inserting WCF RIA Services Class library project into LightSwitch solution will also add Web project that isn’t really used anywhere.
So, first thing we need is database with appropriate tables.
In my example I will use these tables :
Bike and User have PK column (Primary Key) of type Int and with Is Identity set to true, so it will auto increment on each insert. Same way as LightSwitch. Junction table is table that will say to which Bike we want a User. Each row in this table means one connection between particular User and Bike. Both rows are composite PK so they are unique. We will access this table from WCF RIA Services class library code.
Because I created these tables in SQLExpress, now I need to connect to them in LightSwitch. Click on Attach to external Data Source in VS 2010 LightSwitch welcome screen (Note : LightSwitch maintains it’s own database in \Bin\Data\Temp subfolder of solution. This is the place where all tables are create, when you design them via VS 2010 interface and where all tables for ASP.NET membership are stored. If we would create all these ) :
Choose database and click next, input name of sql server and database name (in my case like this)
Select one entity (in my case Users) and then you can see something like this in Solution Explorer :
Then add new Class library to whole solution (I was able to do that from Solution Navigator pane) and name it as you like.
Add
I found out, that if you want to structure your class library to have overview over multiple classes, it would be better to :
- Create folder per every item you want to access from WCF RIA Services. With item currently I mean junction table,
- Create class for POCO object,
- Create DAL (Data Access Layer) class that would use POCO Class and handle CRUD in datasource you like (you are accessing, in this case SQL Server Express), you could also create interface that this class will derive from and use it for testing, or perhaps use some DI,
- Create class that will derive from DomainService and expose all actions per item to LightSwitch – WCF RIA Services Domain Service Class (with adding DomainContext class to the project you also add required references to your .csproj. but we also need to add System.Web and System.Configuration, I will tell you why later)
OK, so now in new Class Library project create a subfolder. I created BikeUser subfolder for this case.
We will follow this article : http://powerbala.com/2011/04/10/many-to-many-control-for-microsoft-lightswitch/ and create class with these properties :
Two properties of type int for both columns in BikeUserJunction table. (BikeFK and UserFK both decorated with [Key] attribute), some usefull name to identify Bike, so we can name it BikeName and IsSelected boolean prop.
And of course one static method to create instance of this object. Code could look like this :
namespace WCFRIAServicesClassLibrary.BikeUser
{
using System.ComponentModel.DataAnnotations;
using System.Data;
public class BikeUserPoco
{
[Key]
public int BikeFK { get; set; }
[Key]
public int UserFK { get; set; }
public string BikeName { get; set; }
public bool IsSelected { get; set; }
public static BikeUserPoco CreateNew(IDataRecord iDR)
{
return new BikeUserPoco
{
TermsCategoriesID = (int)iDR["BikeFK"],
TermsConditionsID = (int)iDR["UserFK"],
TermCategory = (string)iDR["BikeName"],
IsSelected = (bool)iDR["IsSelected"]
};
}
}
}Then we need DAL Class that could look like this :
namespace WCFRIAServicesClassLibrary.BikeUser
{
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Data Accesss Class. This class will be called by DomainService class.
/// </summary>
internal class BikeUserDAL
{
/// <summary>
/// Selects the bike user records from db.
/// </summary>
/// <param name="UserFK">The user FK.</param>
/// <param name="ConnectionString">The connection string.</param>
/// <returns></returns>
public static IEnumerable<BikeUserPoco> SelectBikeUser(int? UserFK, string ConnectionString)
{
var sComm = new SqlCommand { CommandType = CommandType.Text };
sComm.Parameters.AddWithValue("UserFK", UserFK);
sComm.CommandText = @"SELECT b.BikeID AS BikeID, @UserFK AS UserFK, b.BikeName AS BikeName,
CASE WHEN
j.UserFK IS NULL
THEN
CAST (0 AS BIT)
ELSE
CAST (1 AS BIT)
END AS IsSelected
FROM
Bike AS b LEFT OUTER JOIN
BikeUserJunction AS j ON
b.BikeID = j.BikeFK AND
j.UserFK = @UserFK;";
using (var sConn = new SqlConnection(ConnectionString))
{
sComm.Connection = sConn;
sConn.Open();
SqlDataReader sDataReader = sComm.ExecuteReader();
if (sDataReader.HasRows)
while (sDataReader.Read())
yield return BikeUserPoco.CreateNew(sDataReader);
sConn.Close();
}
}
/// <summary>
/// Inserts the bike user records from db.
/// </summary>
/// <param name="UserFK">The user FK.</param>
/// <param name="BikeFK">The bike FK.</param>
/// <param name="ConnectionString">The connection string.</param>
public static void InsertBikeUser(int UserFK, int BikeFK, string ConnectionString)
{
var sComm = new SqlCommand { CommandType = CommandType.Text };
sComm.Parameters.AddWithValue("UserFK", UserFK);
sComm.Parameters.AddWithValue("BikeFK", BikeFK);
sComm.CommandText = @"INSERT INTO BikeUserJunction (UserFK, BikeFK) VALUES (@UserFK, @BikeFK)";
using (var sConn = new SqlConnection(ConnectionString))
{
sComm.Connection = sConn;
sConn.Open();
sComm.ExecuteNonQuery();
sConn.Close();
}
}
/// <summary>
/// Deletes the bike user records from db.
/// </summary>
/// <param name="UserFK">The user FK.</param>
/// <param name="BikeFK">The bike FK.</param>
/// <param name="ConnectionString">The connection string.</param>
public static void DeleteBikeUser(int UserFK, int BikeFK, string ConnectionString)
{
var sComm = new SqlCommand { CommandType = CommandType.Text };
sComm.Parameters.AddWithValue("UserFK", UserFK);
sComm.Parameters.AddWithValue("BikeFK", BikeFK);
sComm.CommandText = @"DELETE BikeUserJunction WHERE UserFK = @UserFK AND BikeFK = @BikeFK";
using (var sConn = new SqlConnection(ConnectionString))
{
sComm.Connection = sConn;
sConn.Open();
sComm.ExecuteNonQuery();
sConn.Close();
}
}
}
}And last one : DomainService (uncheck Enable Client Access when inserting this class) that could look like this :
namespace WCFRIAServicesClassLibrary
{
using System;
using System.Collections.Generic;
using System.ServiceModel.DomainServices.Server;
using System.Web.Configuration;
// TODO: Create methods containing your application logic.
// TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
public class CommonDomainService : DomainService
{
#region initialization of connection string
/// <summary>
/// Initializes this <see cref="T:System.ServiceModel.DomainServices.Server.DomainService"/>. <see cref="M:System.ServiceModel.DomainServices.Server.DomainService.Initialize(System.ServiceModel.DomainServices.Server.DomainServiceContext)"/> must be called
/// prior to invoking any operations on the <see cref="T:System.ServiceModel.DomainServices.Server.DomainService"/> instance.
/// </summary>
/// <param name="context">The <see cref="T:System.ServiceModel.DomainServices.Server.DomainServiceContext"/> for this <see cref="T:System.ServiceModel.DomainServices.Server.DomainService"/>
/// instance. Overrides must call the base method.</param>
public override void Initialize(DomainServiceContext context)
{
if ((WebConfigurationManager.ConnectionStrings
[(this.GetType().FullName)] == null) ||
string.IsNullOrWhiteSpace(WebConfigurationManager.ConnectionStrings
[GetType().FullName].ConnectionString))
{
/*_connectionString = "data source=NorthwindDB;initial catalog= " +
"Northwind;user id=myID;password=myPassword";*/
throw new Exception("Can’t load connection string for WCF RIA service data source");
}
else
{
ConnectionString = WebConfigurationManager.ConnectionStrings
[GetType().FullName].ConnectionString;
}
base.Initialize(context);
}
/// <summary>
/// Private property for current connection string.
/// </summary>
/// <value>
/// The connection string.
/// </value>
private string ConnectionString { get; set; }
#endregion initialization of connection string
#region BikeUser
//All this code will be called directly by LightSwitch
//Fake Function to satisfy WCF RIA requirement
//without this query LS will not accept this library
//in NtoN scenario wi will not use this query, its used when whole page is loaded.
[Query(IsDefault = true)]
public IEnumerable<BikeUser.BikeUserPoco> SelectFakeBikeUser()
{
return BikeUser.BikeUserDAL.SelectBikeUser(-1, ConnectionString);
}
[Query]
public IEnumerable<BikeUser.BikeUserPoco> SelectBikeUser(int? UserFK)
{
return BikeUser.BikeUserDAL.SelectBikeUser(UserFK, ConnectionString);
}
[Insert]
public void CreateBikeUser(BikeUser.BikeUserPoco bup)
{
BikeUser.BikeUserDAL.InsertBikeUser(bup.UserFK, bup.BikeFK, ConnectionString);
}
[Update]
public void UpdateBikeUser(BikeUser.BikeUserPoco bup)
{
if (bup.IsSelected)
CreateBikeUser(bup);
else
DeleteBikeUser(bup);
}
[Delete]
public void DeleteBikeUser(BikeUser.BikeUserPoco bup)
{
BikeUser.BikeUserDAL.DeleteBikeUser(bup.UserFK, bup.BikeFK, ConnectionString);
}
#endregion BikeUser
}
}Remember when I told you to add two more references? These assemblies are needed to access connection string from database via WebConfigurationManager.ConnectionStrings classes. Of course there is also another way to access Connection strings from .config files, choose what you like. I chose this approach.
After this, WCF RIA Services class for our LightSwitch app to consume should be ready.
Just a short look at all my references :
OK, if you are able to build the project, you can reference it in LightSwitch.
Right click on Data Sources node, choose WCF RIA Services, click Next and you should be able to Add Reference. If Choose or CommonDomainService class and click Next :
Then you will see window like this :
This is the place where we can set connection string which will use our code to access data in WCF RIA Services Domain Service. Connections string will be pulled from web.config that will be deployed with published solution. It can look like this :
Data Source=.\SQLEXPRESS;Initial Catalog=LSnton;Integrated Security=TrueBut please be aware, that this will connect to DB with credentials of user that is running the application and therefore you should user SQL Server authentication.
Connection string is read on overriden Initialize method of Domain Service and as key LightSwitch is using the type of class (GetType().FullName property).
I found this out from MSDN documentation here :
http://msdn.microsoft.com/en-us/library/gg589479.aspx – Guidelines for Creating WCF RIA Services for LightSwitch (btw good read)
Now to the UI:
Add new screen like this (Editable grid screen):
Switch to columns layout. Add new data item and select Query and choose SelectBikeUser with return type of set of BikeUserPoco.
Now connect UserFK query parameter of SelectBikeUser query with Users query by clicking on UserFK and then on right side textbox ParameterBinding and VS 2010 UI will display possible values. In this case it will be Users.SelectedItem.UserID.
After connection you will see line pointing to UserID in Users query. Like so :
On this picture you can also see that there is only Is Selected and Bike Name in Select User Poco grid. I also recommend you to delete all commands from this data grid from command bar and also disable Show Add-new row checkbox, since its not needed.
After all this you should be able to see window like this one :
You can find DB you need and whole sample app packaged on this link (LightSwitch_n_to_n.zip file) :
You just need to have SQL Server Express installed and mount files in dbs folder and then open project with VS2010 with LightSwitch installed.
In case I missed some step or you need help, just mail me od write in the discussion or consult the original powerbala article. I know I have made just tiny changes to the original, but I hope they will make a change.
Hope this helps.