score:0

I don't recommend that you write the more complex linq query to solve it.

Yes, it's becoming shorter that way but also becoming harder to read. The longer answer below. You can shorten it by yourself.

First you should get the ones which have genreIds of subGenereIds list.

var onesWithGenreIdFromList = (from bk in bookContext.Books
where subGenereIds.Contains(bk.genreId).orderby bk.Id descending select bk).ToList();

Then get the rest of the list (ones don't have genreId of subGenereIds list) from your list.

var results = (from bk in bookContext.Books
where !subGenereIds.Contains(bk.genreId)
orderby bk.id descending
select bk).ToList();

As the last step you should add the first list to second one to create the order you would like:

onesWithGenreIdFromList.AddRange(results);
var lastResults = onesWithGenreIdFromList;

Happy coding!

score:1

You'll need to to the ordering in-memory since there's not a way to pass that ordering logic to SQL. You can do that by using AsEnumerable() to change the context from a SQL query to an in-memory query:

 var results = bookContext.Books
                          .AsEnumerable()
                          .OrderByDescending(bk => bk.Id)
                          .ThenBy(bk => subGenereIds.IndexOf(bk.Id));

score:1

From your comments it looks like you need to have as much of this as possible done in the the database. Assuming that you have the books and the user subscriptions stored in the same place you can combine the query for the user's preferences with the search of the book table to get the result you're after.

For the sake of argument, let's assume you have some tables that look something like this as POCOs:

[Table("Books")]
public class Book
{
    public int ID { get; set; }
    public int Genre { get; set; }
    public string Name { get; set; }
    public string Author { get; set; }
    public Date PublishDate { get; set; }
}

[Table("Genres")]
public class Genre
{
    public int ID { get; set; }
    public string Name { get; set; }
}

[Table("Users")]
public class User
{
    public int ID { get; set; }
    public string Name { get; set; }
}

[Table("UserGenrePreferences")]
public class UserGenrePreference
{
    public int User { get; set; }
    public int Genre { get; set; }
}

For the sake of the example we have one genre per book and no duplicates in the UserGenrePreferences table.

The basic ordering might look something like this:

// get genre preferences for the selected user
var userPrefs =
    from pref in context.UserGenrePReferences
    where pref.User == userID
    select new { Genre = (int?)pref.Genre };

var result = 
    from book in context.Books
    join pref in userPrefs on book.Genre equals pref.Genre
    into prefTemp
    from pref in prefTemp.DefaultIfEmpty()
    orderby (pref.Genre == null ? 1 : 0), book.ID
    select book;

This does an outer join against the user's genre preferences. Books with a matching genre will have a non-null value in the matching pref record, so they will be placed before unmatched books in the result. Pagination would then by handled by the normal .Skip(...).Take(...) method.

For very large data sets this will still take some time to run. If you're expecting that the user will be paging through the data it might be a good idea to grab just the book IDs and cache those in memory. Take the first few thousand results and look for more when needed for instance. Then grab the book records when the user wants them, which will be much quicker than running the query above for every page.


If on the other hand your user preference data is not in the same database, you might be able to use the Contains method to do your ordering.

Assuming that you have an array of genre IDs in memory only you should be able to do this:

int[] userPrefs = new int[] { 1, 2, 3, 5 };

var result = 
    from book in context.Books
    orderby (userPrefs.Contains(book.Genre) ? 0 : 1), book.ID
    select book;

Depending on the ORM this will translate into something similar to:

SELECT *
FROM Books
ORDER BY
    CASE WHEN Genre = 1 OR Genre = 2 OR Genre = 3 OR Genre = 5 THEN 0 ELSE 1 END,
    ID

This can be fairly quick but if the list of preferences is very large it could be slower.


Related Articles