C
C#•2y ago
FusedQyou

✅ 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;
    }
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;
    }
16 Replies
FusedQyou
FusedQyouOP•2y ago
I suppose this awful query can do the same... query = query.SelectMany(x => x.LikedByUsers.Where(y => x.LikedUsers.Any(z => z == x)));
Anton
Anton•2y ago
yeah, or just .Contains instead of Any should also work
FusedQyou
FusedQyouOP•2y ago
I was wrong, the same error is returned. Would it be because of SelectMany?
Anton
Anton•2y ago
no, selectmany is supported oh, it says it's not supported specifically for sqlite
FusedQyou
FusedQyouOP•2y ago
Welp
Anton
Anton•2y ago
there are certain things that don't work on sqlite for example sequences search for workarounds specifically for sqlite
FusedQyou
FusedQyouOP•2y ago
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;
    }
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; }
}
[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);
    }
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?
Anton
Anton•2y ago
yeah idk how to do stuff like this I'd probably split this into two queries
FusedQyou
FusedQyouOP•2y ago
Split it up?
Anton
Anton•2y ago
AsSplitQuery might help
FusedQyou
FusedQyouOP•2y ago
Not sure how, but I'll take a look
Anton
Anton•2y ago
yeah me neither just a gut feel
FusedQyou
FusedQyouOP•2y ago
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);
    }
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
Anton
Anton•2y ago
yeah makes sense you'll probably want an index for it too
FusedQyou
FusedQyouOP•2y ago
Never thought I would have such issues with EF Right
Accord
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. Closed!
Want results from more Discord servers?
Add your server