CODEWITHSUNDEEP
sql.netentity-frameworklinq
student18
student18

Reputation: 538

Query Performance for multiple IQueryable in .NET Core with LINQ

I am currently updating a BackEnd project to .NET Core and having performance issues with my Linq queries.

Main Queries:

var queryTexts = from text in _repositoryContext.Text
                         where text.KeyName.StartsWith("ApplicationSettings.")
                         where text.Sprache.Equals("*")
                         select text;

var queryDescriptions = from text in queryTexts
                                where text.KeyName.EndsWith("$Descr")
                                select text;

var queryNames = from text in queryTexts
                         where !(text.KeyName.EndsWith("$Descr"))
                         select text;

var queryDefaults = from defaults in _repositoryContext.ApplicationSettingsDefaults
                            where defaults.Value != "*"
                            select defaults;

After getting these IQueryables I run a foreach loop in another context to build my DTO model:

foreach (ApplicationSettings appl in _repositoryContext.ApplicationSettings)
{
  var applDefaults = queryDefaults.Where(c => c.KeyName.Equals(appl.KeyName)).ToArray();

  description = queryDescriptions.Where(d => d.KeyName.Equals("ApplicationSettings." + appl.KeyName + ".$Descr"))
                .FirstOrDefault()?
                .Text1 ?? "";

  var name = queryNames.Where(n => n.KeyName.Equals("ApplicationSettings." + appl.KeyName)).FirstOrDefault()?.Text1 ?? "";

  // Do some stuff with data and return DTO Model
}

In my old Project, this part had an execution from about 0,45 sec, by now I have about 5-6 sec..

I thought about using compiled queries but I recognized these don't support returning IEnumerable yet. Also I tried to avoid Contains() method. But it didn't improve performance anyway.

Could you take short look on my queries and maybe refactor or give some hints how to make one of the queries faster?
It is to note that _repositoryContext.Text has compared to other contexts the most entries (about 50 000), because of translations.

Upvotes: 1

Views: 423

Answers (2)

basundhara singh
basundhara singh

Reputation: 26

one can write queries like below

var Profile="developer"; 
var LstUserName = alreadyUsed.Where(x => x.Profile==Profile).ToList();

you can also use "foreach" like below

lstUserNames.ForEach(x=>
                        {
                           //do your stuff
                        });

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

queryNames, queryDefaults, and queryDescriptions are all queries not collections. And you are running them in a loop. Try loading them outside of the loop.

eg: load queryNames to a dictionary:

var queryNames = from text in queryTexts
                         where !(text.KeyName.EndsWith("$Descr"))
                         select text;
var queryNamesByName = queryName.ToDictionary(n => n.KeyName);

Upvotes: 2

Related Questions