Jak pobrać z tabeli A z kluczem obcym B wszystkie rekordy aby wiersze z kolumną z kluczem B się nie powtarzały?

Witam. Przykład:

Mam tabelę z przypisaniem formularzy.
Id, FormId

I wiersze:

1 - 3
2 - 4
3 - 2
4 - 2
5 - 4

Chcę pobrać wszystkie formularze (działając po FormId) ale w taki sposób, aby się nie powtarzały.
Czyli wynikiem powinno być:
3, 4, 2

Następnie będę chciał użyć Skip, Take i OrderBy, więc odpada mi możliwość użycia AsEnumerable i GroupBy (bo to by załatwiło sprawę)
Macie może jakieś pomysły? Wklejam skrócony i pełny kod który aktualnie mam

Skrócony - Tutaj niedozwolone jest użycie GroupBy

var filtered = _applicationDbContext.FormAssocciates
  .Include(x => x.ApplicationUser)
  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
  .GroupBy(x => x.FormId)
  .Select(x => x.First().Form);

Pełny kod
https://pastebin.com/HPMGcwVL

Czemu użycie GroupBy w tym fragmencie jest niedozwolone? Leci wyjątek?

EFCore w wersji młodszej niż 3.0 niejawnie używał AsEnumerable podczas GroupBy pobierając całą tabelę do pamięci. Potem zablokowali możliwość używania tej metody. Niestety byłem zmuszony utworzyć nową tabelę dla archiwów i wszystko działa dobrze. Ale fajnie byłoby posłuchać mądrzejszych jak takie problemy się rozwiązuje, bo nadal nie widzę takiej możliwości w EFCore :D

no jak zablokowali :P

using (var ctx = new Context())
{
    ctx.Database.EnsureDeleted();
    ctx.Database.EnsureCreated();

    var rnd = new Random();

    for (int i = 1; i <= 5; i++)
    {
        var ent = new Test { Id = i, FormId = rnd.Next(1, 6) };
        ctx.Tests.Add(ent);
    }

    ctx.SaveChanges();

    var items = ctx.Tests.ToList();

    Console.Clear();
    foreach (var item in items)
        Console.WriteLine(item);

    var grouped = ctx.Tests.GroupBy(x => x.FormId).ToList();

    Console.WriteLine();

    foreach (var group in grouped)
    {
        Console.WriteLine(group.Key);
        foreach (var item in group)
        {
            Console.WriteLine($"\t{item.Id}");
        }
    }
}

public class Context : DbContext
{
    public DbSet<Test> Tests { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine);
        optionsBuilder.UseSqlServer("Server=SQLEXPRESS;Database=4p_lol;Trusted_Connection=True;Encrypt=False;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Test>().Property(x => x.Id).ValueGeneratedNever();
    }
}

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

    public int FormId { get; set; }

    public override string ToString() => $"{Id} {FormId}";
}

Output:

1 4
2 2
3 2
4 3
5 4

Pogrupowane:

2
        2
        3
3
        4
4
        5
        1

SQL:

No to zmieńmy teraz zapytanie na:

var grouped = ctx.Tests
            .GroupBy(x => x.FormId)
            .Select(x => new { x.Key, Count = x.Count() })
            .ToList();

1 1
2 3
3 2
4 5
5 2

Pogrupowane:

1 : 1 count
2 : 2 count
3 : 1 count
5 : 1 count

Wygenerowany SQL:

Wybacz, że tak późno odpowiadam.
var items = ctx.Tests.ToList();
Dzięki tej linijce GroupBy zadziała, ale do pamięci wczyta się cała zawartość tabeli i dopiero potem jest wykonywana operacja lokalnie :) A mi zależy na tej operacji, aby była po stronie bazy. Chyba, że cos źle doczytałem :D

@gswidwa1:

przecież widać na screenach jaki leci SQL do servera :P

widzisz tego group by?

nawet jak wywale ten ToList to nadal lecą takie SQLe:

CREATE TABLE [Tests] (
    [Id] int NOT NULL,
    [FormId] int NOT NULL,
    CONSTRAINT [PK_Tests] PRIMARY KEY ([Id])
);

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Tests] ([Id], [FormId])
VALUES (@p0, @p1),
(@p2, @p3),
(@p4, @p5),
(@p6, @p7),
(@p8, @p9);
',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int',
@p0=1,@p1=4,@p2=2,@p3=2,@p4=3,@p5=5,@p6=4,@p7=4,@p8=5,@p9=3

SELECT [t].[FormId] AS [Key], COUNT(*) AS [Count]
FROM [Tests] AS [t]
GROUP BY [t].[FormId]

EF to nie jest znów aż taka magia i czary że trzeba zgadywać, wystarczy odpalić kod i wszystko wiadome ;)

Jeżeli dobrze rozumiem to problemem nie jest to zapytanie?

var filtered = _applicationDbContext.FormAssocciates
  .Include(x => x.ApplicationUser)
  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
  .GroupBy(x => x.FormId)
  .Select(x => x.First().Form);

ale zapytanie które wyglądałoby tak:

