C#•2y ago

❔ EntityFramework many-to-many relation does not work

Question: I have two database tables, one for users called DataUser , and one to define a relation between two users, called DataUserLike. I defined a many-to-many relation in DataUser which both go directly into a collection of DataUser (LikedUsers & LikedByUsers), and below that I define the DataUserLike table to use as a seperate entity. This also uses a collection (Likes & LikedBy ). When I add relation by inserting a DataUserLike instance into the database, I can see it properly added into the database. However, when I try to retrieve the users, both mapped and unmapped, the collections remain empty. Does anybody know what I did wrong, and what I have to do to show the likes in the collections? https://hastebin.com/voxawutiko.csharp

Hastebin: Send and Save Text or Code Snippets for Free | Toptal®

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

35 Replies

Anton•2y ago

how do you retrieve tge users?

FusedQyouOP•2y ago

Here is the empty collection

Anton•2y ago

you haven't included the relevant bit of code so? I can't see the code the message blocks the relevant code

FusedQyouOP•2y ago

You're being too quick 😄 I would like to show you the bit of code, but I think it contains the problem, now that I look at it That being the fact that I forgot to include the line of code that fetches the related data 😛

public Task<DataUser?> GetByIdAsync(uint id, bool includeDeleted, CancellationToken cancellationToken)
    {
        return this._databaseContext
            .Users
            .Where(x => !includeDeleted ? !x.Deleted : true)
            .Include(x => x.Photos.Where(y => !includeDeleted ? !y.Deleted : true))
            .SingleOrDefaultAsync(x => x.Id == id, cancellationToken);
    }

public Task<DataUser?> GetByIdAsync(uint id, bool includeDeleted, CancellationToken cancellationToken)
    {
        return this._databaseContext
            .Users
            .Where(x => !includeDeleted ? !x.Deleted : true)
            .Include(x => x.Photos.Where(y => !includeDeleted ? !y.Deleted : true))
            .SingleOrDefaultAsync(x => x.Id == id, cancellationToken);
    }

Anton•2y ago

don't do your wheres like that, that's awful

FusedQyouOP•2y ago

I was hoping to implement ProjectTo<> from Automapper, but I can't seem to filter out deleted entities with that I would love to remove it, but I couldn't find an alternative

Anton•2y ago

var q = _dbContext.Users.AsQueryable();
if (!includeDeleted)
    q = q.Where(x => !x.Deleted);

var q = _dbContext.Users.AsQueryable();
if (!includeDeleted)
    q = q.Where(x => !x.Deleted);

you don't have to chain everything in one go

FusedQyouOP•2y ago

Oh, like that. I actually did that in a query that detches all users, but I haven't done it here yet. Thanks for mentioning it

FusedQyouOP•2y ago

Although I guess this one is also bad since it still does it with the first two cases

Anton•2y ago

and what's the generated query?

FusedQyouOP•2y ago

I haven't checked. I suppose it's logged? I got most of EntityFramework logging turned off Let me fix my main issue and then I will check

Anton•2y ago

yeah I need to see the query

FusedQyouOP•2y ago

Looks like the relation issue is fixed now

FusedQyouOP•2y ago

Give me a second and I'll log the output Alright, I had to set up the logger for EntityFramework @AntonC I'm just gonna dump the whole message

FusedQyouOP•2y ago

https://hastebin.com/vubenogidi.rust

Hastebin: Send and Save Text or Code Snippets for Free | Toptal®

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

FusedQyouOP•2y ago

ProjectTo<> would really improve all of this, as it would only return the columns I specifically ask for in a DTO, but due to the issue I had I have not yet been able to add it. https://docs.automapper.org/en/stable/Queryable-Extensions.html#explicit-expansion

Angius•2y ago

You can always .Select() to a DTO if Automapper is wonky And Automapper will be the more wonky, the further you get from a 1:1 mapping

FusedQyouOP•2y ago

Yeah, but since I constantly change the classes whilst developing this I would have to manually change every single Select statement I make, and I really want to avoid it... You're very right though, a manual select statement would be better

Angius•2y ago

You can save the mappings in Expression<Func<TSource, TTarget>> to reuse them

FusedQyouOP•2y ago

That's a good idea. Technically automapper is the same thing. I just have 0 experience with expressions so I'll have to take a look at that But I suppose doing it manually over automapper is something I should focus on

Angius•2y ago

public static class ThingMappings
{
    public static Expression<Func<Thing, ThingDTO>> ToDto = t => new ThingDto {
        Name = t.Name,
        Count = t.Thingamajigs.Count(),
    }
}

public static class ThingMappings
{
    public static Expression<Func<Thing, ThingDTO>> ToDto = t => new ThingDto {
        Name = t.Name,
        Count = t.Thingamajigs.Count(),
    }
}

used with

.Where(ThingMappings.ToDto)

