代码之家 › 专栏 › 技术社区 › Zachary Scott

使用Linq在集合上执行CRUD:如何?

crud linq-to-entities sql-server-2005 c#

Zachary Scott · 技术社区 · 14 年前

使用Linq,尝试选择不在本地集中的所有记录失败:

var localset = new List<Category>();
localset.Add( new Category { pk1 = 1, pk2 = 1, name = "one" } );
localset.Add( new Category { pk1 = 1, pk2 = 2, name = "two" } );

var dbCategories = dc.Categories;

var diff = dbCategories.Except(localset);

我需要做基本的CRUD:从DB集合中删除,而不是在我的本地集合中,更新存在的地方,并在不存在时添加新的。

我通常会:

delete from Category C where not exists
    ( select null from LocalSet L where C.pk1 = L.pk1 and C.pk2 = L.pk2 )

update Category set name = L.name
from LocalSet L
where L.pk1 = Category.pk1 and L.pk2 = Category.pk2

insert into Category (pk1, pk2, name)
select pk1, pk2, name
from LocalSet L
where not exists (
    select null from Category C where L.pk1 = C.pk1 and L.pk2 = C.pk2 )

我觉得很简单。然而,.Contains似乎是唯一一个处理本地集的方法,它似乎只比较单个字段。数据库表具有组合键。

在不更改组合键的情况下,是否有任何方法可以完成这些任务?

3 回复 | 直到 14 年前

Sorax 14 年前

你必须实现一个 CategoryComparer 如下所示:

public class CategoryComparer : IEqualityComparer<Category>
{
    public bool Equals(Category a, Category b)
    {
        bool result = false;
        if( a.pk1 == b.pk1 && a.pk2 == b.pk2 && a.name == b.name)
            result = true;
        return result;    
    }

    public int GetHashCode(Category category)
    {        
        if (Object.ReferenceEquals(category, null)) return 0;        
        return category.pk1.GetHashCode() ^ category.pk2.GetHashCode();
    }
}

然后调用可枚举。除了:

var localset = new List<Category>();
localset.Add( new Category { pk1 = 1, pk2 = 1, name = "one" } );
localset.Add( new Category { pk1 = 1, pk2 = 2, name = "two" } );

var diff = dc.Categories
             .AsEnumerable()
             .Except(localset, new CategoryComparer());

Jon Skeet 14 年前

我不清楚你说的是哪种方法,恐怕。。。但是 一般的 使用组合键的解决方案是使用匿名类型生成单个值。

例如,如果你想参加在复合键上,可以使用

from x in xs
join y in ys on new { x.k1, x.k2 } equals new { y.k1, y.k2 }

有帮助吗?

Enigmativity 14 年前

var localset = new List<Category>();
localset.Add(new Category { pk1 = 1, pk2 = 1, name = "one" });
localset.Add(new Category { pk1 = 1, pk2 = 2, name = "two" });

var flattened = localset
    .Select(dd => dd.pk1 + "|" + dd.pk2 + "|" + dd.name)
    .ToArray();

var dbCategories = dc.Categories;

var diff = from c in dbCategories
           let x = c.pk1 + "|" + c.pk2 + "|" + c.name
           where !flattened.Contains(x)
           select c;

这会将组合键转换为单个数组,该数组允许您使用 Contains