How to filter many-to-many related data in Microsoft Lightswitch?

If you are having a problem in filtering a many-to-many related data in your Microsoft Lightswitch project, don’t worry I have a simple solution for you.  Here it goes.

b1

Lets say you have a table structure like the one above and you want to filter off the Task (sysTask) of a chosen User (mstUser).  As you can see, a User can have many Tasks while a Task can have many Users, it is a Many-to-Many related table so to speak.  Many of the solutions to this problem in the Internet is of course through WCF RIA Service but I have a simpler way.

First.  Make a query in your SysTask table as shown in the image below.  Add a parameter only, in my example, the parameter is UserId, an Integer.

b2

Second.  Write a code in the PreprocessQuery.

b3

Below is the sample code. Basically it is a complex LINQ code the counts the user’s task record based on the user screen table (mstUserScreen) a shown in the database diagram.

        Private Sub SysTasksFiltered_PreprocessQuery(UserId As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.sysTask))
            query = From q In query
                    Where q.sysScreen.mstUserScreens.Where(Function(u) u.mstUser.Id = UserId).Count() > 0
        End Sub

Thats it! To use the query, just add it as a Data Property to your screen and tie up the parameter to any controls that contains the equivalent data, in our example it’s UserId.

Below is my sample screen in action.

b4

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
2 comments on “How to filter many-to-many related data in Microsoft Lightswitch?
  1. Alex says:

    Thanks for sharing! Simple and to the point. Works like a charm! Great article!

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: