Entity compare except and insert problem?

Question

Database name: S

Table: STUDENT

ID   NAME   COUNTRYNO   AGE   BRANCHCODE
----------------------------------------
1    Alex    001        25       05
2    Mary    002        26       09

Database name: P

Table PERSON:

 NAME   COUNTRYNO   AGE   BRANCHCODE  
------------------------------------------
 John     127        45      04
 Elize    125        54      06

I want to new table:

Database name: S

Table NEWPERSON

 NAME   COUNTRYNO   AGE   BRANCHCODE  SITUATION
----------------------------------------------------
 John     127        45      04          0
 Elize    125        54      06          0

I want to compare the two tables (countryno and branchcode), and if I don't have the second table values, add them to the new table and situation get it 0.

But this code doesn't run. How to solve in Entity Framework?

var student=DbContext.Entities.Student.Select(a=> new { CountryNo =a.CountryNo, BranchCode=a.BranchCode
});     ------>  //studentcount:0

var person=DbContext.Entities.Student.Select(a=> new { CountryNo =a.CountryNo, BranchCode=a.BranchCode
});     ----> //personcount:0

var common=person.Except(student);   -----> //common:0

List<NEWPERSON> np= new List<NEWPERSON>();   ---> np:0
foreach(var item in common)   //it doesnt enter loop
{
    var ıtem=person.Single(persons=>persons.PERSON==item.PERSON && persons.CountryNo==item.CountryNo);
if(tempItem !=null)
{
    NEWPERSON newperson=new NEWPERSON
{  
     CountryNo=item.CountryNo,
     BranchCode=item.BranchCode,
     Age=item.Age,
     Name=item.Name,
     Situation=0
}
np.Add(newperson);
}

}

You could check if your common variable got any matches, and if no match use the opposite to intersect to get the difference, assign to your personInf variable and set its situation value to 0.. https://stackoverflow.com/a/5620298/3254405 — boateng, Nov 07 '18 at 20:51
Hi.I use 2.way select countryno and branchcode from student except select countryno and branchcode from person.And then I want to output is insert newperson table.But I couldnt. How do I do with Entity or SQL.Can you help in writing the code. — , Nov 07 '18 at 21:08
In my opinion if your only problem here is writing code, then you should be able to do it.. There seems to be some duplication of tables and data here, consider having 1 table with extra columns for foreign keys, or statuses.. — boateng, Nov 07 '18 at 21:15
`But this code doesn't run.` What code? You didn't post any. — Flater, Nov 14 '18 at 14:41

score 1 · Answer 1 · answered Nov 08 '18 at 11:17

If I understand your requirements correctly you need- to extract values from table Person that not exists in Table Student and add them to Table NewPerson.

Now the first thing to underline here is the use of Intersect. From MSDN documentation "The result of Intersect will produce a set of intersection of matching values". Therefor by intersecting the two table your result will be most often an empty set. Another important factor in using Intersect is that intersecting objects must be of the same type. Eg int , Person or Student and "Anonymous"(this is very important).

The other issue with the code is that you create an empty list of NewPerson and add it to the Student table.

A solution I come up with is the following:

get the complete list of objects for students and people

var students = DbContext.Entities.Student().ToList();
var people = DbContext.Entities.Person().ToList();

Instead of Intersect use Except which will give you a set of non-matching elements:

    var peopleNotRegisteredAsStudents = people.Select(person => new { person.CountryNo, person.BranchCode }).Except(
        students.Select(student => new { student.CountryNo, student.BranchCode })
        );

Map the results in peopleNotRegisteredAsStudents as a list of NewPerson:

    List<NewPerson> personInf = new List<NewPerson>();
    foreach (var item in peopleNotRegisteredAsStudents)
    {
        var tempItem = people.SingleOrDefault(person => person.BranchCode == item.BranchCode && person.CountryNo == item.CountryNo);
        if (tempItem != null)
        {
            NewPerson newPerson = new NewPerson
            {
                ID = tempItem.ID,
                Name = tempItem.Name,
                CountryNo = tempItem.CountryNo,
                Age = tempItem.Age,
                BranchCode = tempItem.BranchCode,
                Situation = 0
            };

            personInf.Add(newPerson);
        }
    }

And then add to the NewPerson table

DbContext.Entities.NewPerson.AddRange(personInf);
DbContext.Entities.SaveChanges();

If you need to add the NewPerson results to the Student table you can simply map the NewPerson values as Student objects and add them to Student.

Hope it helps

Entity compare except and insert problem?

1 Answers1