5

I have two tables Service and Provider. Between them is a joining table ServiceProvider which has only 2 fields - one for each of the two PKs. When added to the edmx the joining many-to-many table is abstracted away and can't be seen (as expected).

This is all fine except when I want to get Providers based on a given service. From this question:

it looks like the answer would be simply:

var query = from p in entities.Providers
            from s in entities.Services
            where s.Id == 15
            select p;

but this returns ALL providers. What am I doing wrong here?

Community
  • 1
  • 1
finoutlook
  • 2,523
  • 5
  • 29
  • 43
  • Does `s` not have a `Providers` property you can use? – Rawling Jun 21 '12 at 11:03
  • Yes s.Providers is a collection of Providers, but I couldn't get that to work as part of the select either. – finoutlook Jun 21 '12 at 11:09
  • @abatishchev - thanks for edits but won't changing the title make the question harder to find if someone searches for entity framework many-to-many? (I realise its EF defined by the tag, but will a search pick up on that) – finoutlook Jun 21 '12 at 11:23
  • @finoutlook: You're always free to change the title back, my edit is just a suggestion, as to do not put tags in the beginning of a title, but if you want - make it more in a literary style. – abatishchev Jun 21 '12 at 13:26

4 Answers4

4
var query = entities.Providers.FirstOrDefault(p => p.Id == 15).Services.ToList();
abatishchev
  • 98,240
  • 88
  • 296
  • 433
halit
  • 1,128
  • 1
  • 11
  • 27
  • I think you've got your `Providers` and `Services` the wrong way around. – Rawling Jun 21 '12 at 11:05
  • Yeah it was providers I was trying to output, but I see its symmetrical anyway – finoutlook Jun 21 '12 at 11:15
  • 2
    @finoutlook: The correct solution is in Maciej Dopieralski answer. The code in this answer here will 1) crash when there is no provider with Id=15, 2) crash if you are using POCOs and don't have enabled lazy loading, 3) create two database queries instead of only one. Rawling's answer has the same flaws. – Slauma Jun 21 '12 at 13:53
2

Try this:

var res = from s in entities.Services
          where s.Id == 15
          select s.Provider;

EDIT

Corrected and tested the query on real-life model and data. It works now.

Maciej
  • 7,871
  • 1
  • 31
  • 36
  • That's not going to work in a many to many relationship, as collection types usually don't have an `Id` property. – Nuffin Jun 21 '12 at 11:07
2

Isn't it as simple as

var matchingProviders = entities.Services.Single(s=>s.Id==15).Providers;
Rawling
  • 49,248
  • 7
  • 89
  • 127
  • This works. I'm still confused why the other question I mentioned got upvotes - and this post has the same 'double from' suggestion: http://smehrozalam.wordpress.com/2010/06/29/entity-framework-queries-involving-many-to-many-relationship-tables/ – finoutlook Jun 21 '12 at 11:13
  • @finoutlook I'm not very familiar with the entity framework, but if this works - and it doesn't seem too farfetched for it to work - then it works :) – Rawling Jun 21 '12 at 11:17
  • halit got answer in first, which is very similar (albeit the selecting the wrong side) - but don't know if edits were after your answer – finoutlook Jun 21 '12 at 11:20
  • @fin I wouldn't have assumed this worked and posted my answer if I hadn't seen halit's, so I won't be put out if you accept his :) (Although I prefer my use of `Single` to `FirstOrDefault` too.) – Rawling Jun 21 '12 at 11:22
  • I'll accept earlier answer, thanks for the help though I'll upvote – finoutlook Jun 21 '12 at 11:25
  • @Rawling: I didn't downvote but reason for the downvote might be that your solution will create *two* database queries and doesn't work at all if lazy loading is not enabled. Maciej Dopieralski's answer solves both problems. – Slauma Jun 21 '12 at 13:58
-1

You may try using join, like so:

entity.Providers.Join(entity.Services, c => c.ID, p => p.ID,(c, p) => new { Providers= c, Services= p })
Anthony
  • 12,177
  • 9
  • 69
  • 105
Boomer
  • 1,468
  • 1
  • 15
  • 19