CODEWITHSUNDEEP
sqlasp.netsql-serverentity-framework
grayson
grayson

Reputation: 1037

ASP.NET EF - SQL - How to get all rows in a table with Id in multiple rows

I may or may not be doing things correctly, so please feel free to offer a better approach if the question is not answerable or is, but things could be organized much better.

I have a class: Resources

public class Resources
{
    public int Id....
    ...... simplified for readability.
    public String Url......
    public DateTime publishDateTime....
}

I also have a list of Tags

public class Tags
{
    public int Id....
    ...... simplified for readability.
    public String Tagname......
}

This is a many to many so I have further class TagResources (simplified)

 public class TagResources
{
    public int TagResourceId....
    ...... simplified for readability.
    public int TagId......
    public int ResourceId......
}

Each Resource can have many (maximum 3) tags, however I want to be able to retrieve all resources where ALL the tags exist.

Example table:

+-----+----------------+--------------------+
+ Id  +   TagId        +      ResourceId    + 
+-----+----------------+--------------------+
+  1  +       1        +          1         +      
+-----+----------------+--------------------+
+  2  +       2        +          1         +      
+-----+----------------+--------------------+
+  3  +       3        +          1         +      
+-----+----------------+--------------------+
+  4  +       1        +          2         +      
+-----+----------------+--------------------+
+  5  +       2        +          2         +      
+-----+----------------+--------------------+
+  6  +       4        +          2         +      
+-----+----------------+--------------------+
+  7  +       1        +          3         +      
+-----+----------------+--------------------+
+  8  +       2        +          3         +      
+-----+----------------+--------------------+
+  8  +       3        +          3         +      
+-----+----------------+--------------------+
+  8  +       4        +          3         +      
+-----+----------------+--------------------+

So for example, I want get all resources that have tags 1, 2 and 3, it would return ResourceId of 1 and 3.

If I want to get all resources that have tags 1, 2 and 4, it would return ResourceId of 2 and 3.

So in essence, how can I get all Resources, where the corresponding tagId is in the selected list.

At the moment, I am getting all rows where the first tag is matched;

var resourceIds = _context.TagResources.Distinct()
        .Include(m => m.Tag)
        .Where(p => tags.Contains(p.Tag.Name)).Select(p => p.ResourceId);

There are a maximum of three unique tags per resource so I then using this to retrieve only those with the second tag and then those with the third tag and then adding them to a new list.

This is very convoluted and I am not very experienced in this area (as you can see). However in addition, whatever is left, I need to OrderBy publishDateTime and then Take(20).

Can anyone steer me in the right direction?

I am using ASP.NET MVC with EF, but any pointers with regards to the SQL would be helpful.

Upvotes: 1

Views: 94

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109271

This is not as straightforward as it looks. You can't use this query ...

var tagIds = new int[] { 1, 2, 3 };

var resources = _context.Resources
                        .Where(r => r.TagResources
                                     .All(tr => tagIds.Contains(tr.TagId)));

... because that only returns resources of which all tags are in tagIds, so resource 3 wouldn't qualify.

Instead, you have to find resources of which tagIds is contained by their tags:

var resources = _context.Resources
                        .Where(r => tagIds
                        .All(id => r.TagResources
                                    .Select(tr => tr.TagId)
                                    .Contains(id)));

You'll notice that this generates a disproportionate amount of SQL, because EF has to translate tagIds into a SQL table of sorts. However, with this limited amount of Id values this is absolutely no problem.

Upvotes: 1

Dave Greilach
Dave Greilach

Reputation: 895

You should be able to solve this using a group by. I created a class for TagResources and then created a list of it matching the data you provided.

var resources = new List<TagResource>()
{
    new TagResource() {Id = 1, TagId = 1, ResourceId = 1},
    new TagResource() {Id = 2, TagId = 2, ResourceId = 1},
    new TagResource() {Id = 3, TagId = 3, ResourceId = 1},
    new TagResource() {Id = 4, TagId = 1, ResourceId = 2},
    new TagResource() {Id = 5, TagId = 2, ResourceId = 2},
    new TagResource() {Id = 6, TagId = 4, ResourceId = 2},
    new TagResource() {Id = 7, TagId = 1, ResourceId = 3},
    new TagResource() {Id = 8, TagId = 2, ResourceId = 3},
    new TagResource() {Id = 9, TagId = 3, ResourceId = 3},
    new TagResource() {Id = 10, TagId = 4, ResourceId = 3},
};

Using that as my datasource I came up with this:

var input = new int[] { 1, 2, 3 }; // the ids to search for.  you may change these anyway you want

var results = resources.GroupBy(x => x.ResourceId, r => new { TagId = r.TagId })
    .Where(x => input.All(x.Select(r => r.TagId).Contains)); // performs the group by and filters out results that don't include all the keys

foreach (var result in results)
{
    Console.WriteLine(result.Key);
}

outputs:
//1
//3

Upvotes: 1

Related Questions