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?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;
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
精彩评论