Entity Framework Core 3.1.5: How to take a table collection logic out side of a linq query to avoid client-side evaluation error?

Question

I've a query like this

public IEnumerable<ContractInquiry> ContractInquiry(Guid itemSoldID)
{
  IEnumerable<ContractInquiry> result;
  using (var context = new ContractDbContext(_ctxOptions))
  {
    var qry = from con in context.Contracts
              join product in context.ContractProducts on con.ID equals product.ContractID
              join service in context.ServiceDetails on con.ID equals service.ContractID into tmpService
              from service in tmpService.DefaultIfEmpty()
              where product.ItemSoldID == itemSoldID
                    && product.ProductStatus != ProductStatus.Deleted.ToString()
                    && con.Status != Status.Deleted.ToString()
              select new ContractInquiry
              {
                 ServiceID = con.ID,
                 ServiceType = con.ServiceType,
                 ServiceDate = service.ServiceDate,
                 ServiceNumber = service.ServiceNumber,
                 ServiceManager = con.Contacts.Where(q => q.Role.Contains(ContractRole.ServiceManager.ToString()))
                                  .OrderBy(o => o.ID).FirstOrDefault()
              };
     result = qry.ToList();
   }
   return result;
}

This query was working fine. But when we upgraded to .NET Core 3.1.5 and Entity Framework Core 3.1.5, it started throwing a client-side evaluation error:

"could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()."

So I had to take the following line out from the query:

ServiceManager = con.Contacts.Where(q => q.Role.Contains(ContractRole.ServiceManager.ToString()))
                                  .OrderBy(o => o.ID).FirstOrDefault()

So re-wrote the query like this:

public IEnumerable<ContractInquiry> ContractInquiry(Guid itemSoldID)
{
  List<ContractInquiry> result;
  using (var context = new ContractDbContext(_ctxOptions))
  {
    var result = (from con in context.Contracts
              join product in context.ContractProducts on con.ID equals product.ContractID
              join service in context.ServiceDetails on con.ID equals service.ContractID into tmpService
              from service in tmpService.DefaultIfEmpty()
              where product.ItemSoldID == itemSoldID
                    && product.ProductStatus != ProductStatus.Deleted.ToString()
                    && con.Status != Status.Deleted.ToString()
              select new ContractInquiry
              {
                 ServiceID = con.ID,
                 ServiceType = con.ServiceType,
                 ServiceDate = service.ServiceDate,
                 ServiceNumber = service.ServiceNumber,
                 Contacts = con.Contacts
              }).ToList();
   }
   
   result.ForEach(con => con.Contacts.Where(q => q.Role.Contains(ContractRole.ServiceManager.ToString()))
                                  .OrderBy(o => o.ID).FirstOrDefault();

   return result;
}

Here

con.Contacts

is a table collection in Contract.cs class

I've added a property like this in ContractInquiry.cs class:

[JsonIgnore]
public IEnumerable<Contact> Contacts { set; get; }

This is working fine as well.

Question: Doing like this will work fine but at run time, the table collection "con.Contacts" will be in memory right? And that will impact the performance of the query right if the table is a huge collection? So is there a work around for this instead of using a memory table? How can I take out the "ServiceManager = .." from the select clause in my first query?

UPDATE: Can someone answer my question?

Answer 1

To answer your question:

• No the whole Contacts table won't be loaded into memory.
• It will be slower than using a database query but unless you have a crazy amount of records you won't be able to 'humanly' measure it (obv. a stress test will point out that this may be slower by 150ms on 10000 records).

Why this is:

EF Core only loads related data and when it is needed. For example you have 1000 of these ContractInquiry records when calling .ToList(). Every one of these records contain ten contacts. Then EF Core will load only 1000*10 contacts. Due to references if any of these overlap they will share memory location and only a reference to it will be saved.

Some changes you can do to make this even faster:

• Change .ToList() to .AsEnumerable(). You can do this because you only iterate over that list once, so you save a whole iteration using .AsEnumerable(). (to create a list the program must iterate over it and then you iterate over it again). Also you are returning an IEnumerable so creating a list is pointless (if you are iterating over it once, which is the case here) unless you later cast it back, which I do not recommend.
• Add .AsNoTracking() in the query. I don't know how you can achieve the same thing with this type of querying (I only use Linq). This will save a lot of time because EF Core will not have to create tracking (and will also save memory).

If you would change the query to a Linq query I would be happy to have a look at it and help you optimise it.