How do I order a sql datasource of uniqueidentifiers in Linq by an array of uniqueindentifiers

Question

I have a string list(A) of individualProfileId's (GUID) that can be in any order(used for displaying personal profiles in a specific order based on user input) which is stored as a string due to it being part of the cms functionality.

I also have an asp c# Repeater that uses a LinqDataSource to query against the individual table. This repeater needs to use the ordered list(A) to display the results in the order specified.

Which is what i am having problems with. Does anyone have any ideas?

list(A)

'CD44D9F9-DE88-4BBD-B7A2-41F7A9904DAC',
'7FF2D867-DE88-4549-B5C1-D3C321F8DB9B',
'3FC3DE3F-7ADE-44F1-B17D-23E037130907'

Datasource example

IndividualProfileId                  Name      JobTitle EmailAddress IsEmployee
3FC3DE3F-7ADE-44F1-B17D-23E037130907 Joe Blo   Director [email protected]   1
CD44D9F9-DE88-4BBD-B7A2-41F7A9904DAC Maxy Dosh The Boss              1
98AB3AFD-4D4E-4BAF-91CE-A778EB29D959 some one  a job    [email protected] 1
7FF2D867-DE88-4549-B5C1-D3C321F8DB9B Max Walsh CEO                   1

Answer 1

1. Turn the list(A), which you stated is a string, into an actual list. For example, you could use listAsString.Split(",") and then remove the 's from each element. I’ll assume the finished list is called list.

2. Query the database to retrieve the rows that you need, for example:

   var data = db.Table.Where(row => list.Contains(row.IndividualProfileId));
   

3. From the data returned, create a dictionary keyed by the IndividualProfileId, for example:

   var dic = data.ToDictionary(e => e.IndividualProfileId);
   

4. Iterate through the list and retrieve the dictionary entry for each item:

   var results = list.Select(item => dic[item]).ToList();
   

Now results will have the records in the same order that the IDs were in list.

Answer 2

There is a very simple (single-line) way of doing this, given that you get the employee results from the database first (so resultSetFromDatabase is just example data, you should have some LINQ query here that gets your results).

var a = new[] { "GUID1", "GUID2", "GUID3"};
var resultSetFromDatabase = new[]
{
    new { IndividualProfileId = "GUID3", Name = "Joe Blo" },
    new { IndividualProfileId = "GUID1", Name =  "Maxy Dosh" },
    new { IndividualProfileId = "GUID4", Name =  "some one" },
    new { IndividualProfileId = "GUID2", Name =  "Max Walsh" }
};

var sortedResults = a.Join(res, s => s, e => e.IndividualProfileId, (s, e) => e);

It's impossible to have the datasource get the results directly in the right order, unless you're willing to write some dedicated SQL stored procedure. The problem is that you'd have to tell the database the contents of a. Using LINQ this can only be done via Contains. And that doesn't guarantee any order in the result set.