var filtered = _applicationDbContext.FormAssocciates
  .Include(x => x.ApplicationUser)
  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
  .GroupBy(x => x.FormId)
  .Select(x => x.First().Form)
  .OrderBy( ... )
  .Skip( ... )
  .Take( ... );

Bo próbując takie zapytanie dostaję wyjątek:

The LINQ expression 'DbSet<FormAssocciates>()
    .GroupBy(f => f.FormId)
    .Select(g => g
        .AsQueryable()
        .LeftJoin(
            inner: DbSet<Form>(), 
            outerKeySelector: e => EF.Property<int?>(e, "FormId"), 
            innerKeySelector: f0 => EF.Property<int?>(f0, "Id"), 
            resultSelector: (o, i) => new TransparentIdentifier<FormAssocciates, Form>(
                Outer = o, 
                Inner = i
            ))
        .LeftJoin(
            inner: DbSet<ApplicationUser>(), 
            outerKeySelector: e => EF.Property<int?>(e.Inner, "ApplicationUserId"), 
            innerKeySelector: a => EF.Property<int?>(a, "Id"), 
            resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<FormAssocciates, Form>, ApplicationUser>(
                Outer = o, 
                Inner = i
            ))
        .Select(e => IncludeExpression(
            EntityExpression:
            e.Outer.Inner, 
            NavigationExpression:
            e.Inner, ApplicationUser)
        )
        .First())
    .OrderBy(e0 => e0.Name)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Testowy kod:

using EFCorePractice.Context;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
optionsBuilder.UseSqlite("Data Source=database.db");

using var context = new AppDbContext(optionsBuilder.Options);

string queryWithoutOrderBy = context.FormAssocciates
                  .Include(x => x.ApplicationUser)
                  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
                  .GroupBy(x => x.FormId)
                  .Select(x => x.First().Form)
                  .ToQueryString();

Console.WriteLine(queryWithoutOrderBy);

//To się wywala:
string secondQuery = context.FormAssocciates
                  .Include(x => x.ApplicationUser)
                  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
                  .GroupBy(x => x.FormId)
                  .Select(x => x.First().Form)
                  .OrderBy(x => x.Name)
                  .Skip(20)
                  .Take(10).ToQueryString();

Console.WriteLine(secondQuery);



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

    public string Name { get; set; } = default!;

    public int? FormId { get; set; }

    public Form? Form { get; set; }

    public int? ApplicationUserId { get; set; }

    public ApplicationUser ApplicationUser { get; set; }
}

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

    public string Name { get; set; } = default!;

    public int? ApplicationUserId { get; set; }

    public ApplicationUser ApplicationUser { get; set; }
}

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

    public string Firstname { get; set; } = default!;

    public string Surname { get; set; } = default!;
}

public class AppDbContext : DbContext
{
    public DbSet<ApplicationUser> ApplicationUsers { get; set; }

    public DbSet<Form> Forms { get; set; }

    public DbSet<FormAssocciates> FormAssocciates { get; set; }

    public AppDbContext(DbContextOptions options)
        : base(options)
    {
    }
}

Jedna rzecz mnie zastanawiam bo masz:

context.FormAssocciates

ale potrzebujesz tak naprawdę Form:

.Select(x => x.First().Form)

Może dałoby się skonstruować inaczej to zapytanie, możesz opisać co ono powinno pobrać? Może gdyby zaczynało się od:

context.Form

byłoby łatwiej?

Bo dla zapytania:

string queryWithoutOrderBy = context.FormAssocciates
                  .Include(x => x.ApplicationUser)
                  .Include(x => x.Form).ThenInclude(x => x.ApplicationUser)
                  .GroupBy(x => x.FormId)
                  .Select(x => x.First().Form)
                  .ToQueryString();

dostaję takiego sql a możliwe że dałoby się to uprościć

SELECT "t0"."Id", "t0"."ApplicationUserId", "t0"."Name", "t0"."Id0", "t0"."Firstname", "t0"."Surname"
FROM (
    SELECT "f"."FormId"
    FROM "FormAssocciates" AS "f"
    GROUP BY "f"."FormId"
) AS "t"
LEFT JOIN (
    SELECT "t1"."Id", "t1"."ApplicationUserId", "t1"."Name", "t1"."Id0", "t1"."Firstname", "t1"."Surname", "t1"."FormId"
    FROM (
        SELECT "f1"."Id", "f1"."ApplicationUserId", "f1"."Name", "a"."Id" AS "Id0", "a"."Firstname", "a"."Surname", "f0"."FormId", ROW_NUMBER() OVER(PARTITION BY "f0"."FormId" ORDER BY "f0"."Id", "f1"."Id", "a"."Id") AS "row"
        FROM "FormAssocciates" AS "f0"
        LEFT JOIN "Forms" AS "f1" ON "f0"."FormId" = "f1"."Id"
        LEFT JOIN "ApplicationUsers" AS "a" ON "f1"."ApplicationUserId" = "a"."Id"
    ) AS "t1"
    WHERE "t1"."row" <= 1
) AS "t0" ON "t"."FormId" = "t0"."FormId"