LINQ to SQL query

Question

Hi,

I have a User entity which represents a User table in my database. This table has a field called ManagerID which references another User record (basically an employee to manager mapping). Each User will also have a reference to another table which can have multiple entries, for the sake of this example we will call it Records.

So the basic rule is every user can have multiple Records and every user can manage a team of other Users.

So as a small example of what I need to do:

User manager = Repository.FindUser('12345');
List<Record> TeamRecordCollection = new List<Record>();

// add the managers records to the team collection
var managerRecords = GetRecords(manager, DateTime.Today);
TeamRecordCollection.AddRange(managerRecords);

// add each individual team members records
foreach (var user in manager.TeamMembers)
{
    var records = GetRecords(user, DateTime.Today);
    TeamRecordCollection.AddRange(records);
}

...

private IEnumerable<Record> GetRecords(User user, DateTime date)
{
    var records = from rec in user.Records
                  where rec.DateSubmitted == date
                  select rec;
    return records.AsEnumerable();
}

// the above code isn't tested it is just a sample idea

So what I would like to know is:

a) Can this be done in the one L2S statement?
b) Would it be more efficient than the current way?

Answer 1

You can use sub-selects or joins:
http://stackoverflow.com/questions/527819/linq-sub-select
to get rid of iterations

Answer 2

How about something like;

var records = from rec in _DB.Records
              from user in _DB.Users.Where(x => x.ManagerId == managerId ||
                                                x.Id == managerId)
              where rec.UserId == user.Id
              select rec;

Answer 3

Also,

Once you find a LINQ2SQL solution for this one, make sure to use SQL profiler to see the execution plan and tweak as needed.

HTH