How to get all linked records from table

Question

I have a MS SQL server with table who is linked on itself

| Id| PreviousId|  Decription |
|---|:---------:|------------:|
| 1 | null      | Blah        |
| 2 | 1         | Blah        |
| 3 | 2         | Blah        |

And I need to get list of all records starting from Id=3.

My list should look like:

public class Record{
    public int Id {get;set;}
    public int? PrevId {get;set;}
    public string Desc {get;set;}
}
List<Record> records= new List<Record>();

/*Code to get all records*/

//Result
record[0] = Record(){Id=3,PrevId=2,Descr="Blah"} 
record[1] = Record(){Id=2,PrevId=1,Descr="Blah"} 
record[2] = Record(){Id=1,PrevId=null,Descr="Blah"}

Thank you!

EDIT1: Sorry guys, but i didn't mentioned that ID are not in order. And there can be situation when, for example, record with ID=17 link to previous record with id =12

This assignment `record[0] = Record(){Id=3,PrevId=2,Descr="Blah"}` is incorrect — Pavel Anikhouski, Feb 24 '20 at 10:29
@PavelAnikhouski: That is just a description of how the result should be — juergen d, Feb 24 '20 at 10:30
_I need to get list of all records starting from Id=3_ why the result contains records with id equals 1 and 2? Please, clarify your question — Pavel Anikhouski, Feb 24 '20 at 10:31
It sounds like you mean you want to order them by Id descending - `var results = records.OrderByDescending(x => x.Id);` — Jonesopolis, Feb 24 '20 at 10:32
I thought that he would like to retrieve the hierarchy. More like "Give me record id 3 and all its ancestors". — G Wimpassinger, Feb 24 '20 at 10:55
Yes, you are right. I need the record and its ancestors, also in sition when ID are not in order — SBarkovskis, Feb 24 '20 at 11:12

score 0 · Answer 1 · answered Feb 24 '20 at 10:31

Assuming your DbContext is something like:

public class MyDbContext : DbContext
{
   public MyDbContext(string connectionString) : base(connectionString) {}
   public DbSet<Record> Records {get; set;}
}

The code to retrieve records from table is:

var db = new MyDbContext(connectionString);
var records = db.Records.ToList();

or, if you want to use LINQ syntax:

var db = new MyDbContext(connectionString);
var recordsQuery = from record in db.Records
                   select record;
var records = recordsQuery.ToList();

Note: since question is pretty basic, I suggest you to have a look at EntityFramework documentation or courses (Julie Lerman courses and books are great, IMO).

Michał Turczyn · Answer 2 · 2020-02-24T11:21:41.960

0

Assuimng your table is named Record, then in your model (context class) you should have property represetning table calles Record or Records (if you set pluralising names in your model). So, getting records from table is done via that property on which you can use normal LINQ methods, such as Where:

var result = dbContext.Records.Where(r => r.Id >= 3).ToList();

ToList() will cause query to execute.

EDIT: to filter based on PreviousId, then use:

var result = dbContext.Records.Where(r => r.PreviousId == 3).ToList();

edited Feb 24 '20 at 11:21

answered Feb 24 '20 at 10:38

Michał Turczyn

32,028
14
47
69

It's ok when all id are in the order, but if if have record with ID=7 and PreviousId=3? – SBarkovskis Feb 24 '20 at 11:08

javachipper · Accepted Answer · 2020-02-24T12:20:09.123

I edited my answer to meet the requirements in getting the heirarchy.

Put this in your main function:

        var idList = GetList();

        //The complete heirarchy list
        var ancestorList = new List<Record>();

        //startId will determine the beginning Id of your query
        int? startId = 3;

        while (true)
        {

            if (startId != null)
            {
                ancestorList.Add(GetList()
                  .Where(m => m.Id == startId)
                  .FirstOrDefault());
            }
            else
            {
                break;
            }

            startId = GetPreviousId((int)startId);
        }

        //this is just for checking the list of ids, this is unnecessary
        foreach (var item in ancestorList)
        {
            Console.WriteLine(item.Id);
        }

        Console.ReadLine();

Retrieving all the data:

public List<Record> GetList(){

     var records = new List<Record>();

     using(YourDBContext db = new YourDBContext())
     {
        records = db.records
         .Select(s=> new Record()
         {
           Id = s.Id,
           PrevId = s.PreviousId,
           Desc = s.Description,
         }).ToList();
     }

     return records;
}

Retrieving the previous Id:

    public static int? GetPreviousId(int startId)
    {
        return GetList()
               .Where(m => m.Id == startId)
               .Select(m => m.PrevId) 
               .FirstOrDefault();
    }

Your solution is OK when id are in order. Please see my Question, I added some edits. Thanks — SBarkovskis, Feb 24 '20 at 11:13
Just to clarify, you want to retrieve all the data that has an Id of 3 and all the previous Id of 3? — javachipper, Feb 24 '20 at 11:19
No, i want to retrieve all records that ar linked together. For exemaple: Id=8 -> Id=6->Id=5->id=3 and so on — SBarkovskis, Feb 24 '20 at 11:30
@SBarkovskis Kindly check, I edited it to meet that requirement. — javachipper, Feb 24 '20 at 12:20

How to get all linked records from table

3 Answers3