Odtnhj

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);

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