All geek questions in one place

Avoid consecutive nested. Anyone in C # LINQ

I have a list of BossList collections. In this, I use the nested .Any () to define the condition. Now performance is very slow in my real project. Consider the following source code example.

void Main() { List<Boss> BossList = new List<Boss>() { new Boss() { ID = 101, Name = "Harry", Department = "Development", Gender = "Male", Role = "Manager", Employees = new List<Person>() { new Person() { ID = 101, SID = 102, Name = "Peter", Department = "Development", Gender = "Male", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } }, new Person() { ID = 101, SID = 103, Name = "Emma Watson", Department = "Development", Gender = "Female", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } }, } }, new Boss() { ID = 104, Name = "Raj", Department = "Development", Gender = "Male", Role = "Manager", Employees = new List<Person>() { new Person() { ID = 104, SID = 105, Name = "Kaliya", Department = "Development", Gender = "Male", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } }, new Person() { ID = 104, SID = 103, Name = "Emma Watson", Department = "Development", Gender = "Female", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } }, }, }, new Boss() { ID = 102, Name = "Peter", Department = "Development", Gender = "Male", Role = "Manager", Employees = new List<Person>() { new Person() { ID = 102, SID = 105, Name = "Kaliya", Department = "Development", Gender = "Male", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } }, new Person() { ID = 102, SID = 103, Name = "Emma Watson", Department = "Development", Gender = "Female", Role = "Assistant", PayInfo = new List<PayrollInfo>() { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 }, } } } } }; BossList.Where(i => i.Employees.Any(j => j.PayInfo.Any(s => s.AbsentDays >6))).Select(m => m.Name).Dump(); }

Model Classes

 public class Person { public int ID { get; set; } public int SID { get; set; } public string Name { get; set; } public string Department { get; set; } public string Gender { get; set; } public string Role { get; set; } public List<PayrollInfo> PayInfo { get; set; } } public class Boss { public int ID { get; set; } public int SID { get; set; } public string Name { get; set; } public string Department { get; set; } public string Gender { get; set; } public string Role { get; set; } public List<Person> Employees { get; set; } } public class PayrollInfo { public int Monthof2015 { get; set; } public int NetWorkingDays { get; set; } public int AbsentDays { get; set; } }

LINQ basic query

 BossList.Where(i => i.Employees .Any(j => j.PayInfo .Any(s => s.AbsentDays >6))) .Select(m => m.Name);

In my main project, it contains 6 nested .Any (). Here I can not create because of the long code.

I use query in EF as IQueryable. The database contains more than 1000K records.

Please suggest me if there is an effective way to maximize performance?

+5
source share
3 answers

Probably the best way to do this still uses the BossList as a starting point, but it builds a list of identifiers at which their identifiers should run:

 db.BossList.Where(b => db.PayrollInfos.Where(s => s.AbsentDays >6) .Select(p => p.Employee.BossId) .Contains(b.BossId));

In SQL, this will likely go into a neat and efficient EXISTS query.

Note that I am assuming that in your real model you have backlinks ( PayrollInfos.Employee ) and I used the placeholder BossId , which you should replace with valid key properties. From your example, it’s a little unclear how the ID and SID links work.

db is your instance of DbContext .

To explain this approach a little more: in my experience, it is usually best to start a query with a collection containing the results you are after, i.e. BossList , and then add predicates that filter the results. Other answers start with PayrollInfo . This is fine, but in the end you need grouping or Distinct to remove duplicates. Usually this does not make the query plan better than the relatively simple table WHERE EXSIST( subqyery ) .

+1
source

What if you add something like a foreign key to the "parent" in each object?

 public class PayrollInfo { public int Monthof2015 { get; set; } public int NetWorkingDays { get; set; } public int AbsentDays { get; set; } public Person Person { get; set; } }

And when you can set a new property when creating the Person parameter - in the constructor:

 public Person(IList<PayrollInfo> list) { this.PayInfo = list; foreach(var pay in this.PayInfo) pay.Person = this; }

Or just define the method that installs PayInfo . As you want. And then do the same for Person and Boss. And then you can write smth like

 AllPayrollInfos.Where(x => x.Days > 6).GroupBy(x => x.Person.Boss).Select(x => x.Key).ToList();
+1
source

Assuming this is an overturned EF request; I would deselect your choice to start with PayrollInfo, and then select the name Boss:

 Boss Boss = new Boss { ID = 101, Name = "Harry", Department = "Development", Gender = "Male", Role = "Manager", }; Person Person = new Person() { ID = 101, SID = 102, Name = "Peter", Department = "Development", Gender = "Male", Role = "Assistant", Boss = Boss}; List<PayrollInfo> PayrollInfoList = new List<UserQuery.PayrollInfo> { new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6, Person = Person }, new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3, Person = Person }, new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2, Person = Person }, new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1, Person = Person }, new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4, Person = Person }, new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6, Person = Person }, new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4, Person = Person }, new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3, Person = Person }, new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8, Person = Person }, new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9, Person = Person }, new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4, Person = Person }, new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1, Person = Person }, }; PayrollInfoList.Where(p => p.AbsentDays > 6).Select(p => p.Person.Boss.Name).Distinct().Dump();
+1
source

Source: https://habr.com/ru/post/1247039/

More articles:


All Articles