CODEWITHSUNDEEP
c#linq
Ale Garcia
Ale Garcia

Reputation: 190

Update field IEnumerable regarding duplicates c# EF

I have the following query:

sqlQuery = 'SELECT MPID, MAPPOINT, X, Y, MPCODE, MultipleSite = 0 FROM MAPPOINT'
 var data = context.Database.SqlQuery<WayPoint>(sqlQuery).ToList();

I've pasted the result below.

I'd like to update this result and set MultipleSite Column to 1 in case there are other rows that has the same X and Y values.

For example: records 2 and 3 have the same X and Y values, So I would like to update the value of their column MultipleSite to 1. Do I have to iterate the result and do some comparison, or I can do it any other way?

The idea is to have a flag that tells me that in that coordinates there are other sites as well.

My class looks like this:

public class WayPoint
{
  public int MPID { get; set; }
  public string MAPPOINT { get; set; }
  public double X { get; set; }
  public double Y { get; set; }
  public string MPCODE { get; set; }
  public bool MultipleSite { get; set; }
}

enter image description here

Upvotes: 1

Views: 194

Answers (3)

Greg R Taylor
Greg R Taylor

Reputation: 3676

This can be done in one SQL command...

UPDATE MAPPOINT 
SET [MultipleSite] = 1
FROM MAPPOINT m
INNER JOIN (
    SELECT X, Y FROM MAPPOINT
    GROUP BY X, Y
    HAVING COUNT(*) > 1
) gr on gr.X = m.X 
and gr.Y = m.Y

And if you just want to select these records to manipulate in memory...

SELECT m.* 
FROM MAPPOINT m
INNER JOIN (
    SELECT X, Y FROM MAPPOINT
    GROUP BY X, Y
    HAVING COUNT(*) > 1
) gr on gr.X = m.X 
and gr.Y = m.Y

Upvotes: 1

Ping
Ping

Reputation: 124

Given your data model (i.e., WayPoint), please try the following:

             data.GroupBy(o => new { X = o.X, Y = o.Y })
                .Select(g => new
                {
                    Items = g.Select(gp => new WayPoint
                        {
                            MPID = gp.MPID,
                            MAPPOINT = gp.MAPPOINT,
                            X = g.Key.X,
                            Y = g.Key.Y,
                            MPCODE = gp.MPCODE,
                            MultipleSite = g.Count() > 1
                        })
                }
              )
              .SelectMany(g=>g.Items)
              .ToList();

Upvotes: 1

Kjartan
Kjartan

Reputation: 19121

There are several potential solutions, depending on what you want to achieve, and how you'd prefer to solve it.

You could use something like the following in C# after filling the list data as in your question, and then use the result to update the DB (if that is what you want to do):

foreach(wayPoint in data)
{
    if(data.Any(d => (d.MPID != wayPoint.MPID) 
                     && (d.X == wayPoint.X) 
                     && (d.Y == wayPoint.Y)))
    {
        wayPoint.MultipleSite = 1;
    }
}

If you don't need to update the DB, then that should be fine; otherwise, you could either use the result of the above to update your DB, or you might create a stored procedure in the database to do something similar.

One idea:

  • Create a temp table with the values MPID, X, and Y, and populate it with copies of everything from your main table.
  • Iterate over the resulting temp table and check against the main table with the same criteria as in the if-structure above (select and get the MPID-values for all rows with matching X and Y-values).
  • For each row in the temp table, update the relevant rows in the main table, and then remove the updated rows from the temp-table (Note: Removing all rows in the temp-table that correspond to updated rows in the main table means that you will have to query for at least one row less in the temp table; after all you don't need to check if you already know it has a MultipleSite value of 1).

This may sound heavy / slow, and perhaps there are more efficient ways to do what you want, but it should at least be doable and understandable this way.

Either way, I can't see how you can get around iterating over all rows, since that is in essence, exactly what you want to do; to check for each row if there are others with the same values.

Upvotes: 0

Related Questions