C#•16mo ago

❔ EFC Is not loading collection properly

I have the following 2 models in my database, EFC does not seem to be loading it at all for some reason

public record ChatUser : IEntityTypeConfiguration<ChatUser>
{
    public Guid Id { get; set; }
    
    public required string Username { get; set; }
    public required string Email { get; set; }
    public required string HashedPassword { get; set; }
    
    public Guid? RegistrationToken { get; set; }
    public Guid? PasswordResetToken { get; set; }
    
    public virtual ICollection<ChatChannel> Channels { get; set; } = new HashSet<ChatChannel>();
    public virtual ICollection<ChatRelationship> Relationships { get; set; } = new HashSet<ChatRelationship>();

    public void Configure(EntityTypeBuilder<ChatUser> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
    }
}


public record ChatRelationship : IEntityTypeConfiguration<ChatRelationship>
{
    public Guid Id { get; set; }
    
    public required Guid SelfId { get; set; }
    public ChatUser Self { get; set; }
    
    public required Guid OtherUserId { get; set; }
    public ChatUser OtherUser { get; set; }

    public required ChatRelationShipType Type { get; set; } = ChatRelationShipType.None;

    public void Configure(EntityTypeBuilder<ChatRelationship> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
        
        builder.HasKey(f => new { User1Id = f.SelfId, User2Id = f.OtherUserId });

        builder.HasOne(x => x.Self)
            .WithMany(x => x.Relationships)
            .HasForeignKey(x => x.SelfId)
            .OnDelete(DeleteBehavior.Restrict);
        
        builder.HasOne(x => x.OtherUser)
            .WithMany()
            .HasForeignKey(x => x.OtherUserId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

public record ChatUser : IEntityTypeConfiguration<ChatUser>
{
    public Guid Id { get; set; }
    
    public required string Username { get; set; }
    public required string Email { get; set; }
    public required string HashedPassword { get; set; }
    
    public Guid? RegistrationToken { get; set; }
    public Guid? PasswordResetToken { get; set; }
    
    public virtual ICollection<ChatChannel> Channels { get; set; } = new HashSet<ChatChannel>();
    public virtual ICollection<ChatRelationship> Relationships { get; set; } = new HashSet<ChatRelationship>();

    public void Configure(EntityTypeBuilder<ChatUser> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
    }
}


public record ChatRelationship : IEntityTypeConfiguration<ChatRelationship>
{
    public Guid Id { get; set; }
    
    public required Guid SelfId { get; set; }
    public ChatUser Self { get; set; }
    
    public required Guid OtherUserId { get; set; }
    public ChatUser OtherUser { get; set; }

    public required ChatRelationShipType Type { get; set; } = ChatRelationShipType.None;

    public void Configure(EntityTypeBuilder<ChatRelationship> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
        
        builder.HasKey(f => new { User1Id = f.SelfId, User2Id = f.OtherUserId });

        builder.HasOne(x => x.Self)
            .WithMany(x => x.Relationships)
            .HasForeignKey(x => x.SelfId)
            .OnDelete(DeleteBehavior.Restrict);
        
        builder.HasOne(x => x.OtherUser)
            .WithMany()
            .HasForeignKey(x => x.OtherUserId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

77 Replies

S-IERRAOP•16mo ago

Possibly what I assume could be happening is its trying to search by "SelfId" when the key is saved to "OtherUserId" ? tho the behaviour I'm expecting is its fetching any table that contains the value, both are keys so it shouldn't be a problem unless thats not how it works and its only searching by the 1st key

JakenVeina•16mo ago

EFC does not seem to be loading it at all for some reason

define this

Angius•16mo ago

1. Don't use lazy loading 2. .Include() the related data you need 3. Or better yet, .Select() what you need

S-IERRAOP•16mo ago

I was told to use lazy loading any idea why I shouldn’t do it? I ve tried doing with include but same outcome

S-IERRAOP•16mo ago

Relationships shouldn’t be 0

Angius•16mo ago

Because lazy loading will query the database multiple times So why do that, if you can query it once for all the data you need

JakenVeina•16mo ago

in a difficult to predict way with thread blocking

S-IERRAOP•16mo ago

I see, I can switching to load regularly that’s not an issue I guess, tho no idea why it still isn’t being loaded at all

Angius•16mo ago

Since it's lazy-loaded, the related data will only ever be queried for when you access that property So, do you?

S-IERRAOP•16mo ago

I would believe so

S-IERRAOP•16mo ago

Angius•16mo ago

Do you have UseLazyLoadingProxies() in your EF config?

S-IERRAOP•16mo ago

Angius•16mo ago

https://learn.microsoft.com/en-us/ef/core/querying/related-data/lazy I feel dirty posting this link

S-IERRAOP•16mo ago

Haha why

Angius•16mo ago

Because I'm actively helping someone make lazy loading work Instead of discouraging them from using this shit

S-IERRAOP•16mo ago

I should of probably informed my self a bit more on this, just this is the way I've been taught the entire time

Angius•16mo ago

And telling them to .Select()

S-IERRAOP•16mo ago

dw I ll switch it tho in some cases I have to use lazy loading i.e loading server members

Angius•16mo ago

Why Why

S-IERRAOP•16mo ago

well youre not gonna load 100k entites at once are you ig paging does the job

Angius•16mo ago

No, you paginate it

S-IERRAOP•16mo ago

fucking hell gonnaa be funny swapping the entire logic from lazy loading what would the difference between select and include be ? if at all

Angius•16mo ago

.Include() means you're loading the entire entity and the entirety of related data Which is not always a good idea For example, if you want to display the user profile and their posts, if you do

var user = await _context.Users
    .Where(u => u.Id == id)
    .Include(u => u.Posts)
    .FirstOrDefaultasync();

var user = await _context.Users
    .Where(u => u.Id == id)
    .Include(u => u.Posts)
    .FirstOrDefaultasync();

it will load everything Including the user's password hash, their email, and all of their posts with their whole bodies and everything But you just need the user name, avatar, and the posts' IDs and titles So you can do

var user = await _context.Users
    .Select(u => new UserProfileDto {
        Name = u.Name,
        Avatar = u.Avatar,
        Role = u.Role.Name,
        Posts = u.Posts.Select(p => new PostLinkDto {
            Title = p.Title,
            Id = p.Id,
            Category = p.Category.Name
        })
    })
    .FirstOrDefaultAsync();

var user = await _context.Users
    .Select(u => new UserProfileDto {
        Name = u.Name,
        Avatar = u.Avatar,
        Role = u.Role.Name,
        Posts = u.Posts.Select(p => new PostLinkDto {
            Title = p.Title,
            Id = p.Id,
            Category = p.Category.Name
        })
    })
    .FirstOrDefaultAsync();

And it will get only that data

S-IERRAOP•16mo ago

oh yeah... thats alot better

Angius•16mo ago

The difference between

SELECT * FROM Users

SELECT * FROM Users

and

SELECT u.Name, u.Avatar FROM Users u

SELECT u.Name, u.Avatar FROM Users u

S-IERRAOP•16mo ago

well depends on the scenario bu tyeah

S-IERRAOP•16mo ago

if i remeber correctly virtual on icollections marks the entity as being loaded lazily

Angius•16mo ago

yep On any related data

S-IERRAOP•16mo ago

        if (await context.Users.Where(x => x.Id == creatorId).Include(x=>x.Relationships).FirstOrDefaultAsync() is not { } creatorUser)
            return NotFound();

        if (await context.Users.Where(x => x.Id == creatorId).Include(x=>x.Relationships).FirstOrDefaultAsync() is not { } creatorUser)
            return NotFound();

switched to this removing that virtual requires db update or no need

Angius•16mo ago

No need It doesn't change anything about the database Just about how EF queries for items

S-IERRAOP•16mo ago

S-IERRAOP•16mo ago

ong im gonna shoot my self

S-IERRAOP•16mo ago

S-IERRAOP•16mo ago

its there

Angius•16mo ago

The properties have to be virtual, because EF basically overrides

public virtual ICollection<Foo> Foos { get; set; }

public virtual ICollection<Foo> Foos { get; set; }

into a

private ICollection<Foo> _foos;
public override ICollection<Foo> Foos {
    get => _dbContext.Foos.Where(f => f.ThingId == this.Id).ToList();
    set => _foos = value;
}

private ICollection<Foo> _foos;
public override ICollection<Foo> Foos {
    get => _dbContext.Foos.Where(f => f.ThingId == this.Id).ToList();
    set => _foos = value;
}

S-IERRAOP•16mo ago

i think it could be bcause its querying by "SelfId" in this case its in OtherUserId

Angius•16mo ago

huh

S-IERRAOP•16mo ago

fails to load them it could be my garbage config

public record ChatRelationship : IEntityTypeConfiguration<ChatRelationship>
{
    public Guid Id { get; set; }
    
    public required Guid SelfId { get; set; }
    public ChatUser Self { get; set; }
    
    public required Guid OtherUserId { get; set; }
    public ChatUser OtherUser { get; set; }

    public required ChatRelationShipType Type { get; set; } = ChatRelationShipType.None;

    public void Configure(EntityTypeBuilder<ChatRelationship> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
        
        builder.HasKey(f => new { User1Id = f.SelfId, User2Id = f.OtherUserId });

        builder.HasOne(x => x.Self)
            .WithMany(x => x.Relationships)
            .HasForeignKey(x => x.SelfId)
            .OnDelete(DeleteBehavior.Restrict);
        
        builder.HasOne(x => x.OtherUser)
            .WithMany()
            .HasForeignKey(x => x.OtherUserId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

public record ChatRelationship : IEntityTypeConfiguration<ChatRelationship>
{
    public Guid Id { get; set; }
    
    public required Guid SelfId { get; set; }
    public ChatUser Self { get; set; }
    
    public required Guid OtherUserId { get; set; }
    public ChatUser OtherUser { get; set; }

    public required ChatRelationShipType Type { get; set; } = ChatRelationShipType.None;

    public void Configure(EntityTypeBuilder<ChatRelationship> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
        
        builder.HasKey(f => new { User1Id = f.SelfId, User2Id = f.OtherUserId });

        builder.HasOne(x => x.Self)
            .WithMany(x => x.Relationships)
            .HasForeignKey(x => x.SelfId)
            .OnDelete(DeleteBehavior.Restrict);
        
        builder.HasOne(x => x.OtherUser)
            .WithMany()
            .HasForeignKey(x => x.OtherUserId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

Angius•16mo ago

Well, OtherUser is not said to be related to Relationships, technically... So I wonder It shouldn't matter, I don't think

S-IERRAOP•16mo ago

is it not? eeeeeeeeeeee

Angius•16mo ago

.WithMany() is empty

S-IERRAOP•16mo ago

thought thats more of a way to reuse the existing relationship

Angius•16mo ago

Again, I don't think it matters that you explicitly do that But it might

S-IERRAOP•16mo ago

public record ChatUser : IEntityTypeConfiguration<ChatUser>
{
    public Guid Id { get; set; }
    
    public required string Username { get; set; }
    public required string Email { get; set; }
    public required string HashedPassword { get; set; }
    
    public Guid? RegistrationToken { get; set; }
    public Guid? PasswordResetToken { get; set; }
    
    public virtual ICollection<ChatChannel> Channels { get; set; } = new HashSet<ChatChannel>();
    public ICollection<ChatRelationship> Relationships { get; set; } = new HashSet<ChatRelationship>();

    public void Configure(EntityTypeBuilder<ChatUser> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
    }
}

public record ChatUser : IEntityTypeConfiguration<ChatUser>
{
    public Guid Id { get; set; }
    
    public required string Username { get; set; }
    public required string Email { get; set; }
    public required string HashedPassword { get; set; }
    
    public Guid? RegistrationToken { get; set; }
    public Guid? PasswordResetToken { get; set; }
    
    public virtual ICollection<ChatChannel> Channels { get; set; } = new HashSet<ChatChannel>();
    public ICollection<ChatRelationship> Relationships { get; set; } = new HashSet<ChatRelationship>();

    public void Configure(EntityTypeBuilder<ChatUser> builder)
    {
        builder.Property(x => x.Id)
            .HasDefaultValue(Guid.NewGuid());
    }
}

because

Angius•16mo ago

See what SQL your query generates Maybe EF doesn't know it has to join on both SelfId and OtherUserId

S-IERRAOP•16mo ago

this is the wrong thing

SELECT t."Id", t."Email", t."HashedPassword", t."PasswordResetToken", t."RegistrationToken", t."Username", r."SelfId", r."OtherUserId", r."Id", r."Type"
FROM (
    SELECT u."Id", u."Email", u."HashedPassword", u."PasswordResetToken", u."RegistrationToken", u."Username"
    FROM "Users" AS u
    WHERE u."Id" = @__creatorId_0
    LIMIT 1
) AS t
LEFT JOIN "Relationships" AS r ON t."Id" = r."SelfId"
ORDER BY t."Id", r."SelfId"

SELECT t."Id", t."Email", t."HashedPassword", t."PasswordResetToken", t."RegistrationToken", t."Username", r."SelfId", r."OtherUserId", r."Id", r."Type"
FROM (
    SELECT u."Id", u."Email", u."HashedPassword", u."PasswordResetToken", u."RegistrationToken", u."Username"
    FROM "Users" AS u
    WHERE u."Id" = @__creatorId_0
    LIMIT 1
) AS t
LEFT JOIN "Relationships" AS r ON t."Id" = r."SelfId"
ORDER BY t."Id", r."SelfId"

there

Angius•16mo ago

Yeah, it only joins on SelfId

S-IERRAOP•16mo ago

Any clue why that is I assume its because of the SelectMany()

Angius•16mo ago

Possibly

S-IERRAOP•16mo ago

tho if i were to write a second " .WithMany(x => x.Relationships)" it would error and say that relationship already exists

Angius•16mo ago

Right, I was afraid this might be the case No clue why I wrote "afraid" as "fartaid" lmao

S-IERRAOP•16mo ago

dw haha, and hm weird I ll have to look around how I could make a "friendship" model so to say cuz it seems to be a pain in the ass

Angius•16mo ago

The easiest way would be to have two collections MyRelationships and RelationshipsWithMe But that's more akin the followers system, not friendship where it has to be mutual

S-IERRAOP•16mo ago

https://stackoverflow.com/questions/69926761/how-to-model-friendships-between-identityusers-more-directly-in-entity-framework

Stack Overflow

How to Model Friendships Between IdentityUsers More Directly in Ent...

I'm trying to make a simple Social Network (MicroBlog) with ASP.NET Core 5+ with Entity Framework Core. I ran into a problem when modeling the friendships between the users and fetching friends of ...

S-IERRAOP•16mo ago

yeah found this but like exact same issue 1:1 just there is no replies

Angius•16mo ago

I wonder if you can go around it with a manual join

S-IERRAOP•16mo ago

Possibly, how would I do that?

Angius•16mo ago

.Join()

S-IERRAOP•16mo ago

programming is my passion

S-IERRAOP•16mo ago

https://www.youtube.com/watch?v=Nk-K7kJ6jeg&ab_channel=TotheMoon

To the Moon

YouTube

Tense1983 smashes his keyboard

S-IERRAOP•16mo ago

manual join doesnt seem to be doing much, also its not really a good fix, i ll look around because i'm sure there is some weird ass way of doing this

Angius•16mo ago

Try asking in #database

S-IERRAOP•16mo ago

@ZZZZZZZZZZZZZZZZZZZZZZZZZI'm thinking a hacky ass method I could write is if I made their relation ship many to many instead

Angius•16mo ago

I mean, it already is many-to-many users-to-users

S-IERRAOP•16mo ago

yeah just written weirdly

Angius•16mo ago

Except the join table has some added data Yeah

S-IERRAOP•16mo ago

yeah im a dumb ass the way i wrote this the fuck

Angius•16mo ago

Nah, N-M with added data is fine

S-IERRAOP•16mo ago

mm i see

Angius•16mo ago

It's just that EF seems to have issues with N-M where both sides of the relationship are the same

S-IERRAOP•16mo ago

Yeah

Angius•16mo ago

And stored in the same collection

S-IERRAOP•16mo ago

I will make it M-M

JakenVeina•16mo ago

I've actually gotten in to work quite well PreviousVersionId and NextVersionId just had to configure the relationships manually, but the nav props worked fine, IIRC.

Accord•16mo 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

❔ EFC Is not loading collection properly

Did you find this page helpful?