C#

When I am Trying to save current list of data into database, I need to get already existing data from database, and need to compare with current list of data.

I have two lists one is PreviousList(existing data from DB) and other is CurrentList(Modified data)


    public class SoftClose
    {
        public int ID = -1;
        public int AID = -1;
        public int WFID = -1;
        public string PREFIX;
        public DateTime SCDATE;
        public string STATUS;
    }


PreviousList
------------
ID		Status		AID		WFID 	PREFIX		SCDATE
1		NO_CHANGE	19		388		D1			01/28/2019
2		NO_CHANGE	20		388		D2			02/25/2019
3		NO_CHANGE	21		388		D3			03/30/2019

CurrentList
-----------
ID		Status		AID		WFID 	PREFIX		SCDATE
1		NO_CHANGE	19		388		D2			01/28/2019
2		NO_CHANGE	20		388		D2			02/25/2019
3		NO_CHANGE	21		388		D3			03/30/2019
4		NO_CHANGE	22		388		D4			04/09/2019


In CurrentList I modified Prefix to D2 where ID=1 and added new row(Id=4)...

My req is

When I am trying to save CurrentList to Db,

If there is any new Prefix in CurrentList that is not there in PreviousList I need to insert that new row and need to change Status to ADD for that row.

I changed Prefix to D2 where Id = 1 in CurrentList. D1 is there is DB and but not in CurrentList so i need to delete it. So i need to change the status to DELETE for that record. I should not insert D2 record where id=1 becuase D2 is already there. If I changed to D5 where Id = 1 then I need to insert it because D5 is not there in DB So i need to change the status to UPDATE.

How to do this? What is the best approach to compare lists

var list1 = new List<ItemOne>
                        {
                            new ItemOne {IDItem = 1, OneProperty = "1"},
                            new ItemOne {IDItem = 2, OneProperty = null},
                            new ItemOne {IDItem = 3, OneProperty = "3"},
                            new ItemOne {IDItem = 4, OneProperty = "4"}
                        };
            var list2 = new List<ItemTwo>
                        {
                            new ItemTwo {IDItem = 2, TwoProperty = "2"},
                            new ItemTwo {IDItem = 3, TwoProperty = "3"},
                        };


            var query = list1.Join(list2, l1 => l1.IDItem, l2 => l2.IDItem, (l1, l2) =>
            {
                l1.OneProperty = l2.TwoProperty;
                return l1;
            });

join b in list2
                       on a.IDItem equals b.IDItem into c
                       from d in c.DefaultIfEmpty()
                       select new
                       {
                           a.OneProperty=(d==null ? string.Empty : d.TwoProperty)
                       }).ToList();

var list1 = new []{
    new { CountryID = 12, CountryData = "Has good gras", regionUID = 4567  },
    new { CountryID = 12, CountryData = "nice weather", regionUID = 6789  },
    new { CountryID = 16, CountryData = "stormy weather", regionUID = 1234 }};

var list2 = new []{
    new { CountryID = 12, CountryName = "green hill"},
    new { CountryID = 16, CountryName = "stormy mountain"}
};

var list3 = new []{
    new {regionUID = 4567, regionName = "above 1000feet"},
    new {regionUID = 6789, regionName = "on the ground"},
    new {regionUID = 1234, regionName = "on the hill"}
};

var result = from m1 in list1
    join m2 in list2 on m1.CountryID equals m2.CountryID
    select new { m2.CountryName, m1.CountryData, m1.regionUID } into intermediate
    join m3 in list3 on intermediate.regionUID equals m3.regionUID
    select new { intermediate.CountryName, intermediate.CountryData, m3.regionName};