CODEWITHSUNDEEP
c#sqllistobjectjoin
Norrec
Norrec

Reputation: 553

c# how to populate a complex object containing a list with sql

I currently have an object model using Entity Framework that contains a complex object with a property that is a list. I store it in the DB with the second option proposed here: Representing a list in a SQL database

But to summarize:

Person()
{
  long Id;//primary key
  string Name;
  List<long> ResponsibleFor; //list of id    
}

gets stored as the 2 following tables

Persons:
 Id,
 Name

Responsibilities:
 Id,
 PersonId,
 ResponsibleForId

Now I'm kind of stumped with how to repopulate the Person object. My Current approach is the following:

 from p in db.Persons
 join r in db.Responsibilities on p.Id equals r.PersonId
 where p.Id == IdImSearchingFor
 select new Person
 {
  Id = p.Id
  ResponsibileFor = ?
 }

And I'm stuck trying to repopulate that List property.

Any Suggestions?

Thanks!

Upvotes: 1

Views: 2095

Answers (5)

VajNyiaj
VajNyiaj

Reputation: 1910

My suggestion would be to use a Lazy load pattern for getting your list and it would probably perform and scale better. Using your example.

public class Person()
{
  public long Id { get; set; }
  public string Name { get; set;}
  private Lazy<List<long>> _responsibleFor;
  public List<long> ResponsibleFor
  {
    get { return _responsibleFor.Value; }
  }

  public void SetResponsibleFor(Func<long, List<long>> getResponsibleFor)
  {
    _responsibleFor = new Lazy<List<long>>( () => getResponsibleFor(Id) );
  }

  public Person():base() { }
  public Person(long id, string name)
  {
    Id = id;
    Name = name
  }

}

   // Implementation
   var p = new Person(1,"John Doe");
   p.SetResponsibleFor(GetResponsibleForPerson); //Pass a function/method which takes the long for input parameter and outputs List<long>

Upvotes: 0

okrumnow
okrumnow

Reputation: 2416

You need to group the result of the join to get all people responsible for.

Try

var result = from p in db.Persons
             join r in db.Responsibilities on p.Id equals r.PersonId
             where p.Id == IdImSearchingFor
             group r.ResponsibleForId by p into g
             select new Person 
             {
                 Id = g.Key.Id, 
                 Name = g.Key.Name,
                 ResponsibleFor = g.ToList()
             };

Upvotes: 2

Norrec
Norrec

Reputation: 553

Although everyone's previous answers helped me (Thank you!), they weren't completely working. So I thought I'd add my solution here, which was to use a combination of joins, grouping, and nested selects.

from p in db.Persons
where p.Id == IdImSearchingFor
join r in db.Responsibilities on p.Id equals r.ParentId into pr
select new Person
{
    Id = p.Id,
    name = p.Name,
    ResponsibleFor = ( from a in pr
                       where a.ParentId == IdImSearchingFor
                       select new Person
                       {
                           Id = a.Id
                       }).ToList<Person>()
}

Upvotes: 0

samar
samar

Reputation: 5201

Check this one out. I have tested it will normal classes and Lists and not with EF or any other ORM.

The following is the class structure I have understood

class Person
{
    public long Id;//primary key
    public string Name;
}

class Responsibility
{
    public long Id;//primary key
    public long PersonId;
    public long ResponsibleForId; 
}

The following are the 2 test lists

List<Person> Persons = new List<Person>() { new Person() { Id = 1, Name = "Samar" } };
List<Responsibility> Responsibilities = new List<Responsibility>() { new Responsibility() { Id = 1, PersonId = 1, ResponsibleForId = 1 }, new Responsibility() { Id = 2, PersonId = 1, ResponsibleForId = 2 } };

The following is the query

var allPeople = from p in Persons
                join r in Responsibilities on p.Id equals r.PersonId into g
                where p.Id == 1
                select new
                {
                    Id = p.Id,
                    ReponsibleFor = g.Select(x => x.ResponsibleForId).ToList()
                };

Hope this is what you are looking for.

Upvotes: 0

Neel
Neel

Reputation: 11741

Suppose you want to get the person Id 1 then..

from p in db.Persons
 join r in db.Responsibilities on p.Id equals r.PersonId
 where p.Id == 1
 select new Person
 {
  Id = p.Id
  ResponsibileFor = p.ReponsibleFor 
}

Upvotes: 0

Related Questions