Linq to Entities many to many select query

c#, linq-to-entities, t-sql

Solution

This proved to be much simpler than it seemed. I've solved the problem using the following blogpost: http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx

The key to this solution is to apply the filter of the bandname on a subset of Bands of the musicstyle collection.

var result=(from m in _entities.MusicStyle 
            from b in m.Band
            where b.Name.Contains(search)
            select new {
                BandName = b.Name,
                m.ID,
                m.Name,
                m.Description
            });

notice the line

from b IN m.Band

This makes sure you are only filtering on bands that have a musicstyle.

Thanks for your answers but none of them actually solved my problem.

Problem

I am at a loss with the following query, which is peanuts in plain T-SQL. We have three physical tables: - Band (PK=BandId) - MusicStyle (PK=MuicStyleId) - BandMusicStyle (PK=BandId+MusicStyleId, FK=BandId, MusicStyleId) Now what I'm trying to do is get a list of MusicStyles that are linked to a Band which contains a certain searchstring in it's name. The bandname should be in the result aswell. The T-SQL would be something like this: ``` SELECT b.Name, m.ID, m.Name, m.Description FROM Band b INNER JOIN BandMusicStyle bm on b.BandId = bm.BandId INNER JOIN MusicStyle m on bm.MusicStyleId = m.MusicStyleId WHERE b.Name like '%@searchstring%' ``` How would I write this in Linq To Entities? PS: StackOverflow does not allow a search on the string 'many to many' for some bizar reason...

Original source