Linq, should I join those two queries together?

Question

I have a Logins table which records when user is login, logout or loginFailed and its timestamp. Now I want to get the list of loginFailed after last login and the loginFailed happened within 24 hrs.

What I am doing now is get the last login timestamp first. then use second query to get the final list. do you think I should join those two queris together? why not? why yes? and How if yes.

                    var lastLoginTime = (from inRecord in db.Logins
                                  where inRecord.Users.UserId == userId
                                      && inRecord.Action == "I"
                                  orderby inRecord.Timestamp descending
                                         select inRecord.Timestamp).Take(1);
                    if (lastLoginTime.Count() == 1)
                    {
                        DateTime lastInTime = (DateTime)lastLoginTime.First();

                        DateTime since = DateTime.Now.AddHours(-24);
                        String actionStr = "F";    
                        var records = from record in db.Logins
                                      where record.Users.UserId == userId
                                          && record.Timestamp >= since
                                          && record.Action == actionStr
                                          && record.Timestamp > lastInTime
                                      orderby record.Timestamp 
                                      select record;
                    }

Answer 1

In the long run, I don't think it'd matter. No matter how you actually build the query in LINQ to SQL, the ultimate sequence of events on the DB server will be

1. get lastInTime
2. use lastInTime as part of records filter

Now... doing it as part of a single query will save on roundtrips of the actual date-time, so you can get some performance that way. But I would suggest that you only try to merge them if you absolutely need to because your performance profiling suggested that query was a bottleneck.

Answer 2

I don't think you should combine them because your current queries are quite readable. I think if they were combined it would be more difficult to understand the code.

Answer 3

I wouldn't merge, for reasons already stated by everyone else, but you can simplify the first query a bit: instead of

orderby inRecord.Timestamp descending
select inRecord.Timestamp).Take(1);

you can simply say:

select inRecord.Timestamp).Max();

It'll do the same thing, but it's a bit clearer than your way.

Answer 4

You can also use the IQueryable objects to compose more complex queries and still keep the code pretty easy to read. (I mixed the Extension syntax and query syntax just to show it can be done. You can just as easily swap this code around to separate it out as you would any other code in your solution.)

var usersRecords = db.Logins.Where(r => r.Users.UserId == userId);

var userLoginTimes = usersRecords.Where(r => r.Action == "I")
                                 .Select(r => r.Timestamp);

var usersFunctions = usersRecords.Where(r => r.Action == "F");

var records = from record in usersFunctions
              where userLoginTimes.Any()
              let lastLoginTime = userLoginTimes.Max()
              where record.Timestamp >= since
                 && record.Timestamp > lastLoginTime
              select record;