Calling functions from within linq statement

Just wondering if this is the most efficient method of doing this? is there a way of having all the linq within one statement instead of calling a method, like a subselect or something?

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

This is the GetEmployeeCurrent开发者_StackOverflow中文版Status which returns the status of the employee:

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

---

how 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 appear cleaner and is functionally ok. However, it is firing up a secondary db call. This will be bad for scalability and performance. The version i've posted uses the same initial db connection and will make the join 1-1. This will result in tighter, faster queries as well as lower resource usage.

---

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

• Call ToList before performing the select that needs to call the method (this way, the method will be called on in-memory data)

• Compose the query such that it can all run on the SQL server if it is possible. This can be done using AsExpandable extension in predicate builder. For more information on how this works, see also my blog post.

---

its fine for small data (employees count) but since each GetEmployeeCurrentStatus requires an sql new connection so its not that best practice. I personally will get all employees (one trip to database) and then get all employees status (one trip to database) so i cashed them all, now i'll join them locally

Hope this helped

---

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

---

Assuming you are using LINQ to SQL or EF, I would refactor your query to use a Join. That way, you will execute a single efficient SQL query on the database, instead of two separate queries.