Linq to Entities many to many select query

Question

Hi,

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...

Answer 1

In Linq, actually you don't need to write anything, if you define the relation in the diagram in SQL database, and generated using the utility, the object hierarchy is built automatically. That means, if you do:

var bands = from ms in db.MusicStyle
            let b = ms.Bands
            where b.Name.Contains(SEARCHSTRING)
            select new {
                b.Name, ms.Name, 
                ms.ID, ms.Description};

If you look into the generated classes of entities, the BandMusicStyle should not appear as LINQ to Entities consider that Band and MusicStyle are many to many and that table is not necessary.

See if it works?

Answer 2

from ms in Context.MusicStyles
where ms.Bands.Any(b => b.Name.Contains(search))
select ms;

This just returns the style, which is what your question asks for. Your sample SQL, on the other hand, returns the style and the bands. For that, I'd do:

from b in Context.Bands
where b.Name.Contains(search)
group b by band.MusicStyle into g
select new {
    Style = g.Key,
    Bands = g
}

from b in Context.Bands
where b.Name.Contains(search)
select new {
    BandName = b.Name,
    MusicStyleId = b.MusicStyle.Id,
    MusicStyleName = b.MusicStyle.Name,
    // etc.
}

Answer 3

You could do the above, but that will bring back all your results once you start to iterate over them and the filtering is done in-memory rather than on the db.

I think what you're looking for is just a few joins?

var q = from b in db.Bands
join bm in db.BandMusicStyle on on b.BandId equals bm.BandId
join ms in db.MusicStyle on bm.MusicStyleId equals m.MusicStyleId
where b.Name.Contains(searchString)
select new { b.Name, ms.ID, ms.Name, ms.Description };

or something to this effect anyway

Answer 4

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.