vb.net - Linq join on parameterized distinct key CASE INSENSITIVE -
to revisit previous question further stipulation...
anyone know how following, ignoring case?
dim matches = mrows in linqmastertable join srows in linqsecondtable _ on mrows(theprimarykey) equals srows(theforignkey) _ order mrows(theprimarykey) _ select mrows, srows for details query , it's functions / usage, previous post here.
edit:
here's kind of tables we're querying:
linqmastertable: ------------------------------------- |theprimarykey| description | ------------------------------------- |green | green apple | |green | green apple | |green | green apple | |red | red apple | |red | red apple | |red | red apple | ------------------------------------- linqsecondtable -------------------------- |theforignkey | appleprice | -------------------------- |green | $0.90 | |pink | $0.80 | |red | $0.85 | |yellow | $0.79 | -------------------------- and here's desired outcome:
-------------------------------------- |green | green apple | $0.90 | |green | green apple | $0.90 | |green | green apple | $0.90 | |red | red apple | $0.85 | |red | red apple | $0.85 | |red | red apple | $0.85 | -------------------------------------- unfortunately, actual (undesired) result this:
-------------------------------------- |green | green apple | $0.90 | |red | red apple | $0.85 | -------------------------------------- reedit:
private sub linqtwotableinnerjoincaseinsensitive(byref qmasterds dataset, _ byref qmastertable datatable, _ byref qsecondds dataset, _ byref qsecondtable datatable, _ byref qprimarykey string, _ byref qforignkey string, _ byval qresultsname string) dim themastertable string = qmastertable.tablename 'table name' dim thesecondtable string = qsecondtable.tablename 'table name' dim theprimarykey string = qprimarykey 'the variable name of first 'merge on' column' dim theforignkey string = qforignkey 'the variable name of second 'merge on' column' dim thenewforignkey string = "" 'for handling duplicate column names' mastertablecolumns = getcolumns(qmasterds, themastertable) secondtablecolumns = getcolumns(qsecondds, thesecondtable) dim mcolumncount integer = mastertablecolumns.count dim scolumncount integer = secondtablecolumns.count dim columncount integer = mcolumncount + scolumncount dim linqmastertable = qmasterds.tables(themastertable).asenumerable dim linqsecondtable = qsecondds.tables(thesecondtable).asenumerable 'original linq query: (works, case sensitive)' dim matches = mrows in linqmastertable join srows in linqsecondtable _ on mrows(theprimarykey) equals srows(theforignkey) _ order mrows(theprimarykey) _ select mrows, srows 'intellisense doesnt see "toupper" available. no errors, no search results.' 'error: public member "toupper" on type "dbnull" not found.' 'dim matches = mrows in linqmastertable join srows in linqsecondtable _' ' on mrows(theprimarykey).toupper equals srows(theforignkey).toupper _' ' order mrows(theprimarykey) _' ' select mrows, srows' 'message = "public member "srows" on type "string" not found."' 'dim matches2 = mrows in linqmastertable _' 'from srows in linqsecondtable _' 'where string.equals(mrows(theprimarykey), srows(theforignkey), stringcomparison.ordinalignorecase) _' 'select mrows, srows' 'conversion type "dbnull" type "string" not valid.' 'dim matches = _' 'linqmastertable.asenumerable().join( _' 'linqsecondtable.asenumerable(), _' 'function(mrows) mrows("theprimarykey"), _' 'function(srows) srows("theforignkey"), _' 'function(mrows datarow, srows datarow) new {mrows, srows}, _' 'stringcomparer.invariantcultureignorecase)' 'doesnt work @ - multiple errors' 'dim matches2 = _' 'linqmastertable _' ' .join( _' ' linqsecondtable, _' ' function(x) x.key.tolower(), _' ' function(x) x.key.tolower(), _' ' function(o, i) new {.id = o.key, .description = o.value, .price = i.value} _' ' ).dump()' ' make sure dataset available and/or cleared:' if dsresults.tables(qresultsname) nothing dsresults.tables.add(qresultsname) dsresults.tables(qresultsname).clear() : dsresults.tables(qresultsname).columns.clear() 'adds master table column names' x = 0 mastertablecolumns.count - 1 dsresults.tables(qresultsname).columns.add(mastertablecolumns(x)) next 'rename second table names if needed:' x = 0 secondtablecolumns.count - 1 dsresults.tables(qresultsname) y = 0 .columns.count - 1 if secondtablecolumns(x) = .columns(y).columnname secondtablecolumns(x) = secondtablecolumns(x) & "_2" end if next end next 'make sure forign key unique value' if forignkey1 = primarykey thenewforignkey = forignkey1 & "_2" else thenewforignkey = forignkey1 end if 'adds second table column names' x = 0 secondtablecolumns.count - 1 'need error handling if columnname exists' dsresults.tables(qresultsname).columns.add(secondtablecolumns(x)) next pleasewait(true) 'locks controls while processing data' 'copy results dataset:' each match in matches 'build array each row:' dim newrow(columncount - 1) object 'add mrow items:' x = 0 mastertablecolumns.count - 1 newrow(x) = match.mrows.item(x) next 'add srow items:' x = 0 secondtablecolumns.count - 1 dim y integer = x + (mastertablecolumns.count) newrow(y) = match.srows.item(x) next 'add array dsresults row:' dsresults.tables(qresultsname).rows.add(newrow) next if chkunique.checked = true removeduplicates(dsresults.tables(qresultsname), theprimarykey) end if pleasewait(false) 'unlocks controls after processing data' if not chkretainkeys.checked = true 'removes forign key' dsresults.tables(qresultsname).columns.remove(thenewforignkey) end if 'clear arrays' mastertablecolumns.clear() secondtablecolumns.clear() end sub incidentally, additional information:
dim mastertablecolumns new arraylist() 'holds names of master table columns' dim secondtablecolumns new arraylist() 'holds names of second table columns' dim mastertable string 'current user selected master table' dim primarykey string 'table 0 user selected key' dim forignkey1 string 'table 1 user selected key' private function getcolumns(byval adataset dataset, byval atable string) arraylist if adataset nothing return nothing if not adataset.tables(atable) nothing dim temparray new arraylist() x = 0 adataset.tables(atable).columns.count - 1 adataset.tables(atable).columns(x) temparray.add(.columnname) end next return temparray else msgbox("there no column names in table """ & atable & """ load.") return nothing end if end function
you need specify iequalitycomparer<t> in join, can't query syntax. have use extension method syntax:
dim matches = _ linqmastertable.asenumerable().join( _ linqsecondtable.asenumerable(), _ function(mrows) mrows("theprimarykey"), _ function(srows) srows("theforeignkey"), _ function(mrows datarow, srows datarow) new { mrows, srows }, _ stringcomparer.invariantcultureignorecase)
Comments
Post a Comment