Linq method - Return rows only found in another table

Question

I want to return the Tags from the Tag table that are only found in the TagRecipe table. How can I do this?

            var dataTags = await _context.Tags
                .Include(tc => tc.TagCategory)
                .ToListAsync();

    public class Tag
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public ICollection<TagRecipe> TagRecipes { get; set; }

        public int TagCategoryID { get; set; }
        public TagCategory TagCategory { get; set; }
    }

    public class TagRecipe
    {
        public int TagId { get; set; }
        public int RecipeId { get; set; }
        public Tag Tag { get; set; }
        public Recipe Recipe { get; set; }
    }

Thank you

Could you pls post both table model s Tag and TagRecipe? – Serge Jul 22 '21 at 00:25 — Serge, Jul 22 '21 at 00:25

Serge · Answer 1 · 2021-07-22T02:29:53.787

3

Try this

 var dataTags = await _context.TagRecipe
                .Include(tc => tc.Tag.TagCategory)
                .Select(i=> i.Tag)
                .ToListAsync();

or you can use this syntax if you like it more

var dataTags = await _context.TagRecipe
                .Include(t => t.Tag)
                .ThenInclude(tc => tc.TagCategory)
                .Select(i=> i.Tag)
                .ToListAsync();

edited Jul 22 '21 at 02:29

answered Jul 22 '21 at 00:34

Serge

40,935
4
18
45

Serge: I believe you're missing a `.Distinct`. Imagine a case where join table `TagRecipe` contains 5 records with TagId = 1. – Roar S. Jul 22 '21 at 03:32

Roar S. · Answer 2 · 2021-07-22T14:01:48.797

An alternative starting at table Tags using Join that will return a result without duplicates.

var dataTags = db.Tags
    .Join(db.TagRecipes, tag => tag.Id, tagRecipe => tagRecipe.TagId, (tag, tagRecipe) => tag)
    .Include(tag => tag.TagCategory)
    .ToLookup(tag => tag.Id) // client-side from here
    .Select(grouping => grouping.First()) // to make distinct
    .ToList();

Will generate a straight-forward SQL

SELECT "t"."Id", "t"."Name", "t"."TagCategoryId", "t1"."Id", "t1"."Name"
FROM "Tags" AS "t"
INNER JOIN "TagRecipes" AS "t0" ON "t"."Id" = "t0"."TagId"
INNER JOIN "TagCategories" AS "t1" ON "t"."TagCategoryId" = "t1"."Id"

It is possible to use .Distinct in the above expression for removing duplicates instead of using grouping, but that will create a more complex SQL.

Table TagRecipes seems to be a join table in a many-to-many between table Tags and table Recipes. The latter is not included in the question, but I added it during my tests.

Please note that in EF Core 5, many-to-many relations may be created without an entity class for the join table.

Linq method - Return rows only found in another table

2 Answers2