Wsrtjtyk

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