CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinqasp.net-coreentity-framework-core
user3587624
user3587624

Reputation: 1471

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

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'

Upvotes: 3

Views: 1311

Answers (2)

Sampath
Sampath

Reputation: 65920

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();
     }
    }

Upvotes: 1

Pratik Gaikwad
Pratik Gaikwad

Reputation: 1568

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.

Upvotes: 0

Related Questions