Click here to Skip to main content
13,769,022 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
DB1  -  Table Name:STUDENT1

ID   NAME    BRANCHCODE COUNTRYCODE
21   Emily      02          001
26   Alex       56          002
35   Toms       89          003
48   Kelvin     47          004


DB2  -  Table Name:STUDENT2

ID   NAME       BRANCHCODE COUNTRYCODE
14   Mary          32          015
72   Michael       65          066



I want to compare two database and if Available in table 2 and Non-values at 1 table.

I want to add to the new table.And I want to do Status 0.


I don't know how to change the database and I couldn't add the returned value to the new table as a result of the comparison.

DB1 -  Table Name: NEWSTD

ID   NAME       BRANCHCODE COUNTRYCODE   Status
14   Mary          32          015          0
72   Michael       65          066          0


What I have tried:

public static void Tables()
{
    var studentone=Context.Entities.Student1.Select(p=>new { p.BRANCHCODE,p.COUNTRYCODE});
    //change db code dont write.
    var studenttwo=Context.Entities.Student2.Select(p=>new { p.BRANCHCODE,p.COUNTRYCODE});
    var result=studentone.Intersect(studenttwo);

List<NEWSTD> main=new List<NEWSTD>();
foreach(var item in result)
{
  var item2=student2.SingleOrDefault(s=>s.BRANCHCODE==item.BRANCHCODE && s.COUNTRYCODE==item.COUNTRYCODE);
  if(item2 !=null)
{
     NEWSTD st=new NEWSTD
{
      BRANCHCODE=item2.BRANCHCODE,
      COUNTRYCODE=item2.COUNTRYCOD
};
mainbuild.Add(st);
}
}
DbContext.Entities.NEWSTD.AddRange(main);
DbContext.Entities.SaveChanges();
}
Posted 8-Nov-18 10:10am
Updated 8-Nov-18 11:27am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Unless something has fundamentally changed in EF lately, you can't create a new database table using EF.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Why don't you just create a Stored Procedure in the DB and add it to Entity Framework, or call it via ADO?

I'll even give you the SP code
     INSERT db2.dbo.NEWSTD (ID, NAME, BRANCHCODE, COUNTRYCODE)
     SELECT ID, NAME, BRANCHCODE, COUNTRYCODE, 0
     FROM   db2.dbo.STUDENT2
     WHERE  ID NOT IN (SELECT ID FROM db1.dbo.STUDENT1)
--   AND ID NOT IN (SELECT ID FROM db1.dbo.NEWSTD)
The last line which is commented out will prevent duplicates if you un-comment it
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.181117.1 | Last Updated 8 Nov 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100