✅ This EntityFramework query can not be translated for SQLite

The issue lies within Intersect. The point of the query is to fetch the current user, and to populate LikedByUsers with matched users. An user is matched when he appears in LikedByUsers and LikedUsers. How can I make this work for SQLite?
Error: "Translating this query requires the SQL APPLY operation, which is not supported on SQLite."

private IQueryable<DataUser> CreateGetAllMatchedUsersQuery(DataUser user, bool includeDeleted)
    {
        var query = this._databaseContext.Users.AsQueryable();
        query = query.Where(x => x.Id == user.Id);

        // Ensure no deleted users are returned if specified not to.
        if (!includeDeleted)
        {
            query = query.Where(x => !x.Deleted);
            query = query.Include(x => x.LikedByUsers.Where(y => !y.Deleted));
            query = query.Include(x => x.LikedUsers.Where(y => !y.Deleted));
        }
        else
        {
            query = query.Include(x => x.LikedByUsers);
            query = query.Include(x => x.LikedUsers);
        }

        // Select all matches
        query = query.SelectMany(x => x.LikedByUsers.Intersect(x.LikedUsers));
        return query;
    }

I suppose this awful query can do the same...
query = query.SelectMany(x => x.LikedByUsers.Where(y => x.LikedUsers.Any(z => z == x)));

yeah, or just .Contains instead of Any should also work

I was wrong, the same error is returned. Would it be because of SelectMany?

no, selectmany is supported

oh, it says it's not supported specifically for sqlite

Welp

there are certain things that don't work on sqlite

for example sequences

search for workarounds specifically for sqlite

Working on it... Unsure what an alternative would be

Specifically on how to cast IQueryable<IEnumerable<DataUser>> back to IQueryable<DataUser>

I don't get it, I turned it into this, and I still get the error.

private IQueryable<DataUser> CreateGetAllMatchedUsersQuery(DataUser user, bool includeDeleted)
    {
        var query = this._databaseContext.Users.AsQueryable();
        query = query.Where(x => x.Id == user.Id);

        // Ensure no deleted users are returned if specified not to.
        if (!includeDeleted)
        {
            query = query.Where(x => !x.Deleted);
            query = query.Include(x => x.LikedByUsers.Where(y => !y.Deleted));
            query = query.Include(x => x.LikedUsers.Where(y => !y.Deleted));
        }
        else
        {
            query = query.Include(x => x.LikedByUsers);
            query = query.Include(x => x.LikedUsers);
        }

        // Select all matches
        //query = query.Select(x => x.LikedByUsers.Where(y => x.LikedUsers.Contains(x)));
        //query = query.SelectMany(x => x.LikedByUsers.Intersect(x.LikedUsers));
        var intersectedUsers = query.Select(x => x.LikedByUsers.Where(y => x.LikedUsers.Any(z => z == x)));
        query = from list in intersectedUsers
                from item in list
                    select item;

        return query;
    }

Worked around every single thing I could think of and it still complains

@AntonC Sorry for pinging, but maybe you know a solution for this.

It's the same idea, I want to get the matches, but I am now trying to get it from a DataUserLike class.

[Table("Likes")]
[PrimaryKey("LikedId", "LikedById")]
public sealed class DataUserLike : DatabaseKeylessEntity
{
    // Columns
    public required DataUser Liked { get; init; }
    public required DataUser LikedBy { get; init; }
}

The problem is that I somehow need to determine if there is an instance where one user is Liked and LikedBy another user, and the other way around. This would return a list of users that the specified user matched with. I'm having a bit of a brain fart after everything, so excuse me if the solution is simple.

private IQueryable<DTODataUser> CreateGetAllMatchedUsersDTOQuery(DataUser user, bool includeDeleted)
    {
        var query = this._databaseContext.Likes.AsQueryable();

        query = query.Include(x => x.Liked);
        query = query.Include(x => x.LikedBy);
        query = query.Where(x => x.Liked.Id == user.Id || x.LikedBy.Id == user.Id);

        // TODO: The IQueryAble<DataUserLike> needs to get the matched users, making it a IQueryable<IEnumerable<DataUser>>. This is then flattened.

        return query.ProjectTo<DTODataUser>(this._mapper.ConfigurationProvider);
    }

Problem with this is: How can I figure out if a Liked/LikedBy id matches that of another row in the same query?

yeah idk how to do stuff like this

I'd probably split this into two queries

Split it up?

AsSplitQuery might help

Not sure how, but I'll take a look

yeah me neither

just a gut feel

I think I did it

No idea how AsSplitQuery works, but I was reading about complex queries, and ended up using this:

private IQueryable<DTODataUser> CreateGetAllMatchedUsersDTOQuery(DataUser user, bool includeDeleted)
    {
        var query = this._databaseContext.Likes.AsQueryable();

        query = query.Include(x => x.Liked);
        query = query.Include(x => x.LikedBy);
        query = query.Where(x => x.Liked.Id == user.Id || x.LikedBy.Id == user.Id);

        // TODO: The IQueryAble<DataUserLike> needs to get the matched users, making it a IQueryable<IEnumerable<DataUser>>. This is then flattened.
        query = from user1 in query
                join user2 in query
                on user1.LikedBy
                equals user2.Liked
                select user1;

        var userQuery = query
            .Where(x => x.Liked.Id == user.Id)
            .Select(x => x.LikedBy);
        return userQuery.ProjectTo<DTODataUser>(this._mapper.ConfigurationProvider);
    }

There are probably a million ways to improve this

Works in SQLite too

@AntonC I believe the query joins itself, and then based on a predicate selects every entry that equals two likes, pretty much.

The userQuery is a simple "Get the user if the id matches since we need the other one"

I wonder if it can be written in plain linq

yeah makes sense

you'll probably want an index for it too

Never thought I would have such issues with EF

Right

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.

Closed!