title: What is a good design pattern to build complex objects from complex database function?
body: <p>I have a complex structure that needs to be built using data read from database, and I need to know where to place my database access logic. </p>
  
  <p>I read about Repository design pattern, but my database operations are not simple CRUD, and they are not just returning simple values.</p>
  
  <p>I am connecting to PostgreSQL and calling functions that often return data as a set of cursors.</p>
  
  <p>Here is a very simplified part of my code... I have omitted some details</p>
  
  <pre><code>class Topic
  {
      public string TopicLabel { get; set; }
  
      public int TopicCode { get; set; }
  
      List&lt;Topic&gt; parentTopics;
  
      public Topic(int topicCode , string topicLabel)
      {
          ...
      }
  
  }       
  
  
  class InitialTopic
  {
      Topic initialTopic;
  
      public int TopicCode { get { return initialTopic.TopicCode; } }            
      Dictionary&lt;int, float&gt; similarityValues;
  
      public InitialTopic( Topic topic)
      {
          initialTopic = topic;
          similarityValues = new Dictionary&lt;int, float&gt;();
      }
  
  }
  
  
  
  class TopicsDictionary
  {
      Dictionary&lt;int, Topic&gt; topics;
  
      public TopicsDictionary()
      {
          topics = new Dictionary&lt;int, Topic&gt;();
      }     
  
      public Topic this[int topicCode]
      {
          get
          {
              Topic t = null;
              if (topics.ContainsKey(topicCode))
              {
                  t = topics[topicCode];                    
              }
              else
              {
                  t = new Topic(topicCode);
                  topics.Add(topicCode, t);
              }
              return t;
          }
      }
  }
  
       .
       .
       .  
  
  public static void GetData(InitialTopic initialTopic)
  {
  
       using (var conn = new NpgsqlConnection(connString))
      {
          conn.Open();
          NpgsqlTransaction tran = conn.BeginTransaction();
  
          NpgsqlCommand command = new NpgsqlCommand("public.\"GetStuff\"", conn);
          .
          .
  
          string cursor1, cursor2;
          using (var dr = command.ExecuteReader())
          {
              dr.Read();
              cursor1 = dr[0].ToString();
              dr.Read();
              cursor2 = dr[0].ToString();
          }                   
  
          using (var resultSet1 = new NpgsqlCommand())
          {
              resultSet1.CommandText = $@"FETCH ALL FROM ""{cursor1}""";
              resultSet1.Connection = conn;
  
              using (var reader = resultSet1.ExecuteReader())
              {
                  // here read values, create Topic objects,
                  // add them to TopicsDictionary and link them using parentTopics list 
                  // to reflect parent-child relation
              }
          }           
  
          using (var resultSet2 = new NpgsqlCommand())
          {
              resultSet2.CommandText = $@"FETCH ALL FROM ""{cursor2}""";
              resultSet2.Connection = conn;
  
              using (var reader = resultSet2.ExecuteReader())
              {
                  // here read values and fill similarityValues 
                  // dictionary in InitialTopic object
              }
          }
  
          tran.Commit();
          conn.Close();
  
      }
  }   
  </code></pre>
  
  <p>Should I separate database operation from the actual building of objects (topic objects and their member lists and dictionaries) ? How should I do that? Is there an appropriate design pattern for such case?</p>