Cfrgtkky

I have an app where the user inputs search terms which are used to query a database and return matching rows. I'd like to find the rows that are duplicated like this:

File  Tool  Product  Time1          MeasureTime      Row

319   S32   AX       11/13 1:12AM   11/13 5:02am     5

318   S32   AX       11/13 1:12AM   11/13 4:41am     5

In these cases the entry with the higher file ID may be an errant re-measurement so I'd like to be able to highlight this to the user.

I have this code that counts if there are duplicate rows:

                    var duplicates = db2.AsEnumerable()

                    .GroupBy(r => new

                    {

                        Tool = r.Field<string>("Tool"),

                        Product = r.Field<string>("Product"),

                        Time1 = r.Field<DateTime>("Time1"),

                        Row = r.Field<Int32>("Row")

                    }).Select(g => new {

                        Tool = g.Key.Tool,

                        Product = g.Key.Product,

                        Time1 = g.Key.Time1,

                        Row = g.Key.Row,

                        Count = g.Count() }).ToList();

This returns a generic list that I'd like to be able to take the count from and add it back to the initial datatable db2. Then when a user selects a row where the count is greater than 1 I can alert the user. I would like to only alert when the user picks the higher file number so I may need to do this with a ranking, but either way, how can I take what I have and add it back to the original table?

As written above you'll need to create a new list because you've lost the references to the original data:

var updateList = db2.Where(r=> Tool = r.Field<string>("Tool") 

  && Product = r.Field<string>("Product")

  && Time1 = r.Field<DateTime>("Time1")

  && Row = r.Field<Int32>("Row"));



foreach (var ul in updateList)

  ul.Count = updateList.Count();

Alternatively, if you remove the Select from your code you can retain references to the original data:

var duplicates = db2.AsEnumerable()

  .GroupBy(r => new

  {

    Tool = r.Field<string>("Tool"),

    Product = r.Field<string>("Product"),

    Time1 = r.Field<DateTime>("Time1"),

    Row = r.Field<Int32>("Row")

  });



foreach (var d in duplicates)  //Iterate through the groups

{

  foreach (var item in d)  //Iterate through the items in a group

  {

    item.Count = d.Count();

  }

}

Apparently you designed that you can identify a measurement by the values of Tool, Product, etc: if you've got two items with same values of Tool, Product, etc, they belong to the same measurement group, and might even be the same measurement.

To prevent that I have to say "same Tool, Product, etc" over and over again, I call the collection of these properties the MeasurementId. So whenever I say MeasurementId, I mean the properties Tool/Product/Timel/Row

Problem description

If you've got two measurements with the same MeasurementId, it might be that they are re-measurements. Therefore, whenever the operator selects a measurement of which there are others with the same MeasurementId, you want to warn the operator, maybe not if the operator selects the oldest measurement.

Your problem seems to be similar as keeping several versions of something in a database. If someone adds a new item with the same ``MeasurementId. but with a differentFile, it is as if you add a newVersion` of the measurement to your database.

You want to warn the operator if he doesn't select the oldest version.

You chose the following solution:

I'd like to be able to take the count from and add it back to the initial datatable db2

Instead of adding the count, to your table, consider adding the Id of the "previous version", and if there is no previous version, add 0.

class Measurement

{

     public int Id {get; set;}

     public int File {get; set;}



     // measurement identification

     public string Tool {get; set;}

     public string Product {get; set;}

     ...



     // you wanted to add a Count, instead add a previous version

     public int PreviousMeasurementId {get; set;}   // 0 if no previous measurement

}

Before you add a measurement check if there is already a similar measurement:

void AddMeasurement(Measurement measurementToAdd)

{

     var lastMeasurementVersionId = dbContext.Measurements

         .Where( measurement => // select with same measurementId:

             measurement.Tool == measurementToAdd.Tool

             && measurement.Product == measurementToAdd.Product

             && ...)

         // from the remaining versions, keep the one with the highest File:

         .OrderByDescending(measurement => measurement.File)

         // I'm only interested in the Id of this measurement

         .Select(measurement => measurement.Id)

         .FirstOrDefault();

Now if there were already similar measurements, lastMeasurementVersionId is the Id of the last similar measurement. If there were none, this value equals 0.

Add the new measurement after assigning lastMeasurementVersionId to PreviouseMeasurementId:

    measurementToAdd.PreviousMeasurementId = lastMeasurementVersionId;

    dbContext.Measurements.Add(measurementToAdd);

    dbContext.SaveChanges();

}

How will this help me with my problem?

If the operator selects a Measurement, the only thing you'll have to do is check the value of PreviousMeasurementId. If it is zero, the operator selected the first measurement, if not, you can warn the operator that there are several versions of this measurement. The selected one might be a re-measurements.

Possible improvements:

• Consider adding an extra index for your composite MeasurementId. The values of a MeasurementId won't change often, but the query to fetch all measurement with this MeasurementId is much faster


• If you are not interested in all versions, but really only in the first version, don't remember the PreviousMeasurementId, but remember the FirstMeasurementId.