C
C#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;
    }
F
FusedQyou•512d ago
I suppose this awful query can do the same... query = query.SelectMany(x => x.LikedByUsers.Where(y => x.LikedUsers.Any(z => z == x)));
A
Anton•512d ago
yeah, or just .Contains instead of Any should also work
F
FusedQyou•512d ago
I was wrong, the same error is returned. Would it be because of SelectMany?
A
Anton•512d ago
no, selectmany is supported oh, it says it's not supported specifically for sqlite
F
FusedQyou•512d ago
Welp
A
Anton•512d ago
there are certain things that don't work on sqlite for example sequences search for workarounds specifically for sqlite
F
FusedQyou•512d 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?
A
Anton•512d ago
yeah idk how to do stuff like this I'd probably split this into two queries
F
FusedQyou•512d ago
Split it up?
A
Anton•512d ago
AsSplitQuery might help
F
FusedQyou•512d ago
Not sure how, but I'll take a look
A
Anton•512d ago
yeah me neither just a gut feel
F
FusedQyou•512d 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
A
Anton•512d ago
yeah makes sense you'll probably want an index for it too
F
FusedQyou•512d ago
Never thought I would have such issues with EF Right
A
Accord•510d 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
More Posts
✅ Detect Network RequestI want make service that detect all connection to specific domain or ip? And if this request contain❔ CORS errorHello, I have Cors error on my project. I add Addpolicy in my configureServices and also add app.Us❔ Ambiguities? How?Idk how i can resolve this i don't see the problem✅ How can I get rid of this annoying pointer thing in the first row, datagridview winforms^✅ How can I use a row header of a datagridview to display the row number instead of being empty?^✅ How can i properly display this datagridviewHow can i get rid of the row select thing on the left (just white space)✅ I am trying to output a 2D array to a datagridview, but it is not outputting correctlyIt is saying System.Int32[]✅ Task not returning and stopping programHey, I am currently trying to start one task for every string in the list, but this isn't working an❔ DbContextCan i use this in a non-asp area like for example, a simple program to make few tables using DbSet✅ The specified deps.json does not exist while scaffolding DBI am going through a tutorial to create a Web API app with ASP.NET6 and MySql. I wanted to scaffold ❔ C sharp object reference errorI am trying to reference an object that I'm displaying on PageHeader.xaml and I keep getting this erInteroperability issues (PInvoke)Hi! I'm calling a native CPP library and retrieving a struct by reference which returns a struct con❔ SKIA SHARP PROBLEMError: ```cmd Dec 02 16:05:22 mourashop shopmoura-api-app[3847595]: Unhandled exception. System.Argu❔ FluentAssertions excluding collection elementsI have two IEnumerable<Claim> tmp1, tmp2 and I want to see if they are equal, except for the claims ✅ Best way to display a 2D arrayWhat would be the best way to display a 2D array in winforms c#, with the best design, or is just prWrong count for my inversions functionIt is supposed to be 10 inversion count but I am getting 13 ```csharp var array = new int✅ Need some help with classesNot too sure how to correctly use attributes from a class in a class function✅ Type CastingHi, Just wondering how I would type cast the object 'control' to the type 'telerikslider'?❔ Mocking with EntityFrameworkCoreHm, I'm creating unit tests for a class that has a `private readonly MyDbContext dbContext;` that ca❔ How to use BeginTransaction in repository from infrastructureI have this code in CommandHandler (Application layer): ``` using var transaction = _ProductReposito