Entity structure, gets the surrounding elements of the selected element

Question

I want to get an element from db and elements that appear before and after it.

var data= repo .OrderBy(a => a.Date)
               .Select((item, index) => new { item, index })
               .Where(itemAndIndex=>itemAndIndex.item.Id == someId)

this is what i got so far.

To clarify,

will say that this is my table

Id     Name        Date
1      SomeText1   01.01.2017
2      SomeText2   03.01.2017
3      SomeText3   02.01.2017
4      SomeText4   04.01.2017
5      SomeText5   05.01.2017

I want to make a select request for Id==3, then sort the result by Date field and get a list of items

Id     Name        Date
1      SomeText1   01.01.2017
3      SomeText3   02.01.2017
2      SomeText2   03.01.2017

like this.

Thank.

Answer 1

Here is the same idea as the Harald Coppoolse answer (which I think is the only reasonable way to satisfy your requirements), but with the best (IMO) LINQ to Entities Translation of SQL queries (if repo- IQueryable<T>representing your table):

var data = repo
    .Where(elem => elem.Id == someId)
    .SelectMany(elem =>
        repo.Where(e => e.Date < elem.Date).OrderByDescending(e => e.Date).Take(1)
        .Concat(new[] { elem })
        .Concat(repo.Where(e => e.Date > elem.Date).OrderBy(e => e.Date).Take(1)))
    .ToList();

Answer 2

:

var data = repo.OrderBy(x => x.Date)
    .Select((item, index) => new { item, index });

var idx = data.First(x => x.item.Id == someId);

var result = data
    .Where(x => x.index >= idx.index)
    .Take(3);

Answer 3

EF, :

public class Data
{
    public int id;
    public string name;
    public DateTime date;
}

class Program
{
    static void Main(string[] args)
    {
        List<Data> l = new List<Data>
        {
            new Data { id = 1, name = "Name1", date = DateTime.Parse("2017/01/01")},
            new Data { id = 2, name = "Name2", date = DateTime.Parse("2017/01/03")},
            new Data { id = 3, name = "Name3", date = DateTime.Parse("2017/01/02")},
            new Data { id = 4, name = "Name4", date = DateTime.Parse("2017/01/04")},
            new Data { id = 5, name = "Name5", date = DateTime.Parse("2017/01/05")},
        };

        int id = 2;

        var result = l.Where(c => c.id == id)
            .Union(l.Where(c => c.date < l.Where(t => t.id == id).Select(d => d.date).First()).OrderByDescending(c => c.date).Take(1))
            .Union(l.Where(c => c.date > l.Where(t => t.id == id).Select(d => d.date).First()).OrderBy(c => c.date).Take(1)).ToList();
    }
}

Answer 4

:

var data= repo.OrderBy(c => c.Date)
           .Where(c => c.Id == id || c.Id == id - 1 || c.Id == id + 1)
           .Select(c => new { c.Id, c.Name,c.Date });

Answer 5

, , someId , .

, someId? NULL?

, ? , ?

, , RepoElements, 3 RepoElements,

• result 1= Id = someId. .
• result [0] = < 1. .
• result [2] = Data > 1.Date

" " 1. Select with Min Max, [0] [2] . , SingleOrDefault, 1:

var result = repo
    .Where(repoElement => repoElement.Id == someId)
    .Select( result1 => new RepoElement[]
    {
        // [0]: the largest element with date < date of item with Id == someId
        repo.Where(repoElement => repoElement.Date < result1.Date)
            .Max(repoElement => repoElement.Date),
        // [1] the item with Id == someId
        result1,
        // [2] the smallest element date > date of item with Id == someId
        repo.Where(repoElement => repoElement.Date > result1.Date)
            .Min(repoElement => repoElement.Date),
    })
    .SingleOrDefault();

, : . : , , , , , . , , ,

: / , Max , NULL

, c.q. , .

, Max, , , . MSDN Enumerable.Max , Max IComparable.

, IComparable, .

private class RepoElement: IComparable<MyData>
{
    public int Id {get; set;}
    public string Name {get; set;}
    public DateTime Date {get; set;}

    public int CompareTo(RepoElement other)
    {   // this object smaller than other object
        // if this date smaller than other date
        return this.Date.CompareTo(other.Date);
    }
}

var result = repo
    .Where(repoElement => repoElement.Id == someId)
    .Select( result1 => new RepoElement[]
    {
        repo.Where(repoElement => repoElement.Date < result1.Date).Max(),
        repoElement,
        repo.Where(repoElement => repoElement.Date > result1.Date).Min(),
    }

Enumerable.Max(), TSource.

, , IEnumerable, IQueryable. .

, IQueryable ( ), Enumerable.Aggregate [0] [2].

, , "", "" , , "", .. , "".

 var result = repo
    .Where(repoElement => repoElement.Id == someId)
    .Select(repoElement => new RepoElement[]
    {
        // [0]: take only the elements that are smaller,
        // aggregate to find the largest one:
        repo.Where(element => element.Date < repoElement.Date)
            .Aggregate(
            // compare the largest item already found, with the current one
            // and take the largest one as largest
            (largest, next) => next.Date > largest.Date ? next : largest),
        // [1]
        repoElement,
        // [2]: take only the elements that are bigger
        // aggregate to find the smallest one
        repo.Where(element => element.Date > repoElement.Date)
            .Aggregate(
            // compare the smallest item already found, with the current one
            // and take the smallest one as smallest
            (smallest, next) => next.Date < smallest.Date ? next : smallest),
    })
    .SingleOrDefault();