Take one and skip other duplicate item in a child table

Question

I have a list of Items and every item have some list, Now I wants to select Distinct items of child. I have tried like below but it's not working.

var items = await _context.Items.
Include(i => i.Tags.Distinct()).
Include(i => i.Comments).
OrderBy(i => i.Title).ToListAsync();

//Tag items
TagId    -   tag
------------------
  1   ---   A 
  2   ---   B 
  3   ---   B 
  4   ---   C 
  5   ---   D 
  6   ---   D 
  7   ---   F
//Expected Result
Item.Tags -> [A,B,C,D,F]

how can I do this in EF Core? Thanks.

Answer 1

You can use the MoreLinq library to get DistinctBy or write your own using this post.

Then use this:

var items = await _context.Items.
    Include(i => i.Tags).
    Include(i => i.Comments).
    OrderBy(i => i.Title).
    DistinctBy(d => d.Tags.tag).
    ToListAsync();

You want to get distinct records based on one column; so that should do it.

Answer 2

Apparently you have a table of Items, where every Item has zero or more Tags. Furthermore the Items have a property Comments, of which we do not know whether it is one string, or a collection of zero or more strings. Furthermore every Item has a Title.

Now you want all properties of Items, each with its Comments, and a list of unique Tags of the items. Ordered by Title

One of the slower parts of database queries is the transport of the selected data from the database management system to your local process. Hence it is wise to limit the amount of data to the minimum you are really using.

It seems that the Tags of the Items are in a separate table. Every Item has zero or more Tags, every Tag belongs to exactly one item. A simple one-to-many relation with a foreign key Tag.ItemId.

If Item with Id 300 has 1000 Tags, then you know that every one of these 1000 Tags has a foreign key ItemId of which you know that it has a value of 300. What a waste if you would transport all these foreign keys to your local process.

Whenever you query data to inspect it, Select only the properties you really plan to use. Only use Include if you plan to update the included item.

So your query will be:

var query = myDbContext.Items
    .Where(item => ...)                // only if you do not want all items
    .OrderBy(item => item.Title)       // if you Sort here and do not need the Title
                                       // you don't have to Select it
    .Select(item => new
    {   // select only the properties you plan to use
        Id = item.Id,
        Title = item.Title,
        Comments = item.Comments,      // use this if there is only one item, otherwise
        Comments = item.Comments       // use this version if Item has zero or more Comments
           .Where(comment => ...)      // only if you do not want all comments
           .Select(comment => new
           {   // again, select only the Comments you plan to use
               Id = comment.Id,
               Text = comment.Text,

               // no need for the foreign key, you already know the value:
               // ItemId = comment.ItemId,
           })
           .ToList();

           Tags = item.Tags.Select(tag => new
           {   // Select only the properties you need
               Id = tag.Id,
               Type = tag.Type,
               Text = tag.Text,

               // No need for the foreign key, you already know the value
               // ItemId = tag.ItemId,
           })
           .Distinct()
           .ToList(),
     });

var fetchedData = await query.ToListAsync();

Answer 3

I haven't tried it, but I'd say you put .Distinct() in the wrong place.

var items = await _context.Items
        .Include(i => i.Tags)
        .Include(i => i.Comments).
        .OrderBy(i => i.Title)
        .Select(i => { i.Tags = i.Tags.GroupBy(x => x.Tag).Select(x => x.First()); return i; })
        .ToListAsync();