Archeg Archeg - 1 month ago 14
SQL Question

Union of ordered sets in entity framework and then skip

I have a table of lessons, and I want to perform a text search over several fields of it. However the search should be ordered: for example lesson have a

Keywords
field and
Description
field. The search should give a priority over values found by
Keywords
. Everything should be also ordered by date but only after the priority is considered.

I'm also using
ToPagedList()
in the end from https://github.com/troygoode/PagedList (I think it just uses
Skip()
and
Top()
to manage pages)

This is what I have so far:

string[] word = /*Search words*/
var data = db.LessonsLearneds.Where(dbRecord => words.Any(word =>
dbRecord.SearchKeywords.StartsWith(word + ",") ||
dbRecord.SearchKeywords.Contains("," + word + ",") ||
dbRecord.SearchKeywords.EndsWith("," + word)))
.Select(x => new { Record = x, Order = 1 });

data = data.Union(
db.LessonsLearneds
.Where(dbRecord => words.Any(word => dbRecord.Title.Contains(word)))
.Select(x => new { Record = x, Order = 2 }));

data = data.Union(
db.LessonsLearneds
.Where(dbRecord => words.Any(word => dbRecord.Description.Contains(word)))
.Select(x => new { Record = x, Order = 3}));

data = data.Union(
db.LessonsLearneds
.Where(dbRecord => words.Any(word => dbRecord.Lesson.Contains(word)))
.Select(x => new { Record = x, Order = 4 }));

return data
.Distinct()
.OrderBy(x => x.Order)
.ThenByDescending(x => x.Record.Date)
.Select(x => x.Record)
.ToPagedList(pageNumber, pageSize);


Overall this code does almost what I want, except of
Distinct()
. Each union here can retrieve the same record, so I may receive it several times, and
Distinct()
does not forces the uniqueness because of virtual
Order
field. I cannot put
Distinct
after
Select(x => x.Record)
because of
ToPagedList(..)
which requires the set to be ordered (results in:
The method 'Skip' is only supported for sorted input in LINQ to Entities.
exception)

Any ideas?

I have one so far: to add
Order
field after I
Distinct
, but this means that I will have to write those Contains checks twice which I think is very ugly solution.

Answer

First, since you are projecting unique records due to the different Order value, replace the Union operator with Concat (which is the LINQ equivalent of the SQL UNION ALL).

string[] word = /*Search words*/
var data = db.LessonsLearneds.Where(dbRecord => words.Any(word =>
        dbRecord.SearchKeywords.StartsWith(word + ",") ||
        dbRecord.SearchKeywords.Contains("," + word + ",") ||
        dbRecord.SearchKeywords.EndsWith("," + word)))
        .Select(x => new { Record = x, Order = 1 });

data = data.Concat(
    db.LessonsLearneds
        .Where(dbRecord => words.Any(word => dbRecord.Title.Contains(word)))
        .Select(x => new { Record = x, Order = 2 }));

data = data.Concat(
     db.LessonsLearneds
        .Where(dbRecord => words.Any(word => dbRecord.Description.Contains(word)))
        .Select(x => new { Record = x, Order = 3}));

data = data.Concat(
     db.LessonsLearneds
        .Where(dbRecord => words.Any(word => dbRecord.Lesson.Contains(word)))
        .Select(x => new { Record = x, Order = 4 }));

Then replace the Distinct with GroupBy using x.Record as a key and taking min Order for each grouping, and do the rest as in your current query:

return data
     .GroupBy(x => x.Record)
     .Select(g => new { Record = g.Key, Order = g.Min(x => x.Order) })
     .OrderBy(x => x.Order)
     .ThenByDescending(x => x.Record.Date)
     .Select(x => x.Record)
     .ToPagedList(pageNumber, pageSize);
Comments