I have two DataTables with the same structure as each other.  There are two columns in these tables ID and LIST.  One table is populated from an internal database and other table is populated by a webservice call to a vendor's database.    Both Tables have close to 2 million records.  I am trying to compare the two tables to figure out where that are matches so I can figure out which records that I need to synchronize.  Table 1            Table 2          ACTION --------           --------         ------ ID  LIST           ID  LIST --  ----           --  ---- 1   ABC            1   ABC          No Change 1   BCD                             Add To Table 2 (1,BCD) 2   ABC            2   ABC          No Change 2                  2   BCD          Delete From Table 2 (2,BCD) Leave (2,ABC)                     3   BCD          Delete From Table 2 (3,BCD)   With the data provided.  I have come up with the following LINQ queries  Dim t1_DistinctIDs As System.Collections.Generic.IList(Of String) = (From r In ds.Tables("t1").AsEnumerable                                                                      Select r.Field(Of String)("ID")).Distinct.ToList  Dim t2_DistinctIDs As System.Collections.Generic.IList(Of String) = (From r In ds.Tables("t2").AsEnumerable                                                                      Select r.Field(Of String)("ID")).Distinct.ToList  'Records to Delete from Table 2 Because They Don't Exist In Table 1 Dim IDsToDelete As Array = (From r In ds.Tables("t2").AsEnumerable()                             Where Not t1_DistinctIDs.Contains(r.Field(Of String)("ENTITY_ID"))).ToArray  'Get All ID &amp; LIST in Table 1 Dim t1_AllItems As Array = (From r In ds.Tables("t1").AsEnumerable                             Select New With                               {                                 .ID = r.Field(Of String)("ID"),                                 .LIST = r.Field(Of String)("LIST")                               }                            ).ToArray  'Find All Records In Table 1 That Do Not Exist In Table 2 Dim ListToAdd As Array = (From r In ds.Tables("t2").AsEnumerable()                           Join l In t1_AllItems                             On r.Field(Of String)("ID") Equals l.ID _                            And r.Field(Of String)("LIST") Equals l.LIST _                           Select New With                             {                               .ID = r.Field(Of String)("ID"),                               .NAME = r.Field(Of String)("LIST")                             }                          ).ToArray   This is what I would do if I were using T-SQL.  DECLARE @T1 AS TABLE (     ID INT,     LIST VARCHAR(10) )  DECLARE @T2 AS TABLE (     ID INT,     LIST VARCHAR(10) )   INSERT INTO @T1(ID,LIST) VALUES (1,'ABC') INSERT INTO @T1(ID,LIST) VALUES (1,'BCD') INSERT INTO @T1(ID,LIST) VALUES (2,'ABC') INSERT INTO @T2(ID,LIST) VALUES (1,'ABC') INSERT INTO @T2(ID,LIST) VALUES (2,'ABC') INSERT INTO @T2(ID,LIST) VALUES (2,'BCD') INSERT INTO @T2(ID,LIST) VALUES (3,'BCD')  SELECT      t1.ID,     t1.LIST FROM @T1 t1 LEFT JOIN @T2 t2   ON t1.ID = t2.ID  AND t1.LIST = t2.LIST WHERE t2.ID IS NULL   I am trying to figure out how to do a Left Join where t2.ID IS NULL in LINQ.
122774
I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL.  I understand how to use one left outer join.  I'm using VB.NET.  Below is my SQL syntax.  T-SQL  SELECT     o.OrderNumber,     v.VendorName,     s.StatusName FROM     Orders o LEFT OUTER JOIN Vendors v ON     v.Id = o.VendorId LEFT OUTER JOIN Status s ON     s.Id = o.StatusId WHERE     o.OrderNumber &gt;= 100000 AND     o.OrderNumber &lt;= 200000
54604
Is there a way to search across all rows/columns in a lightning:datatable to narrow down the list, just like jQuery DataTables? If so, how do implement that?  Can I somehow do it all on the client-side to make it super fast (some sort of DOM manipulation)  I'm having some issues with the lightning:datatable not updating. I have attached my code below, maybe you can help me spot the issue:  Markup:  &lt;aura:component extends="c:Base" controller="AccountBrowserForm"&gt; &lt;aura:handler name="init" value="{! this }" action="{! c.doInit }"/&gt; &lt;aura:attribute name="selectedAccountId" type="Id" access="public" /&gt; &lt;aura:attribute name="accounts" type="Object"/&gt; &lt;aura:attribute name="mycolumns" type="List"/&gt; &lt;aura:attribute name="sortedBy" type="String" /&gt; &lt;aura:attribute name="sortedDirection" type="Boolean" default="true" /&gt;  &lt;aura:registerEvent name="AccountFilterChange" type="c:AccountFilterChange" /&gt;  &lt;aura:attribute name="backingdata" type="List" /&gt; &lt;aura:attribute name="filter" type="String" /&gt; &lt;lightning:input type="text" onchange="{!c.filter}" value="{!v.filter}" label="Filter" /&gt;  &lt;lightning:datatable aura:id="lightningTable"                      data="{! v.accounts }"                      columns="{! v.mycolumns }"                      keyField="Id"                      onrowaction="{! c.handleRowAction }"                      onsort="{!c.updateColumnSorting}"                      sortedBy="{!v.sortedBy}"                      sortedDirection="{!v.sortedDirection}"                      hideCheckboxColumn="true" /&gt;     Controller:  doInit: function (component, event, helper) {     helper.callServer(component, "c.getAccounts", $A.getCallback(function (response) {         component.set("v.accounts", response);         component.set("v.backingdata", response);         console.log(component.get("v.backingdata") + " /// BACKING DATA INSIDE ")         console.log(component.get("v.accounts") + " // ACCOUTN DATA INSIDE")          component.set('v.mycolumns', [{                 type: "button",                 typeAttributes: {                     label: '',                     iconName: 'utility:add',                     name: 'selectRecord',                     title: 'selectRecord',                     disabled: false,                     value: 'edit',                     variant: {                         fieldName: 'variantValue'                     },                 }             },             {label: 'Name', fieldName: 'Name', type: 'text', sortable: true},             {label: 'Street', fieldName: 'BillingStreet', type: 'text', sortable: true},             {label: 'Postal Code', fieldName: 'BillingPostalCode', type: 'text', sortable: true},             {label: 'City', fieldName: 'BillingCity', type: 'text', sortable: true},             {label: 'Country', fieldName: 'BillingCountry', type: 'text', sortable: true},         ]);          console.log(component.get("v.backingdata") + " *** BACKING DATA OUTSIDE *** ")         console.log(component.get("v.accounts") + " *** ACCOUNT DATA OUTSIDE *** ")     })) },  filter: function (component, event, helper) {     var data = component.get("v.backingdata"),         term = component.get("v.filter"),         results = data,         regex;     try {         regex = new RegExp(term, "i");         // filter checks each row, constructs new array where function returns true         results = data.filter(row =&gt; regex.test(row.name) || regex.test(row.age.toString()));     } catch (e) {         // invalid regex, use full list     }     component.set("v.accounts", results); },   For some reason the filter function is completely unresponsive. Also, does it matter that I'm populating the component with a response from a callback function. @sfdcfox, I noticed in your example that you simple created an array with dummy data. My "myaccounts" attribute is of type object
16869
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)  The following query works perfectly  SELECT *   FROM [CLIENT]   left outer join   (     select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = ENTRY_CODE   where Client_recd = 'A'   --and client.[FileRecordID] = 10607   I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me      Msg 245, Level 16, State 1, Line 1   Conversion failed when converting the varchar value 'BK/TN' to data type int.   The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.  What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.  I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.   How do I fix this? Can anyone explain what is happening so I can understand how to avoid this in the future?   Non filtered query planed execution   Filtered (non working) query planned execution  Notice the reversed order of the Compute Scalar and the Filter    Other things I have tried that do not work:   Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join. Added OPTION (FORCE ORDER) query hint to the query.     Update:  but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.  SELECT *   FROM [CLIENT]   left outer join   (     select ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')   where Client_recd = 'A'   and client.[FileRecordID] = 10607
122930
I'm comparing two list given in the list to see if the item.name exists in both for loop. If not then add into a list to show the error for the missing names.  set&lt;id&gt; setAssetIds = new set&lt;id&gt;(); list&lt;Asset_Store__c&gt;  assets = [select id,name......]; for(Asset_Store__c item : assets ) {     setAssetIds.add(item.id);  }   list&lt;Meter__c&gt;  meters = [select id,name......where id in : setAssetIds];  //now comparing the two lists for(Asset_Store__c a : assets) {    for(Meter__c m : meters)    {      if(a.Name != m.Name)      {        //not matched...        break;      }     } }   the problem with the above approach is that, its not finding the correct name.
16134
I have two complex (i.e. objects with string, int, double, List and other home made data type) objects of the same type. I would like to compare the content of both of them to ensure that they are identical. Note: The object doesn't implement .Equals (I have no control on that) and doesn't implement IComparable.   Is there a generic way (reflection?) to compare the content of the two objects?  Thanks!
42549
All,  So I've got all my select queries in LINQ-to-SQL converted to using CompiledQueries to speed things up.  Works great so far for select statements, but I haven't been able to figure out how to pre-compile insert, update, or delete statements.  Granted, when you're inserting, deleting or updating in LINQ-to-SQL, you have to use the object model.  But obviously somewhere along the way it generates a query, which would be nice to pre-compile and store in a static member.  Is this possible?  What is LINQ performance like for updates, deletes and inserts when its not pre-compiled?  I could see it being a lot faster than the selects, because what they do underneath is a lot simpler and less "dynamic"...
91708
I have a sharepoint list with managed metadata column and I have to find items matching values from columns. If I filter the list for a column, let's say COLUMN1  &lt;In&gt;         &lt;FieldRef LookupId='TRUE' ID='919cad04-5872-42a5-8c79-93e38e5e5b96' /&gt;         &lt;Values&gt;           &lt;Value Type='Integer'&gt;65&lt;/Value&gt;         &lt;/Values&gt;       &lt;/In&gt;   It returns the record RECORD1.  Now i filter the list for a second column, let's say COLUMNB,   &lt;In&gt;         &lt;FieldRef LookupId='TRUE' ID='5a6a5ee4-d125-4c99-9109-f943d8da7af0' /&gt;         &lt;Values&gt;           &lt;Value Type='Integer'&gt;68&lt;/Value&gt;         &lt;/Values&gt;       &lt;/In&gt;   It returns record RECORD2. The issue is that if I run the query  &lt;Or&gt;         &lt;In&gt;           &lt;FieldRef LookupId='TRUE' ID='5a6a5ee4-d125-4c99-9109-f943d8da7af0' /&gt;           &lt;Values&gt;             &lt;Value Type='Integer'&gt;68&lt;/Value&gt;           &lt;/Values&gt;         &lt;/In&gt;         &lt;In&gt;           &lt;FieldRef LookupId='TRUE' ID='919cad04-5872-42a5-8c79-93e38e5e5b96' /&gt;           &lt;Values&gt;             &lt;Value Type='Integer'&gt;65&lt;/Value&gt;           &lt;/Values&gt;         &lt;/In&gt;       &lt;/Or&gt;   No record is returned. I was expecting both record RECORD1 and RECORD2. BTW SharePoint version is 2010.  Any help is appreciated
22593
I would like to compare two collections (in C#), but I'm not sure of the best way to implement this efficiently.  I've read the other thread about , but it's not exactly what I'm looking for.  In my case, two collections would be equal if they both contain the same items (no matter the order).  Example:  collection1 = {1, 2, 3, 4}; collection2 = {2, 4, 1, 3};  collection1 == collection2; // true   What I usually do is to loop through each item of one collection and see if it exists in the other collection, then loop through each item of the other collection and see if it exists in the first collection. (I start by comparing the lengths).  if (collection1.Count != collection2.Count)     return false; // the collections are not equal  foreach (Item item in collection1) {     if (!collection2.Contains(item))         return false; // the collections are not equal }  foreach (Item item in collection2) {     if (!collection1.Contains(item))         return false; // the collections are not equal }  return true; // the collections are equal   However, this is not entirely correct, and it's probably not the most efficient way to do compare two collections for equality.  An example I can think of that would be wrong is:  collection1 = {1, 2, 3, 3, 4} collection2 = {1, 2, 2, 3, 4}   Which would be equal with my implementation. Should I just count the number of times each item is found and make sure the counts are equal in both collections?    The examples are in some sort of C# (let's call it pseudo-C#), but give your answer in whatever language you wish, it does not matter.  Note: I used integers in the examples for simplicity, but I want to be able to use reference-type objects too (they do not behave correctly as keys because only the reference of the object is compared, not the content).
44610
I need to create a background job that processes a table looking for rows matching on a particular id with different statuses. It will store the row data in a string to compare the data against a row with a matching id. I know the syntax to get the row data, but I have never tried comparing 2 rows from the same table before. How is it done? Would I need to use variables to store the data from each? Or some other way? (Using SQL Server 2008)
75988
This question comes up occasionally, but I haven't seen a satisfactory answer.  A typical pattern is (row is a DataRow):   if (row["value"] != DBNull.Value)  {       someObject.Member = row["value"];  }   My first question is which is more efficient (I've flipped the condition):    row["value"] == DBNull.Value; // Or   row["value"] is DBNull; // Or   row["value"].GetType() == typeof(DBNull) // Or... any suggestions?    indicates that .GetType() should be faster, but maybe the compiler knows a few tricks I don't?  Second question, is it worth caching the value of row["value"] or does the compiler optimize the indexer away anyway?  For example:    object valueHolder;   if (DBNull.Value == (valueHolder = row["value"])) {}   Notes:   row["value"] exists. I don't know the column index of the column (hence the column name lookup). I'm asking specifically about checking for DBNull and then assignment (not about premature optimization, etc.).   I benchmarked a few scenarios (time in seconds, 10,000,000 trials):  row["value"] == DBNull.Value: 00:00:01.5478995 row["value"] is DBNull: 00:00:01.6306578 row["value"].GetType() == typeof(DBNull): 00:00:02.0138757   Object.ReferenceEquals has the same performance as "=="  The most interesting result? If you mismatch the name of the column by case (for example, "Value" instead of "value", it takes roughly ten times longer (for a string):  row["Value"] == DBNull.Value: 00:00:12.2792374   The moral of the story seems to be that if you can't look up a column by its index, then ensure that the column name you feed to the indexer matches the DataColumn's name exactly.  Caching the value also appears to be nearly twice as fast:  No Caching: 00:00:03.0996622 With Caching: 00:00:01.5659920   So the most efficient method seems to be:   object temp;  string variable;  if (DBNull.Value != (temp = row["value"]))  {       variable = temp.ToString();  }
5841
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.  When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?  Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?  First way:  IF EXISTS (SELECT 1             FROM INFORMATION_SCHEMA.TABLES             WHERE TABLE_TYPE='BASE TABLE'             AND TABLE_NAME='mytablename')     SELECT 1 AS res ELSE SELECT 0 AS res;   Second way:  IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL     SELECT 1 AS res ELSE SELECT 0 AS res;   MySQL provides the simple   SHOW TABLES LIKE '%tablename%';    statement. I am looking for something similar.
35596
Currently, I am trying to copy data from TABLE1 to TABLE2. In terms of insertions performance, would it be the same or faster if I would to do  BULK INSERT manually (i.e BULK insert every 10K records into TABLE2 via INSERT INTO TABLE2 VALUES (1,2), (5,5), ...), versus INSERT INTO TABLE2 SELECT * FROM TABLE1
2165
What is a  free tool to compare two Sql Server tables (data and schema).   It would be great if the tool can script the differences found.  I also went through some older . The closest I have seen is  but I would love to try more options.
15299
Is there any efficiency difference in an explicit vs implicit inner join? For example:  SELECT * FROM table a INNER JOIN table b ON a.id = b.id;   vs.  SELECT a.*, b.* FROM table a, table b WHERE a.id = b.id;
88076
This is what I've come up with as a method on a class inherited by many of my other classes. The idea is that it allows the simple comparison between properties of Objects of the same Type.  Now, this does work - but in the interest of improving the quality of my code I thought I'd throw it out for scrutiny. How can it be better/more efficient/etc.?  /// &lt;summary&gt; /// Compare property values (as strings) /// &lt;/summary&gt; /// &lt;param name="obj"&gt;&lt;/param&gt; /// &lt;returns&gt;&lt;/returns&gt; public bool PropertiesEqual(object comparisonObject) {      Type sourceType = this.GetType();     Type destinationType = comparisonObject.GetType();      if (sourceType == destinationType)     {         PropertyInfo[] sourceProperties = sourceType.GetProperties();         foreach (PropertyInfo pi in sourceProperties)         {             if ((sourceType.GetProperty(pi.Name).GetValue(this, null) == null &amp;&amp; destinationType.GetProperty(pi.Name).GetValue(comparisonObject, null) == null))             {                 // if both are null, don't try to compare  (throws exception)             }             else if (!(sourceType.GetProperty(pi.Name).GetValue(this, null).ToString() == destinationType.GetProperty(pi.Name).GetValue(comparisonObject, null).ToString()))             {                 // only need one property to be different to fail Equals.                 return false;             }         }     }     else     {         throw new ArgumentException("Comparison object must be of the same type.","comparisonObject");     }      return true; }
6834
Given a number, how do I discover in what table and column it could be found within? I don't care if it's fast, it just needs to work.
117105
A query that is used to loop through 17 millions records to remove duplicates  has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back?  I have found that when I do a    select count(*) from myTable   That the rows that it returns (while doing this query) is about 5 less than what the starting row count was. Obviously the server resources are extremely poor, so does that mean that this process has taken 16 hours to find 5 duplicates (when there are actually thousands), and this could be running for days?  This query took 6 seconds on 2000 rows of test data, and it works great on that set of data, so I figured it would take 15 hours for the complete set.  Any ideas?  Below is the query:  --Declare the looping variable DECLARE @LoopVar char(10)       DECLARE      --Set private variables that will be used throughout       @long DECIMAL,       @lat DECIMAL,       @phoneNumber char(10),       @businessname varchar(64),       @winner char(10)      SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable)      WHILE @LoopVar is not null     BEGIN        --initialize the private variables (essentially this is a .ctor)       SELECT          @long = null,         @lat = null,         @businessname = null,         @phoneNumber = null,         @winner = null        -- load data from the row declared when setting @LoopVar         SELECT         @long = longitude,         @lat = latitude,         @businessname = BusinessName,         @phoneNumber = Phone       FROM MyTable       WHERE RecordID = @LoopVar        --find the winning row with that data. The winning row means        SELECT top 1 @Winner = RecordID       FROM MyTable       WHERE @long = longitude         AND @lat = latitude         AND @businessname = BusinessName         AND @phoneNumber = Phone       ORDER BY         CASE WHEN webAddress is not null THEN 1 ELSE 2 END,         CASE WHEN caption1 is not null THEN 1 ELSE 2 END,         CASE WHEN caption2 is not null THEN 1 ELSE 2 END,         RecordID        --delete any losers.       DELETE FROM MyTable       WHERE @long = longitude         AND @lat = latitude         AND @businessname = BusinessName         AND @phoneNumber = Phone         AND @winner != RecordID        -- prep the next loop value to go ahead and perform the next duplicate query.       SET @LoopVar = (SELECT MIN(RecordID)      FROM MyTable     WHERE @LoopVar &lt; RecordID)     END