How to do indexing in entity framework?

Question

ASP.NET

I have a main Users table:

{
      public int Id { get; set; }
                        public int Age { get; set; }
                        public string Name { get; set; }
                        public string Status { get; set; }
    }

It already contains about 350k records (generated for the sake of load tests)
Making selections through linq:
Conditionally -

context.Users.Where(x=>x.Name == name && x.Age == age).FirstOrDefault(x => x.Status == status);

If you run such code on the local - it does not matter, a small jump in the CPU and instant page loading
If you run it through the load. tests (let's say 20 calls every second) - then the load will already be significant and the request will already be processed not for a second, but for 6 seconds.

I heard that in such a situation indexing helps to optimize data sampling, but I didn’t quite understand how to apply it. You just need to specify [Index] under Id? And will indexing be applied to already 350 existing records?

Can you show on the example above how to do indexing and mb there are other options to speed up query processing?

Answer 1

Efcore has nothing to do with it. Efcore just translates your code into an SQL query and sends it to the database. If you don't need to track entities, then use AsNoTracking(). Allows you to work even faster.
You need to optimize the database.
As you said, the index is the right idea. You need to index your Name and Age. Then the data sampling will be significantly faster

Answer 2

1. Enable query logging
https://www.entityframeworktutorial.net/efcore/log...
2. Get your query
3. View the execution plan in Management Studio
4. Add indexes to the table
5. Change the field type to Nvarchar( if needed) MAX) to Nvarchar(100) attribute [MaxLength(100)]
6. Play around with the order context.Users.Where(x=>x.Name == name && x.Age == age) context.Users.Where(x=> x.Age == age && x.Name == name)
7. Add indexes to management studio, see what happens (request should be a fraction of a second)
8. Add index creation to the model
https://docs.microsoft.com/ru- en/ef/core/modeling/...

public class User
    {
        [Key]
        public int Id { get; set; }
        public int Age { get; set; }
        [MaxLength(60)]
        public string Name { get; set; }
        [MaxLength(20)]
        public string Status { get; set; }
        public override string ToString() => $"id: {Id} Name: {Name} Age: {Age} Status: {Status}";
    }
public class UserContext : DbContext
    {
        public static readonly ILoggerFactory LoggerFactory
            = new LoggerFactory(new[] {
                new ConsoleLoggerProvider((category, level) =>
                    category == DbLoggerCategory.Database.Command.Name &&
                    level == LogLevel.Information, true)
            });

        public UserContext() : base()
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseLoggerFactory(LoggerFactory) // для отключения логгирования закоменнтировать строку
                .EnableSensitiveDataLogging() // для отключения логгирования закоменнтировать строку
                .UseSqlServer(@"Server=.;Database=UserTestDB;Trusted_Connection=True;");
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasIndex(b => b.Status)
                .HasName("IX_Status");
            modelBuilder.Entity<User>()
                .HasIndex(b => b.Age)
                .HasName("IX_Age");
            modelBuilder.Entity<User>()
                .HasIndex(b => b.Name)
                .HasName("IX_Name");
        }

        public DbSet<User> Users { get; set; }
    }