Alex Rivera | Logout

Linq to Entities many to many select query

Asked 2009-07-08T13:13:31.247
18

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

c# t-sql linq-to-entities
Edit
Report

1 Answer

0
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.
}
answered 2009-07-08T14:10:15.270

Your Answer