Acelerar Entity Framework Core

¡No seas avaricioso!

using var ctx = new EFCoreTestContext(optionsBuilder.Options);                
//    ID  ,       !
ctx.FederalDistricts.Select(x=> new { x.ID, x.Name, x.ShortName }).ToList();

using var ctx = new EFCoreTestContext(optionsBuilder.Options);  
//     ID     !
ctx.FederalDistricts.Select(x=> new { x.Name, x.ShortName }).ToList();
ctx.FederalDistricts.Select(x => new MyClass { Name = x.Name, ShortName = x.ShortName }).ToList();

var blogs = context.Blog.ToList(); //       . ?
//     ?
var somePost = blogs.FirstOrDefault(x=>x.Title.StartWidth(“Hello world!”));

var somePost = context.Blog.FirstOrDefault(x=>x.Title.StartWidth(“Hello world!”));

var blogs = context.Blogs.Where(blog => StandardizeUrl(blog.Url).Contains("dotnet")).ToList();

public static string StandardizeUrl(string url)
{
    url = url.ToLower();
    if (!url.StartsWith("http://"))
    {
        url = string.Concat("http://", url);
    }
    return url;
}

var blogs = context.Blogs.AsEnumerable().Where(blog => StandardizeUrl(blog.Url).Contains("dotnet")).ToList();
 
//  
var blogs = context.Blogs.Where(blog => blog.Contains("dotnet"))
    .OrderByDescending(blog => blog.Rating)
    .Select(blog => new
    {
        Id = blog.BlogId,
        Url = StandardizeUrl(blog.Url)
    })
    .ToList();

Veamos las diferencias entre ToList AsEnumerable AsQueryable

• .
• .ToList() (lazy loading), .

• (lazy loading)
• : Func <TSource, bool>
• ( Where/Take/Skip , , select * from Table1,
• , N )
• : Linq-to-SQL + Linq-to-Object.
• IEnumerable (lazy loading).

• (lazy loading)
• :

  AsQueryable(IEnumerable)  AsQueryable<TElement>(IEnumerable<TElement>) 

  
  
• Expression T-SQL ( ), .
• DbSet ( Entity Framework) AsQueryable .
• , Take(5) «select top 5 * SQL» . , SQL , . AsQueryable() , AsEnumerable() T-SQL Linq .
• Use AsQueryable si desea una consulta de base de datos que pueda mejorarse antes de ejecutarse en el lado del servidor.

public IEnumerable<EmailView> GetEmails(out int totalRecords, Guid? deviceWorkGroupID,
                DateTime? timeStart, DateTime? timeEnd, string search, int? confirmStateID, int? stateTypeID, int? limitOffset, int? limitRowCount, string orderBy, bool desc)
        {
            var r = new List<EmailView>();

            using (var db = new GJobEntities())
            {
                var query = db.Emails.AsQueryable();

                if (timeStart != null && timeEnd != null)
                {
                    query = query.Where(p => p.Created >= timeStart && p.Created <= timeEnd);
                }

                if (stateTypeID != null && stateTypeID > -1)
                {
                    query = query.Where(p => p.EmailStates.OrderByDescending(x => x.AtTime).FirstOrDefault().EmailStateTypeID == stateTypeID);
                }


                if (confirmStateID != null && confirmStateID > -1)
                {
                    var boolValue = confirmStateID == 1 ? true : false;
                    query = query.Where(p => p.IsConfirmed == boolValue);
                }

                if (!string.IsNullOrEmpty(search))
                {
                    search = search.ToLower();
                    query = query.Where(p => (p.Subject + " " + p.CopiesEmails + " " + p.ToEmails + " " + p.FromEmail + " " + p.Body)
                                        .ToLower().Contains(search));
                }

                if (deviceWorkGroupID != Guid.Empty)
                {
                    query = query.Where(x => x.SCEmails.FirstOrDefault().SupportCall.Device.DeviceWorkGroupDevices.FirstOrDefault(p => p.DeviceWorkGroupID == deviceWorkGroupID) != null);
                }

                totalRecords = query.Count();
                query = query.OrderByDescending(p => p.Created);
                if (limitOffset.HasValue)
                {
                    query = query.Skip(limitOffset.Value).Take(limitRowCount.Value);
                }
                var items = query.ToList(); //    

                foreach (var item in items)
                {
                    var n = new EmailView
                    {
                        ID = item.ID,
                        SentTime = item.SentTime,
                        IsConfirmed = item.IsConfirmed,
                        Number = item.Number,
                        Subject = item.Subject,
                        IsDeleted = item.IsDeleted,
                        ToEmails = item.ToEmails,
                        Created = item.Created,
                        CopiesEmails = item.CopiesEmails,
                        FromEmail = item.FromEmail,
                    };

                    //     - 

                    r.Add(n);
                }
            }

            return r;
        }

