Entity framework performance slow

You can consider the Entity Framework Pre-Generated Mapping Views.You can use EF Power Tools to create pre-generate views.

Using pre-generated views moves the cost of view generation from model loading (run time) to compile time. While this improves startup performance at runtime, you will still experience the pain of view generation while you are developing. There are several additional tricks that can help reduce the cost of view generation, both at compile time and run time.

You can refer this for knowing more about it : Entity Framework Pre-Generated Mapping Views

You can use Caching in the Entity Framework to improve the performance of your app.

There are 3 types of caching.

1. Object caching – the ObjectStateManager built into an ObjectContext instance keeps track in memory of the objects that have been retrieved using that instance. This is also known as first-level cache.

2. Query Plan Caching - reusing the generated store command when a query is executed more than once.

3. Metadata caching - sharing the metadata for a model across different connections to the same model.

You can refer this article to read more about it : Performance Considerations for EF 6

I recently had a simple query that runs super quick in SSMS that was taking way, way too long to run using the Entity Framework in my C# program.

This page has been extremely helpful, when trouble shooting EF performance problems in general:

https://www.simple-talk.com/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/

..but in this case, nothing helped. So in the end, I did this:

        List<UpcPrintingProductModel> products = new List<UpcPrintingProductModel>();
        var sql = "select top 75 number, desc1, upccode "
                + "from MailOrderManager..STOCK s "
                + "where s.number like @puid + '%' "
        ;
        var connstring = ConfigurationManager.ConnectionStrings["MailOrderManagerContext"].ToString();
        using (var connection = new SqlConnection(connstring))
        using (var command = new SqlCommand(sql, connection)) {
            connection.Open();
            command.Parameters.AddWithValue("@puid", productNumber);
            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    var product = new UpcPrintingProductModel() {
                        ProductNumber = Convert.ToString(reader["number"]),
                        Description = Convert.ToString(reader["desc1"]),
                        Upc = Convert.ToString(reader["upccode"])
                    };
                    products.Add(product);
                }
            }
        }

(For this particular query, I just completely bypassed the EF altogether, and used the old standby: System.Data.SqlClient.)

You can wrinkle your nose in disgust; I certainly did - but it didn't actually take that long to write, and it executes almost instantly.

You also can circumvent this issue asynchronously "warming" your dbcontexts at application start moment.

protected void Application_Start()
 {
   // your code.
    // Warming up.
            Start(() =>
            {
                using (var dbContext = new SomeDbContext())
                { 
                    // Any request to db in current dbContext.
                    var response1 = dbContext.Addresses.Count();
                }
            });
 }

 private void Start(Action a)
 {
    a.BeginInvoke(null, null);
 }

I also recommend use such settings as: (if they fit your app)

• dbContext.Configuration.AutoDetectChangesEnabled = false; dbContext.Configuration.LazyLoadingEnabled = false; dbContext.Configuration.ProxyCreationEnabled = false;

• Skip validation part ( ie Database.SetInitializer<SomeDbContext>(null);)

• Using .asNoTraking() on GET queries.

For additional information you can read:

• https://msdn.microsoft.com/en-in/data/hh949853.aspx
• https://www.fusonic.net/en/blog/3-steps-for-fast-entityframework-6.1-code-first-startup-performance/
• https://www.fusonic.net/en/blog/ef-cache-deployment/
• https://msdn.microsoft.com/en-us/library/dn469601(v=vs.113).aspx
• https://blog.3d-logic.com/2013/12/14/using-pre-generated-views-without-having-to-pre-generate-views-ef6/

In some cases EF does not use Query Plan Caching. For example if you use contans, any, all methods or use constants in you query. You can try NihFix.EfQueryCacheOptimizer. It convert your query expression that allow EF use cahce.