1
\$\begingroup\$

What is a better way to create this select statement? I tried to just select 'u' and 'us', but the object properties were not accessible when I did this.

var userSubmittedRequests = (from u in db.TimeRequests
                                     join us in db.UserManagers
                                     on u.EmployeeUserID equals us.UserID
                                     join ut in db.Users
                                     on us.ManagerID equals ut.UserID
                                     where (us.ManagerID == userResult.UserID)
                                     where(u.User.disabled == false)
                                     select new PendingTimeOffViewModel
                                     {
                                       TimeRequestID = u.TimeRequestID,
                                       sDateTime = u.sDateTime,
                                       eDateTime = u.eDateTime,
                                       EmployeeUserID = u.EmployeeUserID,
                                       ManagerID = u.ManagerID,
                                       hrID = u.hrID,
                                       ApproveDenyReasonID = u.ApproveDenyReasonID,
                                       ManagerApproval = u.ManagerApproval,
                                       ManagerActionDate = u.ManagerActionDate,
                                       ManagerComment = u.ManagerComment,
                                       hrApproval = u.hrApproval,
                                       hrActionDate = u.hrActionDate,
                                       hrComment = u.hrComment,
                                       UserSubmitDate = u.UserSubmitDate,
                                       Comment = u.Comment,
                                       DayTypeID = u.DayTypeID,
                                       User = u.User,
                                       CompanyDesc = ut.Company.CompanyDesc,
                                       User1 = u.User1,
                                       User2 = u.User2,
                                       DayType = u.DayType,
                                       ManagerIDUM = us.ManagerID,
                                       ManagerADName = ut.ADUserName,
                                       ManagerName = ut.FullName
                                     }).ToList();

View model:

 public class PendingTimeOffViewModel
 {
    public int TimeRequestID { get; set; }
    public int  CompanyID {get;set;}
    public System.DateTime sDateTime { get; set; }
    public System.DateTime eDateTime { get; set; }
    public int EmployeeUserID { get; set; }
    public Nullable<int> ManagerID { get; set; }
    public Nullable<int> hrID { get; set; }
    public Nullable<int> ApproveDenyReasonID { get; set; }
    public bool ManagerApproval { get; set; }
    public Nullable<System.DateTime> ManagerActionDate { get; set; }
    public string ManagerComment { get; set; }
    public bool hrApproval { get; set; }
    public Nullable<System.DateTime> hrActionDate { get; set; }
    public string hrComment { get; set; }
    public string Comment { get; set; }
    public System.DateTime UserSubmitDate { get; set; }
    public int DayTypeID { get; set; }
    public Nullable<int> HoursRequested { get; set; }
    public virtual ApproveDenyReason ApproveDenyReason { get; set; }
    public virtual DayType DayType { get; set; }
    public virtual User User { get; set; }
    public virtual User User1 { get; set; }
    public virtual User User2 { get; set; }
    public virtual Company Company { get; set; }
    public int UserManagerID { get; set; }
    public Nullable<int> UserID { get; set; }
    public Nullable<int> ManagerIDUM { get; set; }
    public string ManagerADName { get; set; }
    public string ManagerName { get; set; }
    public string CompanyDesc { get; set; }
}
\$\endgroup\$
  • \$\begingroup\$ Is there a particular reason you're using Nullable<int> rather than int?? \$\endgroup\$ – 404 Mar 9 '16 at 21:34
  • \$\begingroup\$ It was a database first design. EF generated the original model and I copy and pasted the properties. \$\endgroup\$ – Waragi Mar 9 '16 at 21:49
3
\$\begingroup\$

Not sure what you mean by a "better" way, but assume it's not having to specify every column. You should be able to do this with an anonymous type, though you'd still have to list navigation properties if you don't lazy load:

var query = [beginning of query] select new { u, us, u.User /* other nav properties */ };

Then you could iterate the results and use a mapping tool (Automapper, FastMapper, ExpressMapper...pick your poison) to map to the VM. Depending on the mapping tool, nav properties may have to be mapped separately:

var userSubmittedRequests = new List<PendingTimeOffViewModel>();

foreach (var request in query.ToList())
{
   userSubmittedRequests.Add(/* Map request to a PendingTimeOffViewModel instance */);
}

BUT, I don't like this approach. It's means you're doing a select * against every table involved: TimeRequests, UserManagers, ApproveDenyReason, etc. Is this for a grid? I've seen too many L2E queries like this start taking forever once the amount of data gets large enough because it's pulling back way more fields than necessary. An approach I've taken for grids is to create a dedicated SQL view that does the joins for me and flattens out the data, then have a DbSet for that view. You can let L2E handle any paging via Take and Skip. And honestly, I'd still rather see every column listed in the L2E select, so SQL is only querying the necessary fields.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Yes it is for a grid. I like the idea of a SQL view for the future. I will keep this in mind. Thank you for your feedback. \$\endgroup\$ – Waragi Mar 10 '16 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.