c# - join excel and access using Linq query -
in access table & excel join have status column in access table , want mismatched query account number not equal , status =yes. have tried using , operator not working. xl means excel, ac means access.
var missing = xl.where(x => !ac.any(a => a.accountno == x.accountno));
the above query runs want status =="yes" also
string xlconnstr = @"provider=microsoft.ace.oledb.12.0;data source=" + textselect.text + ";" + "extended properties='excel 8.0;hdr=yes;'"; var xlconn = new oledbconnection(xlconnstr); var da = new oledbdataadapter("select * [mer_svc$]", xlconn); var xldt = new datatable(); da.fill(xldt); datagridview1.datasource = xldt; list<gis> xl = xldt.asenumerable().select(g => new gis() { accountno = g.field<string>("account no"), meterno = g.field<string>("meter no#"), mobile = g.field<string>("mobile "), name = g.field<string>(" name"), geocode = g.field<string>("geo code"), welaiyh = g.field<string>("welaiyh"), area = g.field<string>("area"), region = g.field<string>("region"), }).tolist(); string acconnstr = @"provider=microsoft.ace.oledb.12.0;data source = f:\raec\raecoexcel\customer\customer.mdb;"; var acconn = new oledbconnection(acconnstr); da = new oledbdataadapter("select * gis", acconn); var acdt = new datatable(); da.fill(acdt); list<gis1> ac = acdt.asenumerable().select(g => new gis1() { accountno = g.field<string>("accountno"), stat=g.field<string>("status") }).tolist(); var missing = xl.where(x => !ac.any(a => a.accountno == x.accountno && a.stat == "yes")); datatable dt = xldt.clone(); int count = 0; foreach (var m in missing) { var n = dt.newrow(); n["account no"] = m.accountno; n["meter no#"] = m.meterno; n["mobile "] = m.mobile; n[" name"] = m.name; n["geo code"] = m.geocode; n["welaiyh"] = m.welaiyh; n["area"] = m.area; n["region"] = m.region; dt.rows.add(n); count++; } datagridview1.datasource = dt; messagebox.show(count.tostring()); }
you can try use join clause selecting non-missing values want exclude.
var nonmissing = xl.join(ac, exc => exc.accountno, // select primary key (the first part of "on" clause in sql "join" statement) acs => acs.accountno, // select foreign key (the second part of "on" clause) (exc, acs) => new { exc = exc, acs = acs }).where(x=>x.acs.status =="yes");
with adding select clause end of query, can account numbers want exclude. after that, need selecting account numbers not contained in list of non-missing account numbers.
Comments
Post a Comment