.Where(ThingMappings.ToDto)

And if you need to introduce some param,

public static class ThingMappings
{
    public static Expression<Func<Thing, ThingDTO>> ToDto(bool f) => t => new ThingDto {
        Name = t.Name,
        Count = t.Thingamajigs.Count(),
        IsWhatever = t.Whatever == f
    }
}

public static class ThingMappings
{
    public static Expression<Func<Thing, ThingDTO>> ToDto(bool f) => t => new ThingDto {
        Name = t.Name,
        Count = t.Thingamajigs.Count(),
        IsWhatever = t.Whatever == f
    }
}

FusedQyouOP•2y ago

What exactly is the point of an Expression? Isn't it able to read the behaviour of whatever you feed into it, or something? Perhaps this is something I might aswell Google though Thanks 😄

Angius•2y ago

The Func needs to be wrapped in an Expression to allow EF to parse it properly

FusedQyouOP•2y ago

@AntonC sorry for pinging, but I went ahead and changed the general query a bit. It still misses the improved mapping to only receive relevant data, but the where clauses are fixed. https://hastebin.com/gakixagegu.typescript

Hastebin: Send and Save Text or Code Snippets for Free | Toptal®

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

FusedQyouOP•2y ago

If you happen to have any other points, I would love to hear them

Anton•2y ago

you can make it static and turn it into an extension method the method that applies the deleted filter (but then you won't be able to make the queryable generic, so fair) at least make it static tho

FusedQyouOP•2y ago

Good idea Sadly the methods inside the Include() don't work Something about the expression not being able to be parsed. I can't figure out how to change that

Anton•2y ago

because it takes expressions and not delegates like zzzzz has mentioned

FusedQyouOP•2y ago

Yeah, I saw another thread in here that mentioned it. I didn't realise it also worked for this

protected IQueryable<TEntity> ApplyDeletedWhereClause<TEntity, TProperty>(IQueryable<TEntity> query, Expression<Func<TEntity, ICollection<TProperty>>> nextProperty, bool includeDeleted)
        where TEntity : DatabaseKeylessEntity
        where TProperty : DatabaseKeylessEntity
        => includeDeleted ?
            query.Include(nextProperty) :
            query.Include(nextProperty);

protected IQueryable<TEntity> ApplyDeletedWhereClause<TEntity, TProperty>(IQueryable<TEntity> query, Expression<Func<TEntity, ICollection<TProperty>>> nextProperty, bool includeDeleted)
        where TEntity : DatabaseKeylessEntity
        where TProperty : DatabaseKeylessEntity
        => includeDeleted ?
            query.Include(nextProperty) :
            query.Include(nextProperty);

I got this, but now I somehow need to add .Where(y => !y.Deleted) to it, and I have no clue how.

Anton•2y ago

I think you can just do it separately, try it ah yeah probably not I think you're trying too hard tbh you need expressions for both cases

FusedQyouOP•2y ago

Welp So this isn't going to work? 😂

protected IQueryable<TEntity> ApplyDeletedWhereClause<TEntity, TProperty>(IQueryable<TEntity> query, Expression<Func<TEntity, ICollection<TProperty>>> nextProperty, bool includeDeleted)
        where TEntity : DatabaseKeylessEntity
        where TProperty : DatabaseKeylessEntity
    {
        if (includeDeleted)
        {
            return query.Include(nextProperty);
        }

        Expression<Func<ICollection<TProperty>, IEnumerable<TProperty>>> newPred =
            x => x.Where(x => !x.Deleted);

        nextProperty = Expression.Lambda<Func<TEntity, ICollection<TProperty>>>(
            Expression.AndAlso(nextProperty, newPred), nextProperty.Parameters);

        return query.Include(nextProperty);
    }

protected IQueryable<TEntity> ApplyDeletedWhereClause<TEntity, TProperty>(IQueryable<TEntity> query, Expression<Func<TEntity, ICollection<TProperty>>> nextProperty, bool includeDeleted)
        where TEntity : DatabaseKeylessEntity
        where TProperty : DatabaseKeylessEntity
    {
        if (includeDeleted)
        {
            return query.Include(nextProperty);
        }

        Expression<Func<ICollection<TProperty>, IEnumerable<TProperty>>> newPred =
            x => x.Where(x => !x.Deleted);

        nextProperty = Expression.Lambda<Func<TEntity, ICollection<TProperty>>>(
            Expression.AndAlso(nextProperty, newPred), nextProperty.Parameters);

        return query.Include(nextProperty);
    }

Anton•2y ago

idk, maybe, try it

FusedQyouOP•2y ago

Well it didn't, it was more a question if this could work in general But I'm really overcomplicating it now I'm probably better off doing it manually

Anton•2y ago

yes that's building the expression manually

Accord•2y 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

❔ EntityFramework many-to-many relation does not work