Select Contains With CSV But Keep Sort Order

Question

I have the scenario where I have a IList<Guid> in a variable called csv which are also in a specific order that I need to keep. I am then doing a select contains like so I can get back all my topics based in the list of guids I have.

The guids are from a lucene search which are ordered by the original score from each LuceneResult. Which is why I need to keep them in this order.

            var results = _context.Topic
                              .Where(x => csv.Contains(x.Id));

However. I lose the order the guids came in as soon as I do this. Any idea how I can do this but keep the same order I hand the list of guids to the context and get the topics back in the same order based on the topid.Id?

I have tried the following as mentioned below, by doing a join but they still come out in the same order? Please note that I am paging these results too.

            var results = _context.Topic
            .Join(csv,
                    topic => topic.Id,
                    guidFromCsv => guidFromCsv,
                    (topic, guidFromCsv) => new { topic, guidFromCsv }
                )
                .Where(x => x.guidFromCsv == x.topic.Id)
                .Skip((pageIndex - 1)*pageSize)
                .Take(pageSize)
                .Select(x=> x.topic);

UPDATE

So I have moved away from just using and guid and am attempting to pass in my lucene model which has the score property that I want to order by. Here is what I have

   public PagedList<Topic> GetTopicsByLuceneResult(int pageIndex, int pageSize, int amountToTake, List<LuceneSearchModel> luceneResults)
{
    var results = _context.Topic
        .Join(luceneResults,
                topic => topic.Id,
                luceneResult => luceneResult.Id,
                (topic, luceneResult) => new { topic, luceneResult }
            )
            .Where(x => x.luceneResult.Id == x.topic.Id)
            .OrderByDescending(x => x.luceneResult.Score)
            .Skip((pageIndex - 1) * pageSize)
            .Take(pageSize)
            .Select(x => x.topic);

    var topicResults = results.ToList();

    // Return a paged list
    return new PagedList<Topic>(topicResults, pageIndex, pageSize, topicResults.Count);
}

However I am now getting the following error? Is what I am doing possible?

Unable to create a constant value of type 'LuceneSearchModel'. Only primitive types or enumeration types are supported in this context.

maybe this will help? http://stackoverflow.com/questions/8669985/linq-where-contains-keep-default-order — Nahum, Apr 09 '13 at 06:12
@leen3o _context.Topic.Where(x => csv.Contains(x.Id)).OrderBy(x => x.Id) - isn't it make the order you want? — Alex, Apr 09 '13 at 06:17
What is "specific order" in `IList`? Could you set this ordering for LINQ expression? — Dennis, Apr 09 '13 at 06:19
I have updated the question, please see above. @NahumLitvin thanks for the link, I just tried that but they still come out in the wrong order :( — YodasMyDad, Apr 09 '13 at 06:26
It isn't clear from your question (even after update), what is the ordering of `csv`? If this is just an ordering like 'csv.OrderBy(csv => csv)', then you should set the same ordering in LINQ query (see @voo comment). If this is an arbitrary order (e.g., `csv` content is built from user input), then the only option is ordering at client side (see Eren Ersönmez's answer below). — Dennis, Apr 09 '13 at 06:35
@Dennis as per updated question, the order of the guids coming in is based on a lucene search and score per guid. They are sorted by score (As in highest score first) and then passed to the repo to get the topics via EF. — YodasMyDad, Apr 09 '13 at 06:37
I think I might have figured it out. Instead of just passing a guid I'll pass in a custom object which has the guid and the score from the lucene search and I'll do the join as per the above, but then order by the score property descending... Well thats the theory. — YodasMyDad, Apr 09 '13 at 06:42
@leen3o `Join` and `GroupJoin` preserve order based on the outer sequence, not inner. If you want to order by `csv`, then you need to do `csv.Join`. — Eren Ersönmez, Apr 09 '13 at 06:43
@ErenErsönmez thanks for the heads up I didn't know that. I have updated the question above again. Hope you or Dennis might be able to give me some pointers. — YodasMyDad, Apr 09 '13 at 07:26
@Dennis no I don't want to store them in the database, Ill keep having a think about what I can do. Thanks for the help so far. — YodasMyDad, Apr 09 '13 at 08:17
@leen3o: OK. Just to help you to think - you want from database engine to order the result set, but the ordering condition is unknown for database engine. — Dennis, Apr 09 '13 at 08:42

Eren Ersönmez · Answer 1 · 2013-04-09T06:46:53.280

1

If I understand the question correctly, you want to filter the Topics based on the csv and you want to get back the results in the same order as the csv. If so:

var results = csv
    .GroupJoin(_context.Topic, guid => guid, topic => topic.Id, 
        (guid, topics) => topics)
    .SelectMany(topics => topics);

It is important to note that this treats the _context.Topic as an IEnumerable<T>; therefore, it will fetch all topics from the database and perform the GroupJoin on the client side, not on the database.

EDIT: Based on the comment below, this answer is NOT what you want. I'll just leave the answer here for documentation.

edited Apr 09 '13 at 06:46

answered Apr 09 '13 at 06:25

Eren Ersönmez

38,383
7
71
92

You should mention, that this ordering will be performed at *client* side. – Dennis Apr 09 '13 at 06:29
Thanks for the post. Please note that I am paging the results too, see updated question above – YodasMyDad Apr 09 '13 at 06:35
There could be 10,000's of topics so I can't do it client side. Thanks for the comment though. – YodasMyDad Apr 09 '13 at 06:43

Select Contains With CSV But Keep Sort Order

** UPDATE **

1 Answers1

UPDATE