C#•14mo ago

❔ EF Core LINQ - Many to Many Search with && and ||

Hi there, I have a 3 table structure for file storage that I would like to search, consisting of the following:

public class LabelEntity
{
  public uint Id { get; set; }
  public string Name { get; set; }
}

public class FileEntity
{
  public uint Id { get; set; }
  public string Md5Sum { get; set; }
  public string Name { get; set; }
  public string Location { get; set; }
}

public class FileLabelMappingEntity
{
  public uint Id { get; set; }
  public uint LabelId { get; set; }
  public LabelEntity Label { get; set; }
  public uint FileId { get; set; }
  public FileEntity File { get; set; }
}

public class LabelEntity
{
  public uint Id { get; set; }
  public string Name { get; set; }
}

public class FileEntity
{
  public uint Id { get; set; }
  public string Md5Sum { get; set; }
  public string Name { get; set; }
  public string Location { get; set; }
}

public class FileLabelMappingEntity
{
  public uint Id { get; set; }
  public uint LabelId { get; set; }
  public LabelEntity Label { get; set; }
  public uint FileId { get; set; }
  public FileEntity File { get; set; }
}

I would like to search files by assigned labels. I want to provide AND and OR groups for this search, so a search by LabelEntity.Id might look like so: (1 || 2) && (5 || 7 || 12) && 17 && (4 || 6) I have struggled for a while to work out an efficient way to do this. I will always have the IDs of the labels I want to search by as provided in the example, those are looked up beforehand. I'd like to formulate this into a fast, efficient linq query(s)

19 Replies

Angius•14mo ago

First and foremost, provide a navigation property from File to FileLabel, or even just to Label directly Then you can go through that

PokeyOP•14mo ago

This is actually just a cut down example There's actually some navigations for these

Angius•14mo ago

That's good

PokeyOP•14mo ago

The actual objects at a lot bigger so I just wrote up some super basic examples to give people an idea of the data structure

Angius•14mo ago

You'll probably need something like

var files = await _ctx.Files
    .Where(f => f.FileLabels
        .Select(fl => fl.LabelId)
        .Any(id => /* Yeah good question, actually */)
    .ToListAsync();

var files = await _ctx.Files
    .Where(f => f.FileLabels
        .Select(fl => fl.LabelId)
        .Any(id => /* Yeah good question, actually */)
    .ToListAsync();

PokeyOP•14mo ago

😂 The problem is having both && and || together which complicates it even more

Angius•14mo ago

var files = await _ctx.Files
    .Select(f => new { 
        File = f, 
        Ids = f.FileLabels.Select(fl => fl.LabelId) })
    .Where(x => x.Ids.Any(id => id == 1 || id == 2))
    .Where(x => x.Ids.Any(id => id == 5 || id == 6 || id == 12))
    .Where(x => x.Ids.Any(id => id == 4 || id == 6))
    .Select(x => x.File)
    .ToListAsync();

var files = await _ctx.Files
    .Select(f => new { 
        File = f, 
        Ids = f.FileLabels.Select(fl => fl.LabelId) })
    .Where(x => x.Ids.Any(id => id == 1 || id == 2))
    .Where(x => x.Ids.Any(id => id == 5 || id == 6 || id == 12))
    .Where(x => x.Ids.Any(id => id == 4 || id == 6))
    .Select(x => x.File)
    .ToListAsync();

Only this comes to mind

PokeyOP•14mo ago

hmmmm

Angius•14mo ago

Or you could make all those wheres into one, ig

var files = await _ctx.Files
    .Select(f => new { 
        File = f, 
        Ids = f.FileLabels.Select(fl => fl.LabelId) })
    .Where(x => 
        x.Ids.Any(id => id == 1 || id == 2) && 
        x.Ids.Any(id => id == 5 || id == 6 || id == 12) &&
        x.Ids.Any(id => id == 4 || id == 6))
    .Select(x => x.File)
    .ToListAsync();

var files = await _ctx.Files
    .Select(f => new { 
        File = f, 
        Ids = f.FileLabels.Select(fl => fl.LabelId) })
    .Where(x => 
        x.Ids.Any(id => id == 1 || id == 2) && 
        x.Ids.Any(id => id == 5 || id == 6 || id == 12) &&
        x.Ids.Any(id => id == 4 || id == 6))
    .Select(x => x.File)
    .ToListAsync();

PokeyOP•14mo ago

Lemme see if I have sufficiently butchered this solution in a way that makes your answer not feasable and our time in this chat even more painful I have! That's nice I don't actually have a direct navigation from File to Label and vice versa builder.HasMany(e => e.Labels).WithMany(f => f.Files); Is that actually good enough? 🤔 It doesn't have a way to navigate to that through the mapping entity and I don't know if it'll work that out itself Oh no I am just a professional idiot. It has a .UsingEntity<T>

Angius•14mo ago

I mean, this will work as well Your select for the label IDs will just be different f.Labels.Select(l => l.Id)

PokeyOP•14mo ago

I have made a many to many nav on both Files and Labels so it'll go either direction Could I please bug you for the best way to handle the ORs with this? I actually have a list of IDs to OR together and my only thought so far is to do an intersect with an any but I don't know how efficient/well that'd translate.

Angius•14mo ago

You could use .Contains()

PokeyOP•14mo ago

Its actually a list of lists of IDs to OR, so I am adding a .Where for each group Its a list comparison

Angius•14mo ago

x => x.Ids.Any(id => firstList.Contains(id))

x => x.Ids.Any(id => firstList.Contains(id))

PokeyOP•14mo ago

Wow I feel stupid haha, thanks Wow that is.... very fast Almost feels too fast Thank you so much @ZZZZZZZZZZZZZZZZZZZZZZZZZ that works extremely well. My old buggy search took 8 seconds to process 430K rows. This takes 700ms! Hopefully that actually is all I needed, and I don't need to come back with a caveat

Angius•14mo ago

Three orders of magnitude, that's what I call an improvement lol

PokeyOP•14mo ago

... Mostly Yes, the answer is yes it has helped a lot. For some reason though this SQL is produced when I just do a .Count()

SELECT
       COUNT(*)::INT
FROM
     "Files" AS f
WHERE
    EXISTS (
        SELECT 1 FROM
        "FileLabelMappings" AS f0
    INNER JOIN
        "Labels" AS l
    ON
        f0."LabelId" = l."Id"
    WHERE
          (f."Id" = f0."FileId")
    AND NOT 
        l."Id" = ANY ('{13573,23371,5417,12481,9712,43931,3390,63396,32105,13571,20927,14431,15360,16919}')
        AND
        ((l."Id" = ANY ('{13573,23371,5417,12481,9712,43931,3390,63396,32105,13571,20927,14431,15360,16919}') IS NOT NULL)))
    )

SELECT
       COUNT(*)::INT
FROM
     "Files" AS f
WHERE
    EXISTS (
        SELECT 1 FROM
        "FileLabelMappings" AS f0
    INNER JOIN
        "Labels" AS l
    ON
        f0."LabelId" = l."Id"
    WHERE
          (f."Id" = f0."FileId")
    AND NOT 
        l."Id" = ANY ('{13573,23371,5417,12481,9712,43931,3390,63396,32105,13571,20927,14431,15360,16919}')
        AND
        ((l."Id" = ANY ('{13573,23371,5417,12481,9712,43931,3390,63396,32105,13571,20927,14431,15360,16919}') IS NOT NULL)))
    )

Which takes 8-15 seconds to execute and those strings looks cursed AF I have a feeling the fact I do a .Skip().Take() for pagination for the main page query helps a lot, but this query is not paginated (gets total result count) and basically kills everything The final paginated query is derived from this query with one additional filter + the SkipTake

Accord•14mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF Core LINQ - Many to Many Search with && and ||