The following code works, while needing to have the result fetched from the database first:

// ID's by priority (arbitrary yet limited number; eg. 1 to 10 values)
var ids = new [] { 3, 1, 2 };

// Should return "item 3" if it exists, else "item 1", else "item 2"..
var firstItemByPriority = context.Items
    .Where(i => ids.Contains(i.Id))
    .ToList() // materialize DB results
    .OrderBy(i => Array.IndexOf(ids, i.Id))
    .FirstOrDefault(); // ie. TOP 1 / LIMIT 1 / First (by ordering)

Is there a way to write this in a way where the ordering can occur in the generated database query? (Such can be constructed in several different ways when writing SQL manually.)

While the example above uses Array.IndexOf, the general goal is to sort by derived data not in the database. Using SQL Server specific extensions and/or third-party extensions are fair play.

CodePudding user response：

There's no way to do this efficiently without raw SQL queries, and raw SQL Queries are not composable with LINQ queries in EF6, like they are in EF Core. But the SQL Query for this is very simple. So something like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace Ef6Test
{

    public class Item
    {
        public int Id { get; set; }
        public String Name { get; set; }

    }


    public class Db : DbContext
    {
        public Db() : base("server=localhost;database=ef6Test;integrated security=true")
        { }
        public DbSet<Item> Items{ get; set; }
        
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Item>()
                        .ToTable("Items")
                        .Property(i => i.Id)
                        .HasColumnName("Id")
                        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            base.OnModelCreating(modelBuilder);
        }


    }

    internal class Program
    {

        public static void Main(string[] args)
        {

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);

                if (db.Database.Exists())
                    db.Database.Delete();

                db.Database.Create();

                for (int i = 0; i < 1000; i  = 2)
                {
                    var item = new Item() { Id = i, Name = $"Item{i}" };
                    db.Items.Add(item);
                    if (i % 100 == 0)
                        db.SaveChanges();

                }
                db.SaveChanges();

            }

            using (var db = new Db())
            {
                db.Database.Connection.Open();

                db.Database.ExecuteSqlCommand("create table #ids(position int primary key, id int)");

                var ids = new[] { 3, 1, 12, 5, 8, 10, 7};
                var json = System.Text.Json.JsonSerializer.Serialize(ids);

                var pIds = new SqlParameter("@ids", System.Data.SqlDbType.NVarChar, -1);
                pIds.Value = json;

                db.Database.ExecuteSqlCommand("insert into #ids(position,id) select [key], [value] from openjson(@ids)", pIds);

                var sql = @"
select top (@count) i.*
from Items i
join #ids ids
  on i.Id=ids.Id
order by ids.position
";
                var pCount = new SqlParameter("@count", 3);
                var results = db.Database.SqlQuery<Item>(sql,pCount).ToList();
                
                foreach (var item in results)
                {
                    Console.WriteLine(item.Id);
                }
              
            }
            Console.WriteLine("complete");
         }
  
        }
    }

outputs

12
8
10
complete