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?

mj1313 · Accepted Answer · 2020-10-13T09:34:11.363

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:

edited Oct 13 '20 at 09:34

answered Oct 13 '20 at 08:18

mj1313

7,930
2
12
32

1

Remove Include, which is not needed here. – Svyatoslav Danyliv Oct 13 '20 at 08:46
@SvyatoslavDanyliv you are right, it do the join when try to get Name in Category. – mj1313 Oct 13 '20 at 09:40
This is of course a workable solution, but the scaffolding generates multiple pages (Index, Details, Edit, Delete, Create) and one would need to make this change in multiple pages. It would present a difficulty in a larger applications. – Sudhashbahu Oct 13 '20 at 09:49
Hi @Sudhashbahu, I think it is inevitable，since ef core doesn't know which fields you want to display in the view, it will not select the designated fields. The fields displayed in different pages may also be different, you need to modify it according to specific scenarios. And If my solution is helpful to you, you could mark it as answer, thanks. :) – mj1313 Oct 14 '20 at 08:40

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

1 Answers1