none
[Entity Framework Core] Database update for dbSet<Country> list

    Question

  • Hi,

    I've done some code to remove duplicate country names in a table

    using (var db = new GpaModel())
             {
                    List<Country> countries = db.Countries.ToList();
                    totalItems = countries.Count;

                    countries = countries.DistinctBy(x => x.Name);
                    duplicateItems = totalItems - countries.Count;
                    db.SaveChanges();
            }

    public static List<Country> DistinctBy<Country, TKey>(this List<Country> source, Func<Country, TKey> keySelector)
            {
                var knownKeys = new HashSet<TKey>();
                return source.Where(element => knownKeys.Add(keySelector(element))).ToList();
            }

    In my case totalItems=199 and duplicateOtems=1. The DistinctBy() is so working perfectly. But is I ran 'db.Countries.ToList()' after 'db.SavChanges()', it's still 199 and my duplicate name is still there.

    Is there anything else to update the database ?

    Thanks,

    Vincent

    Friday, April 21, 2017 11:51 PM

Answers

  • So I created for demo purposes a table where the idea is from here.

    So the deal here is some place in the app records get marked for deletion but not actually deleted.

    For this demo I query IsDeleted column for true, iterate those records, set the State property then save changes where save changes shows how many rows where affected.

    using (ForumExamplesEntities entity = new ForumExamplesEntities())
    {
        var deleted = entity.PersonsRemovalDemoes.Where(p => p.IsDeleted == true);
        Console.WriteLine(deleted.Count());
        foreach (var item in deleted)
        {
            entity.Entry(item).State = System.Data.Entity.EntityState.Deleted;
        }
    
        var result = entity.SaveChanges();
        Console.WriteLine(result);
    }

    I validate it worked in SQL-Server Management Studio

    SELECT COUNT(1)
      FROM [ForumExamples].[dbo].[PersonsRemovalDemo]
      WHERE [IsDeleted] = 0

    I could do

    SELECT [id]
          ,[FirstName]
          ,[LastName]
          ,[GenderIdentifier]
          ,[IsDeleted]
      FROM [ForumExamples].[dbo].[PersonsRemovalDemo]
      WHERE LastName = 'Brown'
     

    With

    using (ForumExamplesEntities entity = new ForumExamplesEntities())
    {
        var deleted = entity.PersonsRemovalDemoes.Where(p => p.LastName == "Brown");
        Console.WriteLine(deleted.Count());
        foreach (var item in deleted)
        {
            entity.Entry(item).State = System.Data.Entity.EntityState.Deleted;
        }
    
        var result = entity.SaveChanges();
        Console.WriteLine(result);
    }
    Same as the first, those records that match the where clause are removed


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 7:43 PM
    Moderator

All replies

  • When the record exists in the current context set the State of each item to remove to EntityState.Deleted then execute SaveChanges.

    var customer = customers.First();
    db.Entry(customer).State = EntityState.Deleted;
    Console.WriteLine(db.Entry(customer).State.ToString()); //Deleted


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 1:48 AM
    Moderator
  • In my case, I don't know how many entries from db.Countries will be deleted.

    I can compare 2 lists (the original & the new after call to Dinstinct) but is this the best way ?

    Saturday, April 22, 2017 6:46 AM
  • In my case, I don't know how many entries from db.Countries will be deleted.

    I can compare 2 lists (the original & the new after call to Dinstinct) but is this the best way ?

    Use for for statement on the list that you want to remove.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 10:41 AM
    Moderator
  • can you be more precise, I don't understand.
    Saturday, April 22, 2017 7:20 PM
  • So I created for demo purposes a table where the idea is from here.

    So the deal here is some place in the app records get marked for deletion but not actually deleted.

    For this demo I query IsDeleted column for true, iterate those records, set the State property then save changes where save changes shows how many rows where affected.

    using (ForumExamplesEntities entity = new ForumExamplesEntities())
    {
        var deleted = entity.PersonsRemovalDemoes.Where(p => p.IsDeleted == true);
        Console.WriteLine(deleted.Count());
        foreach (var item in deleted)
        {
            entity.Entry(item).State = System.Data.Entity.EntityState.Deleted;
        }
    
        var result = entity.SaveChanges();
        Console.WriteLine(result);
    }

    I validate it worked in SQL-Server Management Studio

    SELECT COUNT(1)
      FROM [ForumExamples].[dbo].[PersonsRemovalDemo]
      WHERE [IsDeleted] = 0

    I could do

    SELECT [id]
          ,[FirstName]
          ,[LastName]
          ,[GenderIdentifier]
          ,[IsDeleted]
      FROM [ForumExamples].[dbo].[PersonsRemovalDemo]
      WHERE LastName = 'Brown'
     

    With

    using (ForumExamplesEntities entity = new ForumExamplesEntities())
    {
        var deleted = entity.PersonsRemovalDemoes.Where(p => p.LastName == "Brown");
        Console.WriteLine(deleted.Count());
        foreach (var item in deleted)
        {
            entity.Entry(item).State = System.Data.Entity.EntityState.Deleted;
        }
    
        var result = entity.SaveChanges();
        Console.WriteLine(result);
    }
    Same as the first, those records that match the where clause are removed


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 7:43 PM
    Moderator
  • ok that's more clear. it works now. many thanks :)
    Sunday, April 23, 2017 9:01 PM