dimanche 28 juin 2015

ASP.NET Query database column with array of accepted keys

I've reached a problem with query the database correctly. I can't find it answered anywhere else.

My datamaodel contains three tables one with the Image data and another with tags. The third table joins the two tables creting a many to many relationship:

[Image] - 1 --- * ->[ImageTagJoin] <- * --- 1 - [Tag]

From a user input of a set of tags I want to get every image contaning all of the tags. The following code works for one tag

 viewModel.Tags = db.Tags.Where(
                            s =>
                                s.Name == searchString)
                         .Include(i => i.Images.Select( ii => ii.Image));
 viewModel.Images = t.Images.Select(x => x.Image);

The only solution I have is if, input is tag1, tag2, tag3, the controller iterated over each tag.

  1. Get set of images with the tag tag1
  2. From that set of images of tag1 get the subset of images with tag2
  3. From that set get the subset of images with tag3

Now I have a set of images with the tags tag1, tag2 and tag3. Though that this solution works but not as elegant because it requires to search the database once for every tag and every image row gets looked up for every tag in the input.

Conclusion: How can I query a many-to-many relationship between images and tags, where I select all images that have the subset of n tags given from the user.

Thanks.

Aucun commentaire:

Enregistrer un commentaire