C# 过滤器列表删除任何双对象

Question

在本论坛中搜索了很多ant testing示例，但无法获得完整的工作方法。

我正在使用 linq 批量插入实体类列表 (RemoteReadings)。

由于独特的限制，我需要过滤掉所有已插入的项目。

Uniqiuness 由 RemoteReadings 表中的 2 列 meterid 和 datetime 组成。

// approx 5000 records (I need to do this in batches of 2000 due to a 
// constraint in L2S,but can do this after get this working)
List<RemoteReading> lst = createListFromCSV(); 

// Option 1:
// This does not work as am comparing memory list to db list. I need to use contains() method.
// Actually am trying to accomplish this infollowing examples.

List<RemoteReading> myLst = (from ri in db.RemoteReadings                                                       
                            from l in lst 
                            where l.meterid = ri.meterid 
                            && l.date = r.date
                            select ri).ToList();
////

// Option2:
// Get the list from DB that are in memory lst 
List<RemoteReading> myLst = (from ri in db.RemoteReadings                                                       
                            where 
                            // where in this list by comparing meaterid and datemeaured
                            (from l in lst
                            select 
/// help here !
///
                            select ri).ToList<RemoteInterconnectorReading>();


// Option3:
// Get the list from lst that are not in database
// I am bit confused here !


// Tried also to remove from list any duplicates:

List<RemoteReading> result = List<RemoteReading>)myLst.Except(lst).ToList<RemoteReading>();


// Ultimately
db.RemoteReading.InsertAllOnSubmit(result);
db.submitChanges();

有什么帮助吗？

Answer 1

由于 EF 的限制，我们无法将数据库查询与内存列表连接起来。此外，Contains 只能与原始列表一起使用。所以我们需要做一些努力来找到两列上的重复项。

var newItems = createListFromCSV();
var meterIds = newItems.Select(n=> n.meterid).Distinct().ToList();
var dates = newItems.Select(n=> n.date).Distinct().ToList();

var probableMatches = (from ri in db.RemoteReadings                              
                      where (meterIds.Contains(ri.meterids)
                      || dates.Contains(ri.date)
                      select new {ri.merterid, ri.date}).ToList();

var duplicates = (from existingRi in probaleMatches
                 join newRi in newItems
                 on new {existingRi.meterid, existingRi.date}
                 equals {newRi.meterid, newRi.date}
                 select newRi).ToList();

var insertList = newItems.Except(duplicates).ToList();

db.RemoteReadings.Insert(insertList); // or whatever