How to load a related entity after call AddAsync without making another roundtrip to the database

[zinov]

How to load a related entity after calling AddAsync

I have a repository method that looks like this

public virtual async Task<TEntity> AddAsync(TEntity entity)
{
    if (entity == null)
        throw new ArgumentNullException(nameof(entity));

    try
    {
        entity.CreatedOn = entity.UpdatedOn = DateTime.Now;
        var newEntity = await Entities.AddAsync(entity);
        var newEntityToRet = newEntity.Entity;
        _context.SaveChanges();
        
        return newEntityToRet;
    }
    catch (DbUpdateException exception)
    {
        //ensure that the detailed error text is saved in the Log
        throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
    }
}

Trying to insert an Order for example that looks like this, and only passing the StatusId and the TradingActionId makes the Add safe

public class Order
{
      public int Id { get; set; }
      public bool IsDeleted { get; set; }
      public string CreatedBy { get; set; }
      public string UpdatedBy { get; set; }
      public DateTime? CreatedOn { get; set; }
      public DateTime? UpdatedOn { get; set; }
      public string Symbol { get; set; }
      public int Quantity { get; set; }
      public decimal Price { get; set; }
      public int StatusId { get; set; }
      public OrderStatus Status { get; set; }
      public int TradingActionId { get; set; }
      public TradingAction TradingAction { get; set; }
      public string Notes { get; set; }
}

  var order = new Order
  {
         TradingActionId = 1,
         StatusId = 1,
         Notes = source.Notes,
         Price = source.Price,
         Symbol = source.Symbol,
         Quantity = source.Quantity,
         CreatedOn = dateTimeNow,
         UpdatedOn = dateTimeNow,
         UpdatedBy = "test",
         CreatedBy = "test"
   };

the problem with this is that if I need to return the new entity with certain navigation properties. My following approach doesn't work but shows the idea of what I need to save the instance and at the same time return the child properties. The version I am using of EF Core is Microsoft.EntityFrameworkCore.SqlServer 3.1.4

public virtual async Task<TEntity> AddAsync(TEntity entity, string[] include = null)
{
    if (entity == null)
        throw new ArgumentNullException(nameof(entity));

    try
    {
        entity.CreatedOn = entity.UpdatedOn = DateTime.Now;
        var newEntity = await Entities.AddAsync(entity);
        var newEntityToRet = newEntity.Entity;
        _context.SaveChanges();

        if(include != null)
        {
            foreach (var navProp in include)
            {
                try
                {
                    var memberEntry = _context.Entry(newEntityToRet).Member(navProp);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    throw;
                }

                if (memberEntry is DbCollectionEntry collectionMember)
                    collectionMember.Load();

                if (memberEntry is DbReferenceEntry referenceMember)
                    referenceMember.Load();
            }
        }
        
        return newEntityToRet;
    }
    catch (DbUpdateException exception)
    {
        //ensure that the detailed error text is saved in the Log
        throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
    }
}

Any suggestions of how to create the generic repo method and return the data needed without making another roundtrip to the database?

I posted the question as well on Stackoverflow link

[roji]

First, EF Core doesn't currently allow you to batch your queries and updates applied via SaveChanges. #10879 is about exposing an API whereby users could perform multiple queries in a single roundtrip, and it may be extensible to include SaveChanges as well (I've added a note).

However, your code above raises some questions... Typically, after adding a new entity, there aren't yet any relationships to be loaded back from the database. What exactly is the scenario in which you want to insert a new entity and at the same time query back entities related to it?

Second, forgetting the AddAsync for a second... your code above loops over the include array, and performs an explicit load for each relationship. This means a database roundtrip per relationship, which is indeed quite inefficient. You can simply have a single query with multiple Includes, to load all the navigations in one go. This works particularly well with reference navigations, whereas for collection navigations it may produce the so-called "cartesian explosion" problem (see the docs). Using split query can mitigate this; at the moment split queries usually do a separate roundtrip per relationship (unless using MARS on SqlServer) - so would not be an improvement - but we plan to address that and batch in the future (#10878).

To summarize, I'm still missing a more general understanding of what you're trying to do, and why you think you need to add and retrieve relationships in a single batch etc.