动态数据网站的LINQ帮助

I have Dynamic dataWebsite which uses a SQL SP to do update operations..I have a problem here, my delete functionality is also a update (setting IsDeleted=1) operations. I am currently using LINQ query and calling datacontext.SubmitChanges() for deleting. The problem is, the update LINQ query (that sets IsDeleted=1) when I call SubmitChanges() is also calling the update SP which is meant for only Update Operations. Is there any option to fire my LINQ query directly to DB instead of calling update SP?

我有动态数据网站,它使用SQL SP进行更新操作。我有一个问题,我的删除功能也是一个更新(设置IsDeleted = 1)操作。我目前正在使用LINQ查询并调用datacontext.SubmitChanges()进行删除。问题是,当我调用SubmitChanges()时,更新LINQ查询(设置IsDeleted = 1)也调用更新SP,它仅用于更新操作。是否有任何选项可以直接将我的LINQ查询激发到DB而不是调用更新SP?

Employee ild = (from emp in _dataContext.Employee
                                     where emp.IN_ID == int.Parse(ID)
                                     select emp).FirstOrDefault();
ild.IsDeleted=1;
_dataContext.Submitchanges();

The above code always call UpdateSP that is configured to Update operation.

以上代码始终调用配置为Update操作的UpdateSP。

2 个解决方案

#1

In this case, could you use a delete stored proc which will get called just like your update proc. The delete proc doesn't need to actually perform a DELETE FROM table query, but instead could do an Update on the underlying table setting the IsDeleted flag as appropriate:

在这种情况下,您是否可以使用删除存储过程,它将像您的更新过程一样被调用。删除proc不需要实际执行DELETE FROM表查询,而是可以在基础表上执行更新,并根据需要设置IsDeleted标志:

CREATE PROCEDURE
  @Id int
AS
  UPDATE dbo.Employee 
  SET IsDeleted = 1 
  WHERE Id = @Id

You would then map this function to the delete behavior in LINQ to SQL just as you did the Update method. With this option, your client code would do a simple Remove on the table rather than dealing with the IsDeleted flag:

然后,就像使用Update方法一样,将此函数映射到LINQ to SQL中的删除行为。使用此选项,您的客户端代码将在表上执行简单的Remove,而不是处理IsDeleted标志:

_dataContext.Employee.Remove(ild);
_dataContext.SubmitChanges();

In your model, I would argue that you shouldn't expose IsDeleted at all. That is a database implementation detail. When using soft deletes, you should abstract away your physical table and expose the functionality through views or table value functions.

在你的模型中,我认为你根本不应该公开IsDeleted。这是一个数据库实现细节。使用软删除时,应抽象出物理表,并通过视图或表值函数公开功能。

As an alternative to the soft delete option, you could consider including a Tombstone table mimicing your transactional table. On a delete operation, use a stored proc or trigger to move the record from the transactional table to the tombstone table. With that, you could eliminate the IsDeleted flag from the database and elminate the need to include the filtering on all access (including reporting).

作为软删除选项的替代方法,您可以考虑包含模仿事务表的Tombstone表。在删除操作上,使用存储过程或触发器将记录从事务表移动到逻辑删除表。有了它,您可以从数据库中消除IsDeleted标志,并消除在所有访问(包括报告)上包含过滤的需要。

#2

I'm not 100% sure that I follow the idea here.

我不是百分百肯定我在这里遵循这个想法。

generally to delete the record you would say:

通常删除你会说的记录:

_dataContext.Employee.remove(ild);
_dataContext.Submitchanges();

But it seems like you wanted to just update the record to read any Enployee that has a setting IsDeleted = 1 as a deleted record. By running the code you current have there are are generateing an UPDATE statement and so the UpdateSP will fire.

但似乎您只想更新记录以读取任何将IsDeleted = 1设置为已删除记录的Enployee。通过运行当前的代码,可以生成UPDATE语句,因此UpdateSP将触发。

Is there a reason you can't use the .remove() method and ophysically delete the entry?

有没有理由你不能使用.remove()方法并且ophysically删除条目?

#1

CREATE PROCEDURE
  @Id int
AS
  UPDATE dbo.Employee 
  SET IsDeleted = 1 
  WHERE Id = @Id