Calling functions from linq expression

Question

Just wondering, is this the most efficient way to do this? is there a way to have all linq inside the same statement instead of calling a method like a subheading or something else?

newEmployee = (from emp in db.employees select new { a.EmployeeID, a.Username, Status = emp.GetEmployeeCurrentStatus(a.Username) }).ToList(); 

This is GetEmployeeCurrentStatus, which returns the employee status:

  public string GetEmployeeCurrentStatus(string username) { using (Entities db = new Entities()) { var times = (from d in db.TimeTables where d.DateTime == DateTime.Today && d.Employee.Username == username select d) .OrderByDescending(d => d.TimeID).FirstOrDefault(); return (x.ClockOut == null ? "IN" : "OUT"); } } 

Answer 1

What about:

 newEmployee = (db.employees.Select(emp => new { emp.EmployeeID, emp.Username, Status = db.TimeTables .Where(d => d.Employee.Username == emp.Username && d.DateTime == DateTime.Today) .Select(x => x.ClockOut == null ? "IN" : "OUT") .FirstOrDefault() })).ToList(); 

Your attempt may look cleaner and functionally normal. However, it launches a secondary db call. This will be bad for scalability and performance. The version I posted uses the same initial db connection and will make the connection 1-1. This will lead to faster, faster requests, as well as lower resource utilization.

Answer 2

You really cannot call a custom method inside a query (or part of a query that will be executed using a database). You have essentially two options:

• Calling ToList before executing select , which should call the method (thus, the method will be called in data in memory)

• Make a request so that it can run on the SQL server, if possible. This can be done using the AsExpandable extension in the predicate builder . For more information on how this works, see also my blog post .

Answer 3

its fine for small data (number of employees), but since each GetEmployeeCurrentStatus requires a new sql connection, so it should not be used as best practice. I personally receive all employees (one trip to the database), and then I receive the status of all employees (one trip to the database), so I cashed them, now I join them locally.

Hope this helps

Answer 4

Regardless of efficiency, using GetEmployeeCurrentStatus (...) as a method makes the code more understandable and more reusable.

Answer 5

Assuming you are using LINQ to SQL or EF, I would reorganize your request to use Join . This way you will execute one efficient SQL query in the database instead of two separate queries.