CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sqllinq-to-entities
awj
awj

Reputation: 7959

Linq-to-Entities: My OrderBy clause isn't ordering

I have a Linq statement which uses a mixture of fluent and dot notation to order, group, order again, then do a Skip/Take on some entities:

(from terms in db.stt_term
 where !terms.deleted && languageIDs.Contains(terms.language_id)
 join concepts in db.stt_concept
    on terms.concept_id equals concepts.id
 where !concepts.deleted && concepts.dictionary_id == dictionaryID
 select terms).GroupBy(t => t.concept_id)
              .Select(grp => new
                                 {
                                     ConceptId = grp.Key,
                                     // the order of the Terms in each grouping should be dictated by the order of the Languages
                                     Terms = grp.OrderBy(t => SqlFunctions.CharIndex(strLangIDs, "_" + t.language_id + "_"))
                                 })
              // now order all the groups by the first term in each
              .OrderBy(grp => grp.Terms.FirstOrDefault().term_translation)
              .Skip(page*conceptsPerPage)
              .Take(conceptsPerPage)

Ok, that might be a bit too much to take in without any background explanation, but I'd like to draw your attention to the line

Terms = grp.OrderBy(t => SqlFunctions.CharIndex(strLangIDs, "_" + t.language_id + "_"))

This is my attempt to attain IndexOf-like functionality. I've resorted to this because Linq-to-Entities does not recognise an IndexOf call, and the closest thing I can find is CharIndex in the functions provided for linq-to-SQL.

CharIndex obviously takes a string, whereas what I'm really looking to do here is order term entities according to where their .language_id property is indexed in an array of integers. So to get around there being no available IndexOf, I've transformed the array of integers into a _-separated string, then search for a correspondingly-formatted language_id.

For example, the array {15, 1, 3, 4} becomes "_15_1_3_4_", and I then find the index of, say, "_3_" in this string. Terms where language_id = 15 should then be ordered above terms with lanaguage_id = 1.

However, the results show that no ordering takes place.

If I convert that OrderBy statement to be

Terms = grp.OrderBy(t => t.language_id))

Then ordering does take place. But no ordering takes place when I use my, admittedly, slightly hack-ey call to SqlFunctions.CharIndex.

Can anyone explain why CharIndex isn't ordering as I expect it to?

Upvotes: 1

Views: 422

Answers (1)

xanatos
xanatos

Reputation: 111940

You are inverting the searched with the target... SqlFunctions.CharIndex

public static Nullable<int> CharIndex(
    string toSearch,
    string target
)

invert the two.

From

SqlFunctions.CharIndex(strLangIDs, "_" + t.language_id + "_")

to

SqlFunctions.CharIndex("_" + t.language_id + "_", strLangIDs)

Upvotes: 1

Related Questions