c# - LINQ: Select Post with multiple Tags -

i'm trying find posts contains selected tags.

my current code returning posts containing selected tags , posts containing of selected tags.

here function. appreciate help.

sample database structure

tables

[post] - id - title - body

[posttag] - id - postid - tagname

so [post] [posttag] got one-to-many relationship postid foreign key.

public static ienumerable<post> getpostcontainsalltags(ienumerable<string> _selectedtags,                                                             int numposts) {     using (mapleprimesdatacontext dc = new mapleprimesdatacontext(_connectionstring))     {          var tagposts = (from p in dc.posts                         join t in dc.posttags on p.id equals t.postid                         p.status == 1 && _selectedtags.contains(t.name)                         orderby p.dateadded descending                         select p).take(numposts).tolist();         return tagposts;     } } 

i change database structure not duplicate tag name in posttag table

this works , easy undestand:

var tagposts = dc.posts.where(post => post.status == 1);  foreach (var selectedtag in _selectedtags) {     tagposts = tagposts.where(post => post.posttags.any(tag => tag.name == selectedtag)); }  return tagposts.orderbydescending(p => p.dateadded).take(numposts).tolist(); 

this works , quite bit faster

var selectedtagids = dc.tags.where(tag => _selectedtags.contains(tag.name)).select(x => x.tagid);  var tagposts = dc.posts     .where(post => post.status == 1)     .where(post =>        !(from selectedtag in selectedtagids         join tag in post.posttags on selectedtag equals tag.tagid posttags         tag in posttags.defaultifempty()         tag.tagid == null         select 1).any());  return tagposts.orderbydescending(p => p.dateadded).take(numposts).tolist(); 

the difference here create database collection selectedtagids first , use left join (which ugly in linq -> https://msdn.microsoft.com/en-us/library/bb397895.aspx)

it works because if post doesn't have tag, left join of posts tags , selected tags have row without post tag.