Linq query using ID returns result to slow (EF Core)

asp.net-core asp.net-mvc entity-framework entity-framework-core linq

Question

I have the following linq query

internal List<ZipCodeInfo> GetInfoFromZipCode(string zipCode)
{
    using (DbContext context = new DbContext())
    {
        IQueryable<ZipCodeInfo> results;

        results = (from a in context.Address
                    where a.ZipCode.Equals(zipCode)
                    select new ZipCodeInfo
                    {
                        Field1 = a.Field1,
                        Field2 = a.Field2,
                        Field3 = a.Field3
                    });

        return results.ToList();
    }
}

But the query itself takes around 5-6 seconds to be completed. I've executed the counterpart query on SQL and it takes almost nothing to complete. Why is it taking that long? The query at the end just returns 4 matches so there is not that much to do here..

This query is part of a Controller class and I am using ASP.NET Core and EntityFramework Core.

The SQL query looks like this, btw.

SELECT *
FROM Address
WHERE ZipCode = '29130'
1
3
9/11/2016 6:12:08 PM

Accepted Answer

You can rewrite above query as shown below.Please let us know about the performance now.

 internal List<ZipCodeInfo> GetInfoFromZipCode(string zipCode)
    {
        using (DbContext context = new DbContext())
        {
           //disabled tracking
           context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

            IQueryable<ZipCodeInfo> results;

            results = (from a in context.Address
                        where a.ZipCode.Equals(zipCode)
                        select new ZipCodeInfo
                        {
                            Field1 = a.Field1,
                            Field2 = a.Field2,
                            Field3 = a.Field3
                        });

            return results.ToList();
     }
    }
1
9/11/2016 5:57:53 PM

Popular Answer

I don't know what version of .Net and entity frameworks are you using, but I found an interesting article here on MSDN. You can go through it. But code can be used as below:

static readonly Func<DbEntities, IQueryable<ZipCodeInfo>> s_compiledQuery2 = 
CompiledQuery.Compile<DbEntities, IQueryable<ZipCodeInfo>>(
(ctx, total) => from a in context.Address
                where a != null and a != "" 
                a.ZipCode.ToUpper().Equals(zipCode.ToUpper())
                select new ZipCodeInfo
                {
                    Field1 = a.Field1,
                    Field2 = a.Field2,
                    Field3 = a.Field3
                });

internal List<ZipCodeInfo> GetInfoFromZipCode(string zipCode)
{            
     using (DbEntities context = new DbEntities())
     {
          IQueryable<ZipCodeInfo> zipCodes = s_compiledQuery2.Invoke(context, zipCode);
          return zipCodes.ToList();
     }            
}

At this point I don't have any remote database to test but again delay to fetch the result of these kind of query will also depends on N\W and number of records being fetched. You can try this solution.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow