Linq To SQL: Retain list order when using .Contains

I'm using Lucene.net to build a MyListOfIds As List(Of Integer) which I then pass on to my Linq service. I then search the database as follows

Return _EventRepository.Read().Where(Function(e) MyListOfIds.Contains(e.ID)).ToList

Now I know that Lucene is already ordering MyListOfIds based on the weight it gave each term. What sucks is that Linq is losing that order in it's SQL search.

My Question: How can I retain that sort order when building my Lambda expression?

I tried using LINQPad to see how the query is being built, but because I had to declare a variable LINQPad didn't show me the resultant SQL :-(

Here's what I tried in LINQPad

Dim i As New List(Of Integer)
i.Add(1)
i.Add(100)
i.Add(15)
i.Add(3)
i.Add(123)

Dim r = (From e In Events
         Where i.Contains(e.ID)
         Select e)

note: my ex开发者_如何学Goample is in VB.NET, but I don't mind if responses are in C#

As hangy mentioned, I think the Dictionary method is the way to go. I'd go about it this way:

Public Function GetLuceneSearchResults(ByVal ids As List(Of Integer)) As List(Of Domain.Event) Implements IEventService.GetLuceneSearchResults
    Dim Results = (From e In _EventRepository.Read()
                   Where ids.Contains(e.ID)
                   Select e).ToDictionary(Function(e) e.ID, Function(e) e)

    Return (From i In ids
            Where Results.ContainsKey(i)
            Select Results(i)).ToList()
End Function

The first query returns a Dictionary with the event id as key, and the event itself as a value. You get the performance benefit of a hash lookup this way.

I would say that the LINQ to SQL query will return the data in the database's natural order (probably primary key?), because an IN condition in SQL (which is what the .Contains should be translated to) does not specify any ORDER and neither does your LINQ expression. If you think of it as in a normal SQL statement, it becomes obvious that you cannot specify the order in that way easily.

In order to sort the loaded data, you could get it unsorted and then sort the enumeration by the known order from Lucene. You will probably have to write that on your own (ie. a customer IComparer<T>) though.

Get unordered results back from the L2S query, then reorder in the same order as the List in a L2O (linq-to-objects) query using the .Select overload that gives ordinal position. E.g.:

var someResult = _EventRepository.Read().Where(e => MyListOfIds.Contains(e.ID)).ToList();

var someResultOrdered =
  from sr in someResult
  join lid in MyListOfIds.Select((v, i) => new { v, i }) on sr.ID equals lid.v
  orderby lid.i
  select sr;

OP Answer

@hangy got me thinking along the right lines, I think. Here's what I came up with...

open to suggestions!

    Public Function GetLuceneSearchResults(ByVal ids As List(Of Integer)) As List(Of Domain.Event) Implements IEventService.GetLuceneSearchResults
        Dim Results = _EventRepository.Read().Where(Function(e) ids.Contains(e.ID)).AsQueryable
        Dim Output As New List(Of Domain.Event)

        For Each i In ids
            Output.Add(Results.Where(Function(e) e.ID = i).SingleOrDefault)
        Next

        Return Output
    End Function

Now performance speculation aside, this is definitely working exactly as expected. I'd love to hear your thoughts on performance enhancements, or if this is just way out to lunch. Thanks.

You might need to have an IDictionary and your key would be just a incremental (like an identity column) and the value your actual id from Lucene.

Then, in your LINQ2SQL statement instead of doing this "where" clause you can do a "join" in the Dictionary Value with the Column in your DB. In your LINQ, sort by the dictionary key.

EDIT: ADDED EXAMPLE

Here is an example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {

            var ids = new Dictionary<int, int>();

            //key is just a sort sequence, value is the ID from Lucene
            ids.Add(1, 27);
            ids.Add(2, 25);
            ids.Add(3, 29);

            var ctx = new DataClasses1DataContext();

            var tabs = (from t in ctx.Tabs
                         where ids.Values.Contains(t.TabID)
                         select t).ToList();


            var sorted = from t in tabs
                         join id in ids on t.TabID equals id.Value
                         orderby id.Key
                         select t;

            foreach (var sortedItem in sorted) {
                Console.WriteLine(sortedItem.TabID);
            }
            Console.ReadLine();


        }
    }

}