La magia de la lectura simple.

var blogs = context.Blogs.ToList();

var blogs = context.Blogs.AsNoTracking().ToList();

Tipos de carga de datos relacionados

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
            .ThenInclude(post => post.Author)
                .ThenInclude(author => author.Photo)
        .Include(blog => blog.Owner)
            .ThenInclude(owner => owner.Photo)
        .ToList();
}

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);

    var goodPosts = context.Entry(blog)
        .Collection(b => b.Posts)
        .Query()
        .Where(p => p.Rating > 3)
        .ToList();
}

¿Listo para acelerar aún más?

¿Qué pasa con la asincronía?

public void Do()
{
    var myTask = GetFederalDistrictsAsync ();
    foreach (var item in myTask.Result)
    {
         // 
    }
}

public async Task<List<FederalDistrict>> GetFederalDistrictsAsync()
{
    var conn = configurationRoot.GetConnectionString("EFCoreTestContext");
    optionsBuilder.UseSqlServer(conn);
    using var context = new EFCoreTestContext(optionsBuilder.Options);
    return await context.FederalDistricts.ToListAsync();
}

return await context.FederalDistricts.<b>AsNoTracking()</b>.ToListAsync();

//  
public class PopularPosts : ViewComponent
    {
        private readonly IStatsRepository _statsRepository;

        public PopularPosts(IStatsRepository statsRepository)
        {
            _statsRepository = statsRepository;
        }

        public async Task<IViewComponentResult> InvokeAsync()
        {
           //         -
            var federalDistricts = await _statsRepository.GetFederalDistrictsAsync(); 
            var model = new TablePageModel()
            {
                FederalDistricts = federalDistricts,
            };

            return View(model);
        }
    }
    // 
    
    /// <summary>
    ///  -   .... -
    /// </summary>
    public interface IStatsRepository
    {
        /// <summary>
        ///        
        /// </summary>
        /// <returns></returns>
        IEnumerable<FederalDistrict> FederalDistricts();

        /// <summary>
        ///        
	/// !!!
        /// </summary>
        /// <returns></returns>
        Task<List<FederalDistrict>> GetFederalDistrictsAsync();
    }	
	
    /// <summary>
    /// -   .... -
    /// </summary>
    public class StatsRepository : IStatsRepository
    {
        private readonly DbContextOptionsBuilder<EFCoreTestContext>
            optionsBuilder = new DbContextOptionsBuilder<EFCoreTestContext>();
        private readonly IConfigurationRoot configurationRoot;

        public StatsRepository()
        {
            IConfigurationBuilder configurationBuilder = new ConfigurationBuilder()
                    .SetBasePath(Environment.CurrentDirectory)
                    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
            configurationRoot = configurationBuilder.Build();
        }

        public async Task<List<FederalDistrict>> GetFederalDistrictsAsync()
        {
            var conn = configurationRoot.GetConnectionString("EFCoreTestContext");
            optionsBuilder.UseSqlServer(conn);
            using var context = new EFCoreTestContext(optionsBuilder.Options);
            return await context.FederalDistricts.Include(x => x.FederalSubjects).ToListAsync();
        }

        public IEnumerable<FederalDistrict> FederalDistricts()
        {
            var conn = configurationRoot.GetConnectionString("EFCoreTestContext");
            optionsBuilder.UseSqlServer(conn);

            using var ctx = new EFCoreTestContext(optionsBuilder.Options);
            return ctx.FederalDistricts.Include(x => x.FederalSubjects).ToList();
        }
    }

   //         Home\Index 
    <div id="tableContainer">
            @await Component.InvokeAsync("PopularPosts")
     </div>
  //   HTML      Shared\Components\PopularPosts\Default.cshtml

• Iterar sobre los resultados en un bucle for.
• Usar un operador, como ToList, ToArray, Single, Count.
• Enlace de datos de los resultados de la consulta a la interfaz de usuario.

¿Cómo organizar el código de EF Core en términos de arquitectura de la aplicación?

¿Cómo guardar datos correctamente y rápidamente usando SaveChanges ?

using(var db = new NorthwindEntities())
{
var transaction = db.Database.BeginTransaction();

try
{
    //   1
    var  obj1 = new Customer();
    obj1.CustomerID = "ABCDE";
    obj1.CompanyName = "Company 1";
    obj1.Country = "USA";
    db.Customers.Add(obj1);

  //          db.SaveChanges();

    //   2
    var  obj2 = new Customer();
    obj2.CustomerID = "PQRST";
    obj2.CompanyName = "Company 2";    
    obj2.Country = "USA";
    db.Customers.Add(obj2);

    //   
    db.SaveChanges();

    transaction.Commit();
}
catch
{
    transaction.Rollback();
}
}

using(var db = new NorthwindEntities())
{
var transaction = db.Database.BeginTransaction();

try
{
   //  1
    var  obj1 = new Customer();
    obj1.CustomerID = "ABCDE";
    obj1.CompanyName = "Company 1";
    obj1.Country = "USA";
    db.Customers.Add(obj1); 

    //   2
    var  obj2 = new Customer();
    obj2.CustomerID = "PQRST";
    obj2.CompanyName = "Company 2";    
    obj2.Country = "USA";
    db.Customers.Add(obj2);

   //    N 
    db.SaveChanges();

    transaction.Commit();
}
catch
{
    transaction.Rollback();
}
}

//    
public async Task<IActionResult> AddDepositToHousehold(int householdId, DepositRequestModel model)
{
    using (var transaction = await Context.Database.BeginTransactionAsync(IsolationLevel.Snapshot))
    {
        try
        {
            //     
            var deposit = this.Mapper.Map<Deposit>(model);
            await this.Context.Deposits.AddAsync(deposit);

            await this.Context.SaveChangesAsync();

            //    
               var debtsToPay = await this.Context.Debts.Where(d => d.HouseholdId == householdId && !d.IsPaid).OrderBy(d => d.DateMade).ToListAsync();

            debtsToPay.ForEach(d => d.IsPaid = true);

            await this.Context.SaveChangesAsync();

            //   
            var household = this.Context.Households.FirstOrDefaultAsync(h => h.Id == householdId);

            household.Balance += model.DepositAmount;

            await this.Context.SaveChangesAsync();

            transaction.Commit();
            return this.Ok();
        }
        catch
        {
            transaction.Rollback();
            return this.BadRequest();
        }
    }
}

Disparadores, campos calculados, funciones personalizadas y EF Core

Concurrencia en EF Core

public async Task<List<Blog>> GetBlogsAsync()
{
    using (var context = new BloggingContext())
    {
        return await context.Blogs.ToListAsync();
    }
}

¿Qué sabe sobre las consultas compiladas de LINQ?

¡No hagas contextos grandes de DbContext!

Uso de agrupación de contexto (agrupación DdContext)

using System;
using System.Diagnostics;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

namespace Demos
{
    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
        public string Url { get; set; }
    }

    public class BloggingContext : DbContext
    {
        public static long InstanceCount;

        public BloggingContext(DbContextOptions options)
            : base(options)
            => Interlocked.Increment(ref InstanceCount);

        public DbSet<Blog> Blogs { get; set; }
    }

    public class BlogController
    {
        private readonly BloggingContext _context;

        public BlogController(BloggingContext context) => _context = context;

        public async Task ActionAsync() => await _context.Blogs.FirstAsync();
    }

    public class Startup
    {
        private const string ConnectionString
            = @"Server=(localdb)\mssqllocaldb;Database=Demo.ContextPooling;Integrated Security=True;ConnectRetryCount=0";

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<BloggingContext>(c => c.UseSqlServer(ConnectionString));
        }
    }

    public class Program
    {
        private const int Threads = 32;
        private const int Seconds = 10;

        private static long _requestsProcessed;

        private static async Task Main()
        {
            var serviceCollection = new ServiceCollection();
            new Startup().ConfigureServices(serviceCollection);
            var serviceProvider = serviceCollection.BuildServiceProvider();

            SetupDatabase(serviceProvider);

            var stopwatch = new Stopwatch();

            MonitorResults(TimeSpan.FromSeconds(Seconds), stopwatch);

            await Task.WhenAll(
                Enumerable
                    .Range(0, Threads)
                    .Select(_ => SimulateRequestsAsync(serviceProvider, stopwatch)));
        }

        private static void SetupDatabase(IServiceProvider serviceProvider)
        {
            using (var serviceScope = serviceProvider.CreateScope())
            {
                var context = serviceScope.ServiceProvider.GetService<BloggingContext>();

                if (context.Database.EnsureCreated())
                {
                    context.Blogs.Add(new Blog { Name = "The Dog Blog", Url = "http://sample.com/dogs" });
                    context.Blogs.Add(new Blog { Name = "The Cat Blog", Url = "http://sample.com/cats" });
                    context.SaveChanges();
                }
            }
        }
        private static async Task SimulateRequestsAsync(IServiceProvider serviceProvider, Stopwatch stopwatch)
        {
            while (stopwatch.IsRunning)
            {
                using (var serviceScope = serviceProvider.CreateScope())
                {
                    await new BlogController(serviceScope.ServiceProvider.GetService<BloggingContext>()).ActionAsync();
                }

                Interlocked.Increment(ref _requestsProcessed);
            }
        }

        private static async void MonitorResults(TimeSpan duration, Stopwatch stopwatch)
        {
            var lastInstanceCount = 0L;
            var lastRequestCount = 0L;
            var lastElapsed = TimeSpan.Zero;

            stopwatch.Start();

            while (stopwatch.Elapsed < duration)
            {
                await Task.Delay(TimeSpan.FromSeconds(1));

                var instanceCount = BloggingContext.InstanceCount;
                var requestCount = _requestsProcessed;
                var elapsed = stopwatch.Elapsed;
                var currentElapsed = elapsed - lastElapsed;
                var currentRequests = requestCount - lastRequestCount;

                Console.WriteLine(
                    $"[{DateTime.Now:HH:mm:ss.fff}] "
                    + $"Context creations/second: {instanceCount - lastInstanceCount} | "
                    + $"Requests/second: {Math.Round(currentRequests / currentElapsed.TotalSeconds)}");

                lastInstanceCount = instanceCount;
                lastRequestCount = requestCount;
                lastElapsed = elapsed;
            }

            Console.WriteLine();
            Console.WriteLine($"Total context creations: {BloggingContext.InstanceCount}");
            Console.WriteLine(
                $"Requests per second:     {Math.Round(_requestsProcessed / stopwatch.Elapsed.TotalSeconds)}");

            stopwatch.Stop();
        }

¿Cómo evitar errores innecesarios con CRUD en EF Core?

¿Qué hacer cuando las cosas están realmente mal con el rendimiento de la aplicación?