If it okay to use foreach for a simple query like this? Or should I always use the query thing ?

    public static Staff GetTeacher(Context context, int staffId)
    {
        foreach (Staff staff in context.Staff)
        {
            if(staff.StaffID == staffId)
            {
                return staff;
            }
        }

        return null;
    }

    public static object GetTeacher(Context context, int staffId)
    {
        var staff = from teacher in context.Staff
                    where teacher.StaffID == staffId
                    select new
                    {
                        Id = teacher.StaffID,
                        Teacher = teacher.FirstName   " "   teacher.LastName
                    };
        return staff;              
    }

CodePudding user response：

The foreach will loop over every record in your database table until it finds a match, meaning it might eventually pull every single record into memory. That could be incredibly slow.

The Linq query will construct an SQL statement that only pulls out the relevant record(s).

So the second option is far better. However, it looks like you tried to fix the fact your second query give an enumerable list of staff object by making the function return an object. Instead, you can make the entire thing look much nicer, something like this:

public static Staff GetTeacher(Context context, int staffId)
{
    return context.Staff.Single(s => s.StaffID == staffId);
}

This will be the most efficient method. It will throw an exception if there is no matching staffId in the database. That might be a good thing for you. If not, change it to use SingleOrDefault instead of Single.

Side note: I'm concerned about the method being static. This suggests the DbContext might be shared or long-lived which are both bad things.