Monday, 15 August 2011

c# - Linq query returning duplicates -



c# - Linq query returning duplicates -

so have 2 datatables, both taken same odbc info source. 1 has rows sales (estado='ventas') , estado='exist' (the database creates row pairs each sale, 1 indicates sale info , other info stock movements off store's deposit, in order know store made sale, have match each ventas row corresponding exist row [by 'documento' id field] , "deposito" value off exist row).

i built query (the odbc driver i'm using doesn't allow joins doing in linq next best thing)

datatable dtresult = new datatable(); dtresult.columns.add("documento", typeof(int64)); dtresult.columns.add("fecha", typeof(datetime)); dtresult.columns.add("articulo", typeof(string)); dtresult.columns.add("deposito", typeof(string)); dtresult.columns.add("impdmn", typeof(decimal)); dtresult.columns.add("cantidad", typeof(decimal)); dtresult.columns.add("partida", typeof(string)); var result = datarows1 in ventasdt.asenumerable() //ventasdt datatable ventas info bring together datarows2 in existdt.asenumerable() // existdt datatable exist info on datarows1.field<int64>("documento") equals datarows2.field<int64>("documento") select dtresult.loaddatarow(new object[] { datarows1.field<int64>("documento"), datarows1.field<datetime>("fecha"), datarows1.field<string>("articulo"), datarows2.field<string>("deposito"), datarows1.field<decimal>("impdmn"), datarows1.field<decimal>("cantidad"), datarows1.field<string>("partida"), }, false); result.copytodatatable();

and works reason duplicates some rows. checked rows duplicated , identical each other. document numbers equal (and there 1 ventas , 1 exist row per documento number, including these duplicates) don't know why they're getting duplicated. checked on source datatables i'm joining , likewise, there 1 corresponding line in each duplicates. exist table larger ventas table (it records transfers between stores aren't sales) joined table larger either (yet not big both combined)

ventas: 85123 exist: 116857 join: 141287

is there wrong query? should include grouping clause or .distinct or should construction (which far i've seen should mimic left join) have been enough?

since reason doing bring together value of "deposito" fields existdt, can replace joining dictionary lookup. here how can it:

var depositopordocumento = existdt.asenumerable().todictionary( dr => dr.field<int64>("documento"), dr => dr.field<string>("deposito") ); foreach (var vr in ventasdt.asenumerable()) { int64 id = field<int64>("documento"); string deposito; if (!depositopordocumento.trygetvalue(id, out deposito)) { continue; } dtresult.loaddatarow(new object[] { id, vr.field<datetime>("fecha"), vr.field<string>("articulo"), deposito, vr.field<decimal>("impdmn"), vr.field<decimal>("cantidad"), vr.field<string>("partida"), }, false); } result.copytodatatable();

c# linq

No comments:

Post a Comment