LINQ crossroads of many-to-many

Question

I am trying to query Posts based on the Tags list:

 public class Post { public int? Id {get;set;} public string Name {get;set;} public virtual ICollection<Tag> Tags {get;set;} } public class Tag { public int? Id {get;set;} public string Name {get;set;} public vritual ICollection<Post> Posts {get;set;} } 

Now I want to return messages based on the tag list: IList<Tag> searchTags = ParseTagsFromSearchString("tag1,tag2,tag3"); // this function checks the tags in the database, so all the primary keys are available in the list IList<Tag> searchTags = ParseTagsFromSearchString("tag1,tag2,tag3"); // this function checks the tags in the database, so all the primary keys are available in the list

If the message contains one or more tags that also exist in searchTags , it should be included in the result. I tried the following:

 var q = from s in Context.Registrations where s.Tags.Intersect(tagList) select s; 

Error: Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<Models.Tag>' to 'bool'

 var q = from s in Context.Registrations where s.Tags.Any(t => tagList.Any(t2 => t.Id.Value == t2.Id.Value)) select s; 

Runtime Error: NotSupportedException: Unable to create a constant value of type 'Models.Tag'. Only primitive types ('such as Int32, String, and Guid') are supported in this context. NotSupportedException: Unable to create a constant value of type 'Models.Tag'. Only primitive types ('such as Int32, String, and Guid') are supported in this context. Any ideas?

- January 4th update: Answers indicate the correct solution, but in my code I still have a NotSupportedException. Is it possible that this value is nullable integer, since it is not a primitive type?

Answer 1

Today I again ran into this problem and decided to solve it. The problem with my question is that IList<Tag> searchTags = ParseTagsFromSearchString("tag1,tag2,tag3"); creates a list that throws an exception when used in the expression of intersection of another query in the entity structure. Do it right:

 var q = ParseTagsFromSearchString("tag1,tag2,tag3"); // this function will now build a query instead of a list IList<Post> posts = (from s in Context.Posts where s.Tags.Intersect(q.AsEnumerable()).Any() select s).ToList(); 

Answer 2

You are almost there, just change Intersect(taglist) to Intersect(taglist).Any()

Here is a working example (following your definitions for Post and Tag ):

 Tag tag1 = new Tag() { Id = 1, Name = "tag1" }; Tag tag2 = new Tag() { Id = 2, Name = "tag2" }; Tag tag3 = new Tag() { Id = 3, Name = "tag3" }; List<Post> posts = new List<Post>() { new Post() { Id = 1, Name = "post1", Tags = new Tag[] {tag1} }, new Post() { Id = 2, Name = "post2", Tags = new Tag[] {tag2} }, new Post() { Id = 3, Name = "post3", Tags = new Tag[] {tag3} }, new Post() { Id = 4, Name = "post13", Tags = new Tag[] {tag1, tag3} }, }; List<Tag> searchTags = new List<Tag>() { tag1, tag2 }; IEnumerable<Post> matching = posts.Where(p => p.Tags.Intersect(searchTags).Any()); //matching now contains post1, post2 and post13 

now, in real code, you probably won’t use the same instances for tags in the search list and in messages, so you have to override Equals and GetHashCode for the tag or provide IEqualityComparer in the Intersect call

Answer 3

Try converting your TagList to a list of integer identifiers. Use this in your request to fix a NotSupportedException.

 var tagIds = tagList.Select(x=>x.Id); 

Then use tagIds in your request ...

 var q = from s in Context.Registrations where s.Tags.Any(t => tagIds.Any(t2 => t.Id.Value == t2.Id)) select s; 

I'm not sure what nesting is. All such statements will work. Just explaining why you get the exception.

Answer 4

Maybe this might work?

 var q = from s in Context.Registrations where s.Tags.Intersect(tagList).Count() != 0 select s; 

or maybe

 var q = from s in Context.Registrations where s.Tags.Any(t => tagList.Contains(t)) select s; 

I have not tried any of this, so no guarantees :)

Answer 5

Add this together in LinqPad :

 void Main() { var tag1 = new Tag { Name = "tag1" }; var tag2 = new Tag { Name = "tag2" }; var tag3 = new Tag { Name = "tag3" }; var posts = new List<Post> { new Post { Name = "Post1", Tags = new List<Tag> { tag1, tag3 } }, new Post { Name = "Post2", Tags = new List<Tag> { tag2, tag3 } } }; var tagList = new List<Tag> { tag1 }; var q = posts.Where(x => x.Tags.Intersect(tagList).Any()); q.Dump(); } public class Post { public int? Id {get;set;} public string Name {get;set;} public virtual ICollection<Tag> Tags {get;set;} } public class Tag { public int? Id {get;set;} public string Name {get;set;} public virtual ICollection<Post> Posts {get;set;} }