ASP.NET Core Web API can not return results extracted using EF Core lazy-loading

Question

I am exploring EF Core lazy-loading, but struggling to return the results over ASP.NET Web API. Please check my API call below.

// GET: api/Supplier
[HttpGet("/GetSupplierUsingLazyLoading")]
public async Task<ActionResult<Supplier>> GetSupplierUsingLazyLoading()
{
    var products = _context.Products.ToList();
    var supplier = products.Last().Supplier;

    return await Task.FromResult(supplier);
    //return await Task.FromResult(new Supplier());
}

Yes. I have added the package Microsoft.EntityFrameworkCore.Proxies

And I have added UseLazyLoadingProxies in my Startup.cs

services.AddDbContext<NorthwindContext>(
        options =>
        {
            options.UseMySql(Configuration.GetConnectionString("Northwind_MySQL"), Microsoft.EntityFrameworkCore.ServerVersion.Parse("8.0.23-mysql"));
            options.UseLazyLoadingProxies();
            options.LogTo(Console.WriteLine, LogLevel.Information);
        }
    );

I am using Pomelo.EntityFrameworkCore.MySql as database provider.

When I call the Web API from swagger then it makes lot of database calls behind the scenes. This is what I see in the terminal. It keeps on making these call. I had to stop the application to stop these calls. I am not sure what I am doing wrong here. Is anyone else facing this issue?

info: 6/26/2021 08:01:55.859 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`OrderDetailsID`, `o`.`Discount`, `o`.`OrderID`, `o`.`ProductID`, `o`.`Quantity`, `o`.`UnitPrice`
      FROM `orderdetails` AS `o`
      WHERE `o`.`OrderID` = @__p_0
info: 6/26/2021 08:01:55.863 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`OrderDetailsID`, `o`.`Discount`, `o`.`OrderID`, `o`.`ProductID`, `o`.`Quantity`, `o`.`UnitPrice`
      FROM `orderdetails` AS `o`
      WHERE `o`.`OrderID` = @__p_0
info: 6/26/2021 08:01:55.867 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`OrderDetailsID`, `o`.`Discount`, `o`.`OrderID`, `o`.`ProductID`, `o`.`Quantity`, `o`.`UnitPrice`
      FROM `orderdetails` AS `o`
      WHERE `o`.`OrderID` = @__p_0
info: 6/26/2021 08:01:55.870 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`OrderDetailsID`, `o`.`Discount`, `o`.`OrderID`, `o`.`ProductID`, `o`.`Quantity`, `o`.`UnitPrice`
      FROM `orderdetails` AS `o`
      WHERE `o`.`OrderID` = @__p_0
info: 6/26/2021 08:01:55.876 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `o`.`OrderDetailsID`, `o`.`Discount`, `o`.`OrderID`, `o`.`ProductID`, `o`.`Quantity`, `o`.`UnitPrice`
      FROM `orderdetails` AS `o`
      WHERE `o`.`OrderID` = @__p_0

When I use return await Task.FromResult(new Supplier()); then it returns an empty supplier.

Thanks Curious Drive

Try mapping the entity manually or with a mapper (like AutoMapper) to a plain object to forcibly fetch the relations before returning the result to the client. i.e. Use a DTO — abdusco
– abdusco, Commented Jun 26, 2021 at 12:12
Unrelated: you can simply return the result in an async function, or return Task.FromResult(val), you don't need to await Task.FromResult(val) — abdusco
– abdusco, Commented Jun 26, 2021 at 12:16
Interesting... I tried accessing one of the properties of supplier (i.e. CompanyName) and returned that in a different Object and it worked. It seems like it can't return the virtual property itself. I will have to copy it in something before returning? — Curious Drive
– Curious Drive, Commented Jun 26, 2021 at 12:20
Yes, you have to try to access it before it tries to query the data from the database. — abdusco
– abdusco, Commented Jun 26, 2021 at 12:21
Hmmm.... That was a curveball. Anyways, thanks for commenting. It's working now. — Curious Drive
– Curious Drive, Commented Jun 26, 2021 at 12:24

Andre.Santarosa · Accepted Answer · 2021-06-26 12:33:47Z

1

First of all: Remember that Lazy-Loading is a feature that will only retrieve the data from database when needed and should be used very carefully.

With this in mind, let's talk about your models. Let's consider that you have a Supplier. This suppliers must be connect with some Orders as you always buys something from a supplier, right? All Orders have OrderItems and each of this items are related to a Product. So we end with a model graph like this:

Your classes should look like this

public class Supplier
{
    public string Name { get; set; }
    List<Order> Orders { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
}

public  class OrderItem
{
    public int OrderItemId { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; }

    public int OrderId { get; set; }
    public Order Order { get; set; }
}

public class Order 
{
    public decimal FinalPrice { get; set; }
    public int SupplierId { get; set; }
    public Supplier Supplier { get; set; }
    List<OrderItem> OrderItems { get; set; }
}

When you try to serialize a Supplier coming from the database, it will also serialize its Orders, then the OrderItems and then finally the Product since your full object graph is serialized.

Remember that the data is only retrieved from database when needed? Well, the serializer serializes each node a time, so if you have 4 order items, it will go and asks the database for the 4 products 4 times.

When you serialize a new Supplier() this doesn't occurs for 2 reasons. A new Supplier() is not tracked by EntityFramework, so no queries are made and even if it was tracked, this supplier has no Orders and thus no additional query will be ran.

answered Jun 26, 2021 at 12:33

Andre.Santarosa

1,2802 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Curious Drive Over a year ago

Thanks for a detailed answer. Microsoft docs should be this detailed.

Andre.Santarosa Over a year ago

You're welcome, buddy! How Lazy-Loading + EntityFramework works can be tricky sometimes.

Collectives™ on Stack Overflow

ASP.NET Core Web API can not return results extracted using EF Core lazy-loading

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related