Left outer join using LINQ Query Syntax EF Core C#

Question

I have a question in regards with the below,

1. Left outer join of two tables who are not connected through Foreign Key.
2. Order by the results matched in second table.
3. I would like this to be done in LINQ Query method syntax as I am adding lots of conditions depending on the input provided along with skip and limit.

If we have below Product and Favorite tables

So the output that I would like to have is:

meaning with the favorites as part of first set and which are not favorites should be behind them. Below are the tries that I did. I am able to join the tables get the output but not sure how I can make sure that in the first page I get all the favs.

This answer was very near to what I thought but it gets the result and then does the ordering which will not be possible in my case as I am doing pagination and using IQueryable to get less data.

Group Join and Orderby while maintaining previous query

Open to any solutions to achieve the same.

[Table("Product")]
public class ProductModel
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid ProductId { get; set; }
    public string ProductName {get; set;}
    public bool IsFavorite { get; set; }
}

[Table("UserFavorite")]
public class UserFavoriteModel
{
    [Required]
    public Guid UserId { get; set; }
    [Required]
    public Guid Identifier { get; set; }
    [Required]
    public FavoriteType Type { get; set; }
}

// Gets products
private async Task<List<ProductModel>> GetProductsAsync(
    Guid categoryId, 
    Guid subCategoryId, 
    int from,
    int limit)
{
    var query = _context.Products.AsQueryable();

    if (!string.IsNullOrEmpty(categoryId))
        query = query.Where(product => product.CategoryId == categoryId);
    if (!string.IsNullOrEmpty(subCategoryId))
        query = query.Where(product => product.SubCategoryId == subCategoryId);

    query = query.Skip(from).Take(limit);

    var products = await query.ToListAsync();

    query = query.GroupJoin(
    _context.Favorites.AsNoTracking()
    .Where(favorite => favorite.Type == FavoriteType.FASHION)
    // This user Id will come from context just adding for overall picture.
    .Where(favorite => favorite.UserId == userId),
    //This orderby if I add will not make any difference.
    //.OrderByDescending(favorite => favorite.Identifier),
    v => v.ProductId,
    f => f.Identifier,
    (product, fav) => new { product, fav }).
    SelectMany(x => x.Fav.DefaultIfEmpty(),
                    (x, y) => SetFavorite(x.Project, y));

}

private static ProductModel SetFavorite(ProductModel v, UserFavoriteModel si)
{
    v.IsFavorite = (si != null);
    return v;
}

Answer 1

I would do something like this:

var query =
   _context.Products.AsQueryable().Select(p => new ProductModel {
      ProductId = p.ProductId,
      ProductName = p.ProductName,
      IsFavorite =
         _context.Favorites.Any(f =>
            f.Identifier = p.ProductId &&
            f.Type == FavoriteType.FASHION &&
            f.UserId == userId
         )
   }).OrderByDescending(favorite => favorite.Identifier);