Pokey Comments - Answer Overflow

Pokey

Posts Comments

CC#

•Created by Pokey on 10/7/2023 in #help

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

The final paginated query is derived from this query with one additional filter + the SkipTake

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

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

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

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

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

... Mostly

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

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

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

Almost feels too fast

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

Wow that is.... very fast

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

haha, thanks

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

Wow I feel stupid

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

Its a list comparison

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

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

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

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.

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

so it'll go either direction

42 replies

CC#

•Created by Pokey on 10/7/2023 in #help

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

I have made a many to many nav on both Files and Labels

42 replies

CC#