Tune DB queries for Scaffolding in Razor Pages (.NET and EF Core)

Question

Is there a way to optimize DB queries generated by the out-of-the-box code generated by Scaffolding in Razor Pages? Here is the background information:

I have two tables: ToDos/Tasks and Categories. A task is assigned to a category so there is a FK from Tasks to Categories.

The corresponding Model files:

public partial class Category
{
    public Category()
    {
        Tasks = new HashSet<Todo>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string DummyData { get; set; }

    public virtual ICollection<Todo> Tasks { get; set; }
}

public partial class Todo
    {
        public int Id { get; set; }
        public string Description { get; set; }
        [DataType(DataType.Date)]
        public DateTime? DueDate { get; set; }
        public int CategoryId { get; set; }

        public virtual Category Category { get; set; }
    }

Everything is generated by Scaffolding and there is no custom code. The application runs fine.

However we observe that on the Todo Details and Edit pages, only the Category ID and Name fields are required. Any other fields (such as DummyData) that is part of Categories is not needed for the ToDo pages. However the generated SQL does a INNER JOIN on the two tables and all fields are included.

The corresponding SQL generated:

SELECT [t].[Id], [t].[categoryID], [t].[Description], [t].[DueDate], [c].[Id], [c].[DummyData], [c].[Name]
FROM [Tasks] AS [t]
INNER JOIN [Categories] AS [c] ON [t].[categoryID] = [c].[Id]

Here the column [c].[DummyData] is unnecessary. Ditto is the case for the Todo Edit form. Is there a way to tune the behaviour with minimal effort so that the full field/Column list is not used for "Look up" operations where only the ID+display columns is needed?

Answer 1

You can do that simply by using the "new" operator and selecting the properties from the object that we need:

Linq statement:

var result = _context.Todos
                .Select(t => new
                {
                    t.CategoryId,
                    t.Description,
                    t.DueDate,
                    t.Category.Name
                }).ToList();

SQL generated: