Comparing Data grid view column and columns

Question

I'm stuck on one part whereby I have no idea how to solve it. Basically, I have one table, "Shifthours" and another one which is "employeeshift". Shifthours table have shift_Start and shift_Stop. employeeshift table have StartTime and EndTime. I'm comparing shift_Start and StartTime. I have linked this 2 tables together using foreign key and the question I asked is that I want the shift_Start to compare with the StartTime and shift_Stop to compare with the EndTime and see the employee fit which shift and the shift_Start and shift_Stop will appear at the column that the employee is eligible.

Currently I got a code that only joins 2 table together but not comparing the timings.

private void LoadAllEmpShift()
    {
        using (testEntities Setupctx = new testEntities())
        {
            BindingSource BS = new BindingSource();
            var Viewemp = from ES in Setupctx.employeeshifts
                          join shifthour sh in Setupctx.shifthours on ES.ShiftHourID equals sh.idShiftHours
                         select new
                         {
                             ES.EmployeeShiftID,
                             ShiftHour_Start = sh.shiftTiming_start,
                             ShiftHour_Stop = sh.shiftTiming_stop,
                             ES.EmployeeName,
                             ES.StartTime,
                             ES.EndTime,
                             ES.Date
                         };


            BS.DataSource = Viewemp;
            dgvShift.DataSource = BS;
        }
    }

Anyone knows how to do this?

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

Edit:

You said you were trying to find where the employee hours match with a set of shift times. It would be nice to have some sample data and the algorithm that you want to use to determine what is a good shift time match.

I have assumed here that the best way to do that is to base the employee's start time off the nearest shift start time.

In the following code, I use the let function to essentially look through the shift hours and find the set of shift hours that are nearest to the employee's start time.

var Viewemp = from ES in Setupctx.employeeshifts
      join sh in Setupctx.shifthours on ES.ShiftHourID equals sh.idShiftHours 
         into shifts
      let diff = shifts
          .OrderBy (s => 
                    // this is the line that needs attention:
                    System.Math.Abs((int)(ES.StartTime - s.shiftTiming_start))
                   )
                   .First ()
      select new
      {
          ES.EmployeeShiftID,
          ShiftHour_Start = diff.shiftTiming_start,
          ShiftHour_Stop = diff.shiftTiming_stop,
          ES.EmployeeName,
          ES.StartTime,
          ES.EndTime,
          ES.Date
      };

Update

My type in database for the StartTime and EndTime is string instead of time

In the above code the important logic is finding the absolute value difference between ES.StartTime and s.shiftTiming_start and the smallest difference indicates the best match for shift hour. Unfortunately, your database stores this data as a string and you need to compare them as numeric.

Linq-to-Entities does not contain an easy way to convert string to int function.

I think your next step would be to look into how you can convert those string values to int values. Take a look at this question as I think it might help you out:

Convert String to Int in EF 4.0

edited May 23 '17 at 11:56

Community

1
1

answered Jul 19 '12 at 02:23

Brad Rem

6,036
2
25
50

there's an error at the join stating that there's type interference failed in the call to "join" – Philemon Jul 19 '12 at 03:35
By the way I'm not joining the shift_Start and the StartTime together. I just wanna compare StartTime to a list of shift_Start and match see whether StartTime falls on which time range – Philemon Jul 19 '12 at 03:38
For the second code you have given me, there's a error to this line 'where ES.StartTime >= sh.shiftTiming_start && ES.EndTime <= sh.shiftTiming_stop'. It seems that we shouldn't use >= or <= right? – Philemon Jul 19 '12 at 03:45
You should use [EntityFunctions](http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx) here, e.g. DiffSeconds. – Gert Arnold Jul 19 '12 at 12:45
What does the EntityFunctions means? Does i need to add any references? – Philemon Jul 20 '12 at 01:57
@Philemon: reference System.Data.Entity in your project and add the following using: using System.Data.Objects. See the link in GetArnold's comment. – Brad Rem Jul 20 '12 at 11:00
cannot convert from string to system.timespan? meaning? – Philemon Jul 23 '12 at 01:18
My type in database for the StartTime and EndTime is string instead of time so I can't to use EntityFunctions.DiffMinutes for the codes. – Philemon Jul 23 '12 at 01:41
In that case, @Philemon, just convert the strings to ints and find the difference. I've updated my code. It would also be nice to see a sample of what your times look like. – Brad Rem Jul 23 '12 at 03:04
@BradRem linq to entities does not recognise int.parse the error is prompting at the "BS.DataSource = Viewemp" – Philemon Jul 23 '12 at 03:31
@Philemon, so the problem is that L2Entities doesn't contain an easy way to convert string to numeric. The problem might also be worse depending on how exactly your format your time. Can you show an example of what the time data looks like? – Brad Rem Jul 23 '12 at 14:43
What you mean by time data? the database or what? – Philemon Jul 24 '12 at 01:03
I got 4 columns. 2 is from shifthours table and 2 is from the employeeshift table. shifthours is the foreign key to the employeeshift table. the time is displayed as 0000 etc in this format. – Philemon Jul 24 '12 at 01:11
@BradRem I don't understand what the link gives you. where should I change? – Philemon Jul 25 '12 at 01:21

Comparing Data grid view column and columns

1 Answers1

Update