Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for .NET developers. Learn more about Beth.
More videos »
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 http://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:
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.
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.
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.
So to get the speakers for session 1 we have to make two calls.
Instead we only want to have to make 1 call to get all the speakers for a given session.
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.
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)
BTW, in C# the LINQ statement would be:
query = from speaker in query
where speaker.SpeakerSessions.Any(s => s.Session.Id == SessionID)
Now we can call the query directly via the service and it will return the list of Speakers for a given session.
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:
Thanks Beth, very useful information provided ;)
Are there plans to implmenet many to many and/or one to one relationships in the intrinsic data modeler in the future?
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