Share via

LightSwitch OData Tip: Easily Expose Many-to-Many Relationships using Queries

  • Article

The other day I got an email from a good friend trying to expose a many-to-many relationship via his LightSwitch OData service. He was trying to recreate a feed similar to the one at https://odata.msteched.com/teau12/sessions.svc/ using LightSwitch. What he wanted to see was a list of all the speakers for a given session, but there’s a many-to-many relationship between sessions and speakers. Since this feed supports direct many-to-many relationships we can pull up all the speakers for a given session using navigation properties:

https://odata.msteched.com/teau12/sessions.svc/Sessions(27981)/Speakers

Since LightSwitch currently doesn’t support direct many-to-many relationships in its intrinsic (i.e. ApplicationData)database we need to support this scenario a different way. And I should make it clear, LightSwitch will support many-to-many relationships when consuming external data sources (like the feed above), it just doesn’t support modeling the data directly this way when creating a data model through the data designer at this time.

The Data Model

OK first we need to model the data in LightSwitch. In order to model a many-to-many relationship, you need a linking table. Here we have SpeakerSession table that holds the many-to-one relationships to both Speaker and Session.

 

image

Now if I create a List & Details screen, choose the Session for the screen data, and include the SpeakerSession, LightSwitch will automatically bring in the Speakers as drop down lists in the SpeakerSession grid. So the screens do the right thing automatically. (BTW, if you’re trying to design a screen with a multi-select control have a look at Andy’s article here: How to Create a Many-to-Many Relationship)

However, if we take a look at the OData service LightSwitch creates for us, when we look at a Session, you will notice that we need to traverse the SpeakerSession linking table to find each of the Speakers.

https://…/ApplicationData.svc/Sessions(1)/SpeakerSessions

image

So to get the speakers for session 1 we have to make two calls.

https://…/ApplicationData.svc/SpeakerSessions(1)/Speaker
https://…/ApplicationData.svc/SpeakerSessions(5)/Speaker

Instead we only want to have to make 1 call to get all the speakers for a given session.

Create a Query

The trick is to create a query. Queries are also exposed on your OData service endpoint. For instance, we can open the Query Designer and create a query called SpeakersBySession based on the Speakers entity. Add a parameter for the SessionID.

image

Now drop down the Write Code button and add code to the SpeakersBySession_PreprocessQuery method. We need to write a LINQ query that will check the Session ID and return only those speakers that match.

 Private Sub SpeakersBySession_PreprocessQuery(SessionID As System.Nullable(Of Integer),
                ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Speaker))

    'Return the speakers that have sessions matching the passed in Session ID
    query = From speaker In query
            Where speaker.SpeakerSessions.Any(Function(s) s.Session.Id = SessionID)
            Select speaker
End Sub

BTW, in C# the LINQ statement would be:

 query = from speaker in query
        where speaker.SpeakerSessions.Any(s => s.Session.Id == SessionID)
        select speaker;

RUN IT!

Now we can call the query directly via the service and it will return the list of Speakers for a given session.

<https://…/ApplicationData.svc/SpeakersBySession?SessionID=1>

image

Of course you can expose any simple or complex queries you want this way, not just many-to-many relationships. You can also limit who can execute any of the queries using the access control hooks in the Query designer as well.

For more information on OData & LightSwitch in Visual Studio 2012 see:

For more information on queries see:

And for more information on writing LINQ queries see:

Enjoy!

Comments

  • Anonymous
    February 20, 2013
    Thanks Beth, very useful information provided ;)

  • Anonymous
    August 28, 2013
    Are there plans to implmenet many to many and/or one to one relationships in the intrinsic data modeler in the future?

  • Anonymous
    September 15, 2014
    Thanks Beth for this great article. I am trying to expose a OData service  through the LightSwitch EDM based on a SQL VIEW. The problem I am facing it contains a big dataset (needed to provide Power Query / Power Pivot), like100.000+ rows. I tried to use the query technique above, but get a weird performance when applying the query parameter. It seems like LS first gets the whole resultset (millions of records) and then in-memory applies the WHERE filter in the LINQ expression. Is that correct? If so, this will be a problem for us. Is there any other way to have LS read SQL output buffered and write http response buffered? Thanks for any suggestion in the right direction, Bas