title: Entity Framework core select causes too many queries
body: <p>I have the following method which is meant to build me up a single object instance, where its properties are built via recursively calling the same method:</p>
  
  <pre><code>public ChannelObjectModel GetChannelObject(Guid id, Guid crmId)
      {
          var result = (from channelObject in _channelObjectRepository.Get(x =&gt; x.Id == id)
                        select new ChannelObjectModel
                        {
                            Id = channelObject.Id,
                            Name = channelObject.Name,
                            ChannelId = channelObject.ChannelId,
                            ParentObjectId = channelObject.ParentObjectId,
                            TypeId = channelObject.TypeId,
                            ChannelObjectType = channelObject.ChannelObjectTypeId.HasValue ? GetChannelObject(channelObject.ChannelObjectTypeId.Value, crmId) : null,
                            ChannelObjectSearchType = channelObject.ChannelObjectSearchTypeId.HasValue ? GetChannelObject(channelObject.ChannelObjectSearchTypeId.Value, crmId) : null,
                            ChannelObjectSupportingObject = channelObject.ChannelObjectSupportingObjectId.HasValue ? GetChannelObject(channelObject.ChannelObjectSupportingObjectId.Value, crmId) : null,
                            Mapping = _channelObjectMappingRepository.Get().Where(mapping =&gt; mapping.ChannelObjectId == channelObject.Id &amp;&amp; mapping.CrmId == crmId).Select(mapping =&gt; new ChannelObjectMappingModel
                            {
                                CrmObjectId = mapping.CrmObjectId
                            }).ToList(),
                            Fields = _channelObjectRepository.Get().Where(x =&gt; x.ParentObjectId == id).Select(field =&gt; GetChannelObject(field.Id, crmId)).ToList()
                        }
                       );
          return result.First();
      }
  
  public class ChannelObjectModel
  {
      public ChannelObjectModel()
      {
          Mapping = new List&lt;ChannelObjectMappingModel&gt;();
          Fields = new List&lt;ChannelObjectModel&gt;();
      }
      public Guid Id { get; set; }
      public Guid ChannelId { get; set; }
      public string Name { get; set; }
      public List&lt;ChannelObjectMappingModel&gt; Mapping { get; set; }
      public int TypeId { get; set; }
      public Guid? ParentObjectId { get; set; }
      public ChannelObjectModel ParentObject { get; set; }
      public List&lt;ChannelObjectModel&gt; Fields { get; set; }
      public Guid? ChannelObjectTypeId { get; set; }
      public ChannelObjectModel ChannelObjectType { get; set; }
      public Guid? ChannelObjectSearchTypeId { get; set; }
      public ChannelObjectModel ChannelObjectSearchType { get; set; }
      public Guid? ChannelObjectSupportingObjectId { get; set; }
      public ChannelObjectModel ChannelObjectSupportingObject { get; set; }
  }
  </code></pre>
  
  <p>this is connecting to a SQL database using Entity Framework Core 2.1.1</p>
  
  <p>Whilst it technically works, it causes loads of database queries to be made - I realise its because of the <code>ToList(</code>) and <code>First()</code> etc. calls.</p>
  
  <p>However because of the nature of the object, I can make one huge <code>IQueryable&lt;anonymous&gt;</code> object with a <code>from.... select new {...}</code> and call <code>First</code> on it, but the code was over 300 lines long going just 5 tiers deep in the hierarchy, so I am trying to replace it with something like the code above, which is much cleaner, albeit much slower..</p>
  
  <pre><code>ChannelObjectType, ChannelObjectSearchType, ChannelObjectSupportingObject
  </code></pre>
  
  <p>Are all <code>ChannelObjectModel</code> instances and Fields is a list of <code>ChannelObjectModel</code> instances.</p>
  
  <p>The query takes about 30 seconds to execute currently, which is far too slow and it is on a small localhost database too, so it will only get worse with a larger number of db records, and generates a lot of database calls when I run it.</p>
  
  <p>The 300+ lines code generates a lot less queries and is reasonably quick, but is obviously horrible, horrible code (which I didn't write!) </p>
  
  <p>Can anyone suggest a way I can recursively build up an object in a similar way to the above method, but drastically cut the number of database calls so it's quicker?</p>