How to create a VB WCF RIA Services for Microsoft Lightswitch to aggregate or join tables at the same time pass a parameter.

I’ve searched over the Internet and found no workable code for my problem which is to aggregate my Journal Entries table base on a parameter thrown within my Microsoft Ligthswitch screen, to be exact:  I want to sum of all the debit and credit amount by account base on a date encoded by the users.  This is so typical and the solution is so000 long :).  And here it is:

  1. Create a WCF RIA Service Class project.
  2. Link the tables (EF tables)  of your current project to the new WCF RIA Service Class project.
  3. Modify your WCF RIA Service Class to point to the link table (see item no.2)
  4. Make a Table Class outside your WCF RIA Service Class but in the same file.
  5. Make a Query Method inside your WCF RIA Service Class to extract the data and return the Table Class you made in item no. 4
  6. Import the WCF RIA Service Project to your current project to consume the data.

Now the details…

Create a WCF RIA Service Class project.

Add new project to your existing solution.

The name of my new WCF RIA Service Class is dinergie_wfc.  Now delete the default Class1.VB which was automatically created when you create the project.

Since this is just an ordinary class project, of course there are lacking references

Now add the following references.  If you don’t how to add just right click on the Reference and press add 🙂 that simple.  Most of the references are in the .NET tab except one, to find follow the link below in the Browse tab.

With these references you are now ready to create a generic WCF RIA Service Class.

To create a WCF RIA Service Class, just right click on the project and add new item, in my project the name of my Domain Service Class is accounting_dinergie_wcf.

When you click add VS will prompt you with the dialog below, just un-check the Enable client access, the reason, is.., i really don’t know and I don’t care. 🙂 (Update here is the reason: http://lightswitchhelpwebsite.com/Forum/tabid/63/aft/767/Default.aspx#1797)

You should see a similar code below inside your created class after you press OK.  This class is now a WCF RIA Service class because it has all the imports and it inherits DomainService

Now its time to get our hands dirty.

Link the tables (EF tables)  of your current project to the new WCF RIA Service Class project.

The next problem is how to connect our WCF RIA Service Class to our Database.  If this is Microsoft Access just right click and link table from ODBC hahaha… not here… but similar:

Here are the sub steps in linking your existing data to your new WCF RIA Service class:

  1. In your WCF RIA Service class add and existing item.
  2. Navigate to your project: …\dinergie\ServerGenerated\GeneratedArtifacts\
  3. Choose your project’s data source name, in my project, the name of the data source is accounting_dinergieData
  4. And lastly instead of clicking Add click Add As Link, VERY IMPORTANT.

After you press Add As Link, you could see in your WCF RIA Service Class project a link file as shown below:

We are now ready to do some coding:

Modify your WCF RIA Service Class to point to the link table (see item no.2)

Option Compare Binary
Option Infer On
Option Strict On
Option Explicit On

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Linq
Imports System.ServiceModel.DomainServices.Hosting
Imports System.ServiceModel.DomainServices.Server
Imports System.Data.EntityClient
Imports 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 accounting_dinergie_wcf
    Inherits DomainService

    Private _context As accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext

    Public ReadOnly Property Context As accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext
        Get
            If _context Is Nothing Then
                Dim builder = New EntityConnectionStringBuilder
                builder.Metadata =
                    "res://*/accounting_dinergieData.csdl|res://*/accounting_dinergieData.ssdl|res://*/accounting_dinergieData.msl"
                builder.Provider = "System.Data.SqlClient"
                builder.ProviderConnectionString =
                    WebConfigurationManager.ConnectionStrings("accounting_dinergieData").ConnectionString

                _context =
                    New accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext(
                      builder.ConnectionString)
            End If
            Return _context
        End Get
    End Property

    '''
<summary> ''' Override the Count method in order for paging to work correctly
 ''' </summary>
    Protected Overrides Function Count(Of T)(query As IQueryable(Of T)) As Integer
        Return query.Count()
    End Function

End Class

What did I do?  First I added two Imports: EntityClient, Web.Configuration,  I also added three mandatory properites: _context, Context and Count.  Just take note of the accounting_dinergieData, change it to your data source name.  Thats it!  Its time to move on.

Make a Table Class outside your WCF RIA Service Class but in the same file.

You put this code inside accounting_dinergie_wcf (our WCF RIA Service Class) right below the End Class of the above code.

Public Class trnJournalEntriesR001
    <key()>
    Public Property AccountId As Integer
    Public Property AccountNumber As String
    Public Property AccountName As String
    Public Property NumberOfEntries As Integer
    Public Property TotalDebitAmount As Decimal
    Public Property TotalCreditAmount As Decimal
    Public Property PeriodId As Integer
End Class

Sorry about my naming convention, for 15 years of software development I tend to standardize how I name everything, so name of the table is trnJournalEntriesR001, trn stands for transactions while R001 standards for RIA service number 1 and so on and so forth.

Make a Query Method inside your WCF RIA Service Class to extract the data and return the Table Class you made in item no. 4

Now below is the complete code:

Option Compare Binary
Option Infer On
Option Strict On
Option Explicit On

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Linq
Imports System.ServiceModel.DomainServices.Hosting
Imports System.ServiceModel.DomainServices.Server
Imports System.Data.EntityClient
Imports 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 accounting_dinergie_wcf
    Inherits DomainService

    Private _context As accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext

    Public ReadOnly Property Context As accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext
        Get
            If _context Is Nothing Then
                Dim builder = New EntityConnectionStringBuilder
                builder.Metadata =
                    "res://*/accounting_dinergieData.csdl|res://*/accounting_dinergieData.ssdl|res://*/accounting_dinergieData.msl"
                builder.Provider = "System.Data.SqlClient"
                builder.ProviderConnectionString =
                    WebConfigurationManager.ConnectionStrings("accounting_dinergieData").ConnectionString

                _context =
                    New accounting_dinergieData.Implementation.accounting_dinergieDataObjectContext(
                      builder.ConnectionString)
            End If
            Return _context
        End Get
    End Property

    '''
<summary> ''' Override the Count method in order for paging to work correctly
 ''' </summary>
    Protected Overrides Function Count(Of T)(query As IQueryable(Of T)) As Integer
        Return query.Count()
    End Function

    <Query(IsDefault:=True)>
    Public Function GettrnJournalEntriesR001() As IQueryable(Of trnJournalEntriesR001)
        Return From j In Me.Context.trnJournalEntries
               Group By Account = j.mstAccount, j.mstPeriod Into g = Group
               Select New trnJournalEntriesR001 With {
                                                .AccountId = Account.Id,
                                                .AccountNumber = Account.AccountNumber,
                                                .AccountName = Account.AccountName,
                                                .NumberOfEntries = g.Count(),
                                                .TotalDebitAmount = g.Sum(Function(e) e.DebitAmount),
                                                .TotalCreditAmount = g.Sum(Function(e) e.CreditAmount),
                                                .PeriodId = Account.mstPeriod.Id
                                               }
    End Function

    Public Function trnJournalEntriesR001DateAsOf(DateAsOf As Nullable(Of Date)) As IQueryable(Of trnJournalEntriesR001)
        Dim filteredEntries = From j In Me.Context.trnJournalEntries Where j.JournalDate         Return From j In filteredEntries
               Group By Account = j.mstAccount, j.mstPeriod Into g = Group
               Select New trnJournalEntriesR001 With {
                                                .AccountId = Account.Id,
                                                .AccountNumber = Account.AccountNumber,
                                                .AccountName = Account.AccountName,
                                                .NumberOfEntries = g.Count(),
                                                .TotalDebitAmount = g.Sum(Function(e) e.DebitAmount),
                                                .TotalCreditAmount = g.Sum(Function(e) e.CreditAmount),
                                                .PeriodId = Account.mstPeriod.Id
                                               }
    End Function

End Class

Public Class trnJournalEntriesR001
    <key()>
    Public Property AccountId As Integer
    Public Property AccountNumber As String
    Public Property AccountName As String
    Public Property NumberOfEntries As Integer
    Public Property TotalDebitAmount As Decimal
    Public Property TotalCreditAmount As Decimal
    Public Property PeriodId As Integer
End Class

In my example I made two queries: GettrnJournalEntriesR001 (the default query, this is mandatory) and trnJournalEntriesR001DateAsOf (an optional query, but I added a parameter-very important example)

I will not explain how I query the data but it is just a simple aggregate LINQ.

Thats it, now build the WCF RIA Service project to start consuming it.

Import the WCF RIA Service Project to your current project to consume the data.

Open your project then add a new data source

Add a reference, navigate to the Projects tab and choose your WCF RIA Service Project.

Now choose the entities, notice the entity, it is our Table Class.

And wham, we have a table and a query with a parameter to play with:

The lesson of the blog is “just get your hands dirty”.

Additional information and guidelines in making a WCF RIA Service can be found here: http://msdn.microsoft.com/library/gg589479.aspx

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in Lightswitch
13 comments on “How to create a VB WCF RIA Services for Microsoft Lightswitch to aggregate or join tables at the same time pass a parameter.
  1. Yann says:

    Nice article. Pretty much a rehash of Eric Erhardt’s original article from quite some time back, but explained nicely, & it’s good to see that “how to add an extra query with a parameter” is explained. You could always have added the extra query in LightSwitch, as a query based on the table supplied by the RIA service, as I don’t think you’d be sacrificing performance, because LS queries are “composed”, so the same number of records would be returned either way. I could be wrong about that, I haven’t tested it for performance to see which is faster, if either is.

    A couple of points though.

    1. You don’t *have* to create a specific WCF RIA Services Class Project, any .NET class library will do. Good idea to keep all your custom queries in one project of course, it just doesn’t have to be a RIA Service class.

    2. You also don’t have to add a “Domain Class” (although doing so will add the correct assembly references for you. You *can* just write a class that inherits from DomainService. Once you’ve added one service class, it gets even easier, because you can just copy an existing one & modify it to suit.

    3. Although the “table class” has to be in the same *project* as the rest of the service classes, it doesn’t *have* to be *inside* the “service class”. It’s much cleaner to keep the “table class” in its own file.

    I hope that’s helpful?

    • hgminerva says:

      Thanks Yann for the comment, really appreciated it. Yes! I’ve been reading a lot including Eric’s article, it is so much help. Also I will take all your points into consideration for my next problem…external Oracle data, which I am stuck right now.

  2. Ruud Jeursen says:

    Thanks Harold for this post, I’m curently in Italy on vacation and as soon as I get back I’ll dive into it ! Thanks again ! Regards, Ruud Jeursen

  3. Elaine says:

    If you could send me an example WCF RIA Service that uses the Oracle Interface table that would be great…

  4. […] details anymore on the creation of a WCF RIA Service project, I have that documented in another post, but I will emphasize on modifying the Domain Service to accommodate two context.  But before you […]

  5. […] There is actually no change in creating a WCF RIA Service in Lightswitch 2012, just follow the guide I did in Lightswitch 2011 (How to create a VB WCF RIA Services for Microsoft Lightswitch to aggregate or join tables at the sam…). […]

  6. Jimi says:

    Hello,

    Thank you for this great post that helped me alot
    but i stiall have small problem that I cant pass a string parameter?
    how I can fix this problem

    • hgminerva says:

      Can you be specific what is the problem?

      • Jimi says:

        Hello
        when i make a function iWCF RIA like this:
        TestForPara(catname As Nullable(Of String)) As IQueryable(Of CategoriesExpense)

        I got this error:
        Error 7 Type ‘String’ must be a value type or a type argument constrained to ‘Structure’ in order to be used with ‘Nullable’ or nullable modifier ‘?’.

        but it works fine if i sue catUD as nullable(of integer)

        Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: