Scott Harrison - The golden apples
Henry Parker's Robot Wars

ASP.Net, ADO, MS Access
C# How to Transform a linq query into a Business Class

C# How to Transform a linq query into a Business Class

(20 hits)
Overview: Hits=6 This code snippet demonstrates, how to transform a TSQL query into a linq query and then transform the linq select dataresult into a business view class using reflection. The Mapper class requires and exact name match and type returned by the linq query. There are reasons not to automate the transformation between the linq query select and the business view class. Reason one, the compiler detects type mismatch; the mapper resolves name and type of the object properties at runtime and errors in type and name can occur; and last, reflections runs slower than direct mapping.

Hits=7 In the code, sample I have demonstrated, the linq to class direct mappings and the linq to class using reflections.

Linq is very comparable to sql. You can perform joins, left joins, group bys, and aggregations. Subquerying in linq is done in the select section using the any method and a lambda expression. I check the TimeAndAttendanceScheduledToWork table to see if an employeeId and scheduleId, exists, indicating the employee was scheduled to work. My linq select statement matches to the name and type of my business class by design.

Hits=6 The mapper class a generic type T defined as TimeAttendanceView which is instantiated The properties of TimeAttendanceView are stored in a variable call propts. The linq IQueryable object uses the TimeAndAttendanceView class property name to retrieve a value and type. The value then is assigned to the new TimeAttendanceView object.

TSQL

SELECT [TimePunchinId]
      ,[PunchinDate]
      ,[PunchinDateTime]
      ,[PunchoutDateTime]
      ,[JobCodeXrefId]
	  ,udcJobCode.Value JobCode
      ,[Approved]
      ,employee.[EmployeeId]
	  ,employeeAddressBook.Name EmployeeName
      ,supervisor.[SupervisorId]
	  ,supervisorAddressBook.Name SupervisorName
      ,[ProcessedDate]
      ,[PunchoutDate]
      ,[Note]
      ,taschedule.[ShiftId]
      ,[mealPunchin]
      ,[mealPunchout]
      ,[ScheduledToWork]
      ,[TypeOfTimeUdcXrefId]
	  ,udcTypeOfTime.value TypeOfTime
      ,[ApprovingAddressId]
      ,[PayCodeXrefId]
	  ,udcPayCode.value PayCode
      ,taschedule.[ScheduleId]
      ,[DurationInMinutes]
	  ,taShift.ShiftName
	  ,taShift.ShiftStartTime
	  ,taShift.ShiftEndTime
	  ,(select iif(taStoW.EmployeeId is not null,1,0) ScheduledToWork from [dbo].[TimeAndAttendanceScheduledToWork] taStoW where taStoW.EmployeeId=employee.EmployeeId
	  and taStoW.ScheduleId=taPunchin.ScheduleId 
	  )ScheduledToWork
  FROM [dbo].[TimeAndAttendancePunchIn] taPunchin 
	

join   udc udcTypeOfTime 
		on udctypeofTime.xrefid=taPunchin.TypeOfTimeUdcXrefId 
	

join  udc udcJobCode 
		on udcjobcode.xrefid=taPunchin.JobCodeXrefId 



	left join  udc udcPayCode 
		on udcPayCode.xrefid=taPunchin.PayCodeXrefId 

	

left join TimeAndAttendanceSchedule taSchedule 
		on taschedule.ScheduleId=taPunchin.ScheduleId 



	left join TimeAndAttendanceShift taShift
		on taPunchin.ShiftId=taShift.ShiftId



	join supervisor supervisor 
		on supervisor.SupervisorId=taPunchin.SupervisorId 


	join addressbook supervisorAddressbook 
		on supervisor.AddressId=supervisoraddressbook.addressid 

	join employee employee 
		on employee.employeeid = tapunchin.EmployeeId 
	join addressbook employeeaddressbook 
		on employee.AddressId=employeeaddressbook.addressid 
	
join addressbook addressbookApproving 
		on addressbookApproving.AddressId=tapunchin.ApprovingAddressId

Linq



   public List<TimeAndAttendanceView> GetTimeAndAttendanceViewsByDate(DateTime filterDate)
        {
            try
            {
                var query = (from taPunchin in _dbContext.TimeAndAttendancePunchIn
                             where taPunchin.PunchinDate >= filterDate

                             join udcTypeOfTime in _dbContext.Udc
                             on taPunchin.TypeOfTimeUdcXrefId equals udcTypeOfTime.XrefId

                             join udcJobCode in _dbContext.Udc
                             on taPunchin.JobCodeXrefId equals udcJobCode.XrefId

                             join udcPayCode in _dbContext.Udc
                             on taPunchin.PayCodeXrefId equals udcPayCode.XrefId into ljPayCode
                             from udcPayCode in ljPayCode.DefaultIfEmpty()

                             join taSchedule in _dbContext.TimeAndAttendanceSchedule
                             on taPunchin.ScheduleId equals taSchedule.ScheduleId

                             join taShift in _dbContext.TimeAndAttendanceShift
                             on taPunchin.ShiftId equals taShift.ShiftId into ljShift
                             from taShift in ljShift.DefaultIfEmpty()

                             join supervisor in _dbContext.Supervisor
                             on taPunchin.SupervisorId equals supervisor.SupervisorId

                             join supervisorAddressBook in _dbContext.AddressBook
                             on supervisor.AddressId equals supervisorAddressBook.AddressId

                             join employee in _dbContext.Employee
                             on taPunchin.EmployeeId equals employee.EmployeeId


                             join employeeAddressBook in _dbContext.AddressBook
                             on employee.AddressId equals employeeAddressBook.AddressId

                             join approverAddressBook in _dbContext.AddressBook
                             on taPunchin.ApprovingAddressId equals approverAddressBook.AddressId

                             select new
                             {
                                 TimePunchinId = taPunchin.TimePunchinId,
                                 PunchinDate = taPunchin.PunchinDate,
                                 PunchoutDate = taPunchin.PunchoutDate,
                                 PunchinDateTime = taPunchin.PunchinDateTime,
                                 PunchoutDateTime = taPunchin.PunchoutDateTime,
                                 PayCode = udcPayCode.Value,
                                 TypeOfTime = udcTypeOfTime.Value,
                                 JobCode = udcJobCode.Value,
                                 ApproverAddressId = approverAddressBook.AddressId,
                                 ApproverName = approverAddressBook.Name,
                                 EmployeeName = employeeAddressBook.Name,
                                 EmployeeId = employee.EmployeeId,
                                 ShiftId = (long?)taShift.ShiftId,
                                 ShiftName = taShift.ShiftName,
                                 ShiftType = taShift.ShiftType,
                                 ScheduleId = taSchedule.ScheduleId,
                                 ScheduleName = taSchedule.ScheduleName,
                                 ScheduleStartDate = taSchedule.StartDate,
                                 ScheduleEndDate = taSchedule.EndDate,
                                 ScheduleGroup = taSchedule.ScheduleGroup,
                                 ScheduledToWork = _dbContext.TimeAndAttendanceScheduledToWork.Any(e => e.EmployeeId == employee.EmployeeId && e.ScheduleId == taSchedule.ScheduleId)
                             }).ToList();

                List<TimeAndAttendanceView> list = new List<TimeAndAttendanceView>();
                //Mapper mapper = new Mapper();  
                foreach (var item in query)
                {
                    //TimeAndAttendanceView taView = mapper.Map<TimeAndAttendanceView>(item);

 		    TimeAndAttendanceView taView = new TimeAndAttendanceView
                    {
                        TimePunchinId = item.TimePunchinId,
                        PunchinDate = item.PunchinDate ?? DateTime.Now,
                        PunchoutDate = item.PunchoutDate ?? DateTime.Now,
                        PunchinDateTime = item.PunchinDateTime,
                        PunchoutDateTime = item.PunchoutDateTime,
                        PayCode = item.PayCode,
                        TypeOfTime = item.TypeOfTime,
                        JobCode = item.JobCode,
                        ApproverAddressId = item.ApproverAddressId,
                        ApproverName = item.ApproverName,
                        EmployeeName = item.EmployeeName,
                        EmployeeId = item.EmployeeId,
                        ShiftId = item.ShiftId ?? 0,
                        ShiftName = item.ShiftName,
                        ShiftType = item.ShiftType,
                        ScheduleId = item.ScheduleId,
                        ScheduleName = item.ScheduleName,
                        ScheduleStartDate = item.ScheduleStartDate ?? DateTime.Now,
                        ScheduleEndDate = item.ScheduleEndDate ?? DateTime.Now,
                        ScheduleGroup = item.ScheduleGroup,
                        ScheduledToWork = item.ScheduledToWork

                    };
                    list.Add(taView);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(GetMyMethodName(), ex);
            }

        }

Mapper Class


  public class Mapper : AbstractErrorHandling
    {
        public T Map<T>(Object o)  where T : new()
        {
            try
            {
                var propts = typeof(T).GetProperties();

                T model;
                object val;

                model = new T();
                foreach (var l in propts)
                {
                    val = o.GetType().GetProperty(l.Name).GetValue(o, null);

                    if (val == DBNull.Value)
                    {
                        l.SetValue(model, null);
                    }
                    else
                    {
                        l.SetValue(model, val);
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception(GetMyMethodName(), ex);
            }
        }
    }

TimeAndAttendanceView Class


    public class TimeAndAttendanceView
    {
        public long TimePunchinId { get; set; }
        public DateTime PunchinDate { get; set; }
        public DateTime PunchoutDate { get; set; }
        public string PunchinDateTime { get; set; }
        public string PunchoutDateTime { get; set; }
        public string PayCode { get; set; }
        public string TypeOfTime { get; set; }
        public string JobCode { get; set; }
        public long ApproverAddressId { get; set; }
        public string ApproverName { get; set; }
        public string EmployeeName { get; set; }
        public long EmployeeId { get; set; }
        public long ShiftId { get; set; }
        public string ShiftName { get; set; }
        public string ShiftType { get; set; }
        public long ScheduleId { get; set; }
        public string ScheduleName { get; set; }
        public DateTime ScheduleStartDate { get; set; }
        public DateTime ScheduleEndDate { get; set; }
        public string ScheduleGroup { get; set; }
        public bool ScheduledToWork { get; set; }

    }
....

...<<<Register to correspond>>> ...

Members : 152
Name:
Email:

Register to View

Help