我如何将这个linq命令写入一个等效的删除SQL查询

Question

我已经很多年没有写过SQL了，现在我正在做一个更"渴望性能"的网站。有人能帮我把这个转换为删除SQL命令吗?

我有一堆这样的东西，所以帮我解决这个问题会帮我解决很多问题，谢谢!

I got this far:

Delete FROM [DbName].[dbo].[RatingListTriangleModels] 
WHERE  ...

Linq select命令:

var allRatingListObjects = from row in ctx.RatingListTriangle
                           where
                              (row.To1Id == myToid && row.To2Id == theirTradeObjectid)
                               || (row.To2Id == myToid && row.To3Id == theirTradeObjectid)
                               || (row.To3Id == myToid && row.To1Id == theirTradeObjectid)
                           select row;

更新:这是我的工作解决方案:

using (SqlCommand command = new SqlCommand
                ("delete TBL from RatingListTriangleModels TBL where ( TBL.To1Id = @MY_ID and TBL.To2Id = @OTHER_ID ) or ( TBL.To2Id = @My_ID and TBL.To3Id = @OTHER_ID ) or ( TBL.To3Id = @My_ID and TBL.To1Id = @OTHER_ID )", cn))
            {
                // Add new SqlParameter to the command.
                command.Parameters.Add(new SqlParameter("MY_ID", myToid));
                command.Parameters.Add(new SqlParameter("OTHER_ID", theirTradeObjectid));
                var no = command.ExecuteNonQuery();
            }

Answer 1

这将删除您在linq语句中指定的记录，但是您需要用您拥有的相应id替换@YOUR_ID和@OTHER_ID。

您可以对SqlParameter执行此操作。示例

delete from [DbName].[dbo].[RatingListTriangleModels] TBL
where ( TBL.To1Id = @YOUR_ID and TBL.To2Id = @OTHER_ID )
   or ( TBL.To2Id = @YOUR_ID and TBL.To3Id = @OTHER_ID )
   or ( TBL.To3Id = @YOUR_ID and TBL.To1Id = @OTHER_ID )  

Answer 2

如果你想删除linq用TSQL选择的行，你可以这样做

DECLARE @p0 int;
DECLARE @p1 int;
SET @p0 = <some integer>;
SET @p1 = <some other integer>;

DELETE
            [DbName].[dbo].[RatingListTriangleModels]
    WHERE
            ([To1Id] = @p0 AND [To2Id] = @p1)
        OR  
            ([To2Id] = @p0 AND [To3Id] = @p1)
        OR
            ([To3Id] = @p0 AND [To1Id] = @p1);

当然，您可能希望将语句传递给上下文中的ExecuteCommand并将id作为参数传递，然后。net将为您将其封装在对sp_executeSql的调用中。这将防止注入攻击，为您提供查询计划重用的好处和直接TSQL操作的灵活性。

我要提醒一下我的答案，像这样一个简单的操作可能会在linq中执行，而不需要任何额外的复杂性。