c# - How to insert data from MS Access to SQL after checking whether the data exists or not in the database using Entity Framework -

hopefully, question header clear enough tell i'm trying read access file , upload data database checking @ first whether data exists or not in database.

i receive daily report third-party company in access file. i'm trying create windows service check file every morning, , if new file exist, it'll read , upload data database. i'm trying use entity framework. read article on navigation property, i'm still confused on that; never used navigation property before. here models:

    [table("claimstable")]     public partial class claimstable     {             [key]             [databasegenerated(system.componentmodel.dataannotations.schema.databasegeneratedoption.identity)]             public int claimsid { get; set; }             public string eopaid { get; set; }             public string authid { get; set; }             public string pastatus { get; set; }             public string username { get; set; }             [datatype(datatype.date)]             public datetime effectivedate { get; set; }              [datatype(datatype.date)]             public datetime enddate { get; set; }             public string recordtype { get; set; }             public int rxid { get; set; }             public int memberid { get; set; }             public int prescriberid { get; set; }             public string editnumber { get; set; }             public string originsource { get; set; }             public string originmethod { get; set; }     /*             [foreignkey("rxid")]             public virtual rxtable prescription { get; set; }              [foreignkey("memberid")]             public virtual memberstable member { get; set; }              [foreignkey("prescriberid")]             public virtual prescriberstable prescriber { get; set; }     */         } 

...

    [table("memberstable")]     public partial class memberstable     {            [key]             [databasegenerated(system.componentmodel.dataannotations.schema.databasegeneratedoption.identity)]             public int memberid { get; set; }              [datatype(datatype.date), display(name= "date of birth"), displayformat(dataformatstring="{0:mm/dd/yyyy}", applyformatineditmode=true)]             public datetime dateofbirth { get; set; }              public string cardholderid { get; set; }             public string memberfirstname { get; set; }             public string memberlastname { get; set; }             //public virtual icollection<addresstable> address { get; set; }     } 

...

    [table("prescriberstable")]     public partial class prescriberstable     {             [key]             [databasegenerated(system.componentmodel.dataannotations.schema.databasegeneratedoption.identity)]             public int prescriberid { get; set; }             public string npi { get; set; }             public string prescriberfirstname { get; set; }             public string prescriberlastname { get; set; }             public string physiciantype { get; set; }             //public icollection<addresstable> address { get; set; }         } 

....

        using(oledbconnection conn = new oledbconnection(strdsn))         {             oledbdatareader reader = null;             oledbcommand command = new oledbcommand("select * table", conn);             try             {                 conn.open();             }             catch(oledbexception o)             {                 return o.message;             }              reader = command.executereader();              list<claimstable> claim = new list<claimstable>();             list<prescriberstable> prescriberindb = new list<prescriberstable>();             list<memberstable> membersindb = new list<memberstable>();              while(reader.read())             {                 prescriberindb = context.prescribers.tolist();                 membersindb = context.members.tolist();                  //create local variable                 string recordtype =                             //check if member , prescriber exist in database                 int prescriberid = 0;                 int prodid = 0;                 int memberid = 0;                 int drugid = 0;                 int rxid = 0;                 int claimid = 0;                  //check if member , prescriber exist in object before inserted database.                  //the data uploaded database in bulk                  //int newprescriberid = prescriber.where(x => x.prescriberfirstname == reader["prescriber first name"] && x.prescriberlastname == reader["prescriber last name"] && x.npi == reader["prescribing physician"]).select(x => x.prescriberid).firstordefault();                 //int newmemberid = member.where(x => x.memberfirstname == reader["member first name"] && x.memberlastname == reader["member last name"] && x.cardholderid == reader["cardhholder"]).select(x => x.memberid).firstordefault();                  //insert data if doesn't exist                  if(!presciberexist(prescriberfirstname, prescriberlastname, npi, prescriberindb))                  {                     var prescriber = new prescriberstable()                     {                         prescriberfirstname = prescriberfirstname,                         prescriberlastname = prescriberlastname,                         npi = npi,                         physiciantype = physiciantype                                                         };                      context.prescribers.add(prescriber);                     context.savechanges();                     prescriberid = getprescriberid(prescriberfirstname, prescriberlastname, physiciantype, prescriberindb);                 }                 if(!memberexist(memberfirstname, memberlastname, cardholderid, membersindb))                 {                     var member = new memberstable()                     {                         memberfirstname = memberfirstname,                         memberlastname = memberlastname,                         cardholderid = cardholderid,                         dateofbirth = dob                     };                      context.members.add(member);                     context.savechanges();                     memberid = getmemberid(memberfirstname, memberlastname, cardholderid, membersindb);                 }           }     }     return "done uploading"; }  private bool memberexist(string memberfirstname, string memberlastname, string cardholderid, list<memberstable> membersindb) {     return membersindb.exists(x => x.memberfirstname == memberfirstname && x.memberlastname == memberlastname && x.cardholderid == cardholderid); }  private bool presciberexist(string prescriberfirstname, string prescriberlastname, string npi, list<prescriberstable> prescriberindb) {     return prescriberindb.exists(x => x.prescriberfirstname == prescriberfirstname && x.prescriberlastname == prescriberlastname && x.npi == npi); } 

the access database contains sensitive information, won't able add data example. here's made data test. data contains claims of patients.

now, because there many drugs , many claims same patient, , many patients prescriber.. broke database it's shown above. needs improvement? welcome suggestion. reason did because don't want database have repeated records make managing troubling. way, i'll have unique members in memberstable, unique prescribers in prescriberstable , on , forth.

the challenge i'm facing when read data access database, i'm assuming reads row-wise. code should first check database whether member exist or not. if does, member id identity column. if doesn't, should insert member's info only, , memberid. similarly, same thing prescriber's data. check , insert if needed. long way, , way figure out how it.

i know not programming. i'm analyst unfortunately has lot of programming. , i'm learning go. said, there's lot of ways improve code - don't know any. can point me right direction? also, example of how check , insert data if doesn't exist in database using navigation property. currently, data read , uploaded fine, saw in database didn't quite wanted do. still added couple of existing members. needs help.