Linq To SQL: Save List When Using .Contains

Question

I use Lucene.net to create MyListOfIds As List(Of Integer), which I then pass to my Linq service. Then I scan the database as follows:

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

Now I know that Lucene already orders MyListOfIdsdepending on the weight he gave to each member. What sucks is that Linq loses this order in it SQL search.

My question is: How do I maintain this sort order when building a Lambda expression?

I tried using LINQPad to see how the query is built, but since I had to declare a variable LINQPad did not show me the resulting SQL :-(

Here is 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: VB.NET, , #

Answer 1

, , - . :

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

, - . - .

Answer 2

, LINQ to SQL (, ?), IN SQL ( .Contains) ORDER, LINQ. SQL-, , .

, , Lucene. , (.. IComparer<T>).

Answer 3

L2S, , List L2O (linq-to-objects), . , . :.

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;

Answer 4

, IDictionary, (, ) Lucene.

LINQ2SQL "where" "" . LINQ, .

:

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();


        }
    }

}

Answer 5

Answer OP

@hangy made me think the right line, I think. Here is 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

Currently speculating performance aside, it definitely works exactly as expected. I would love to hear your thoughts on improving productivity, or if it's just going out for lunch. Thank.