可以编写使用LINQ SQL和数据表之间的连接？ [英] Possible to write a Join between Sql and DataTable using Linq?

问题描述

我有一个过程，它可以提取基于时间戳多个数据库（MySQL的）客户信息。我这个数据存储到一个数据表。数据表重新presents更新现有的客户信息，以及新的客户信息。

I have a process that extracts customer info from multiple databases (MySql) based on a timestamp. I store this data into a DataTable. The data table represents updates to existing customer info as well as new customer info.

我要删除目标数据库（SqlServer的）基于一个恒定的值在任何愚弄， CompanyID 和客户ID 。于是，我想到了一个加入的会给我的目标DB的易受骗的RecordIDs，通过名单，其中，INT＆GT; 的（或一些回收机制）的到删除方法。

I want to delete any dupes in the destination database (SqlServer) based on one constant value, CompanyID, and the CustomerID. So, I thought a join would give me the RecordIDs of the dupes in the destination DB, pass the List<int> (or some collection mechanism) to the DELETE method.

我有什么：

using (var context = new DataContext(SqlConnection))
{
    var tblSource = context.GetTable<tblCustomerInfo>();
    var dupeIDs = from currCust in tblSource
                  join newCust in myTable.AsEnumerable() on currCust.CompanyID equals newCust.Field<string>("CompanyID")
                  where currCust.CustomerID.Equals(newCust.Field<int>("CustomerID")
                  select currCust.RecordID;
}

这显然是行不通的。我将在一个位确切的错误消息的更新，但是这并没有编译。

This obviously does not work. I will update with the exact error messages in a bit, but this doesn't compile.

第一，是我连接语法正确，即使什么，我想达到什么目的？的

其次，我怎么能写这样的LINQ到DataTable和目标SQLSERVER数据库之间的连接？的

再思考的 - 它是可能的，一旦我有欺骗RecordIDs的集合，使用LINQ从目标数据库中删除记录

Afterthought - is it possible to, once I have a collection of dupe RecordIDs, use Linq to DELETE records from the destination database?

修改 为了澄清这个过程中，我有一个像这样的输入数据表和包含在数据集：

Edit To clarify the process, I have incoming data tables like so and contained in a DataSet:

Table1
CompanyID    CustomerID    Field1    Field2    ....
   1             5          ...       ...
   1             15         ...       ...

Table2
CompanyID    CustomerID    Field1    Field2    ....
   10           125         ...       ...
   10           145         ...       ...

这都将进入一个单一的数据库：

Which will all go into a single database:

Destination DB  
CompanyID    CustomerID    Field1    Field2    ....   
   1              5          ...       ...  
   1              15         ...       ...    
   1              27         ...       ... 
   5              15         ...       ... 
   10             125        ...       ...
   10             145        ...       ... 
   11             100        ...       ...

因此​​，在这种情况下，我会从目标表中删除匹配从表1和物品; 2.目标数据库将不断地增长，因此创建客户ID的列表似乎并不可行。不过，我希望新的和更新客户信息每天进口相对较小（在数百，也许不久的1000条记录）。

So, in this case I would delete from the destination table the items that match from tables 1 & 2. The destination database will be growing constantly so creating a List of CustomerID does not seem feasible. However, I expect daily imports of new and updated customer info to be relatively small (in the hundreds, maybe near 1000 records).

如果我不能写一个连接是什么其他的方法来完成这个过程是否合适？我试图想办法的，因为它看起来像我实际上并不能混用的LINQ到SQL和LINQ到对象。

If I cannot write a single join what other method for completing this process would be appropriate? I am trying to figure something out since it looks like I cannot actually mix Linq-to-Sql and Linq-to-Objects.

是否有可能以某种方式的地图的我的数据表，实体数据映射， tbl_CustomerInfo ，尽显一个原本一成不变的变种，然后执行连接？

Is it possible to somehow map my data table to the entity datamap, tbl_CustomerInfo, filling an otherwise immutable var, then perform the join?

下面是我已经完成了在这一点上，我得到我所期望的结果，从愚弄：

Here is what I have accomplished at this point and I get the results I expect from dupes:

using (DataContext context = new DataContext(SqlConnection)
{
    var custInfo = context.GetTable<tbl_CustomerInfo>();

    string compID = ImportCust.Rows[0]["CompanyID"].ToString();

    var imports = from cust in ImportCust.AsEnumerable()
                  select cust.Field<int>("CustomerID");

    var dupes = from cust in custInfo
                join import in imports
                on cust.CustomerID equals import
                where cust.CompanyID == compID
                select cust;

    custInfo.DeleteOnSubmit(/* what goes here */);
    context.SubmitChanges();
}

我现在的问题是，怎样进入 DeleteOnSubmit（...） 的？我觉得我已经变得如此接近时才被挫败这个。

My question now is, what goes into the DeleteOnSubmit(...)? I feel like I have gotten so close only to be foiled by this.

推荐答案

我通常解决了存储过程的效率，所有这一切。

I usually tackle all of this in a stored proc for efficiency.

添加标识字段，以你的目标表的唯一标识记录，那么使用这样的查询：

Add an identity field to your destination table to uniquely identify the records, then use a query like this:

DELETE d
FROM DestinationTable d JOIN (
   Select CompanyID, CustomerID, Min(UniqueID) AS FirstRecID
    FROM DestinationTable
    GROUP BY CompanyID, CustomerID) u on u.CompanyID=d.CompanyID AND u.CustomerID=d.CustomerID
WHERE d.UniqueID <> u.FirstRecID

这篇关于可以编写使用LINQ SQL和数据表之间的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！