从以下源教程中:


https://www.youtube.com/watch?v=Jt9vSY802mM
http://www.dotnetawesome.com/2017/07/curd-operation-on-fullcalendar-in-aspnet-mvc.html


仅使用SQL查询,如何在没有Entity Framework的情况下执行上述代码示例?

例如在上面的源代码中,

var v = dc.Events.Where(a => a.EventID == eventID).FirstOrDefault();

if (v != null)
{
    dc.Events.Remove(v);
    dc.SaveChanges();
    status = true;
}


我想要做

DELETE FROM Even WHERE EventID = {0}

最佳答案

LINQ中的FirstOrDefault()等同于MySQL中的LIMIT 1,因此可以使用IFCASE WHEN将LINQ函数转换为SQL命令,如下所示(假定命令在存储过程中运行):

DELIMITER //

-- 'Events' is a DbSet name by common convention,
-- therefore table name should be 'Event'
CREATE PROCEDURE procedure_name (IN eventID INT)
BEGIN
    DECLARE v INT;
    SET v = SELECT EventID FROM Event WHERE EventID = eventID LIMIT 1;

    CASE WHEN v IS NOT NULL
    THEN DELETE FROM Event WHERE EventID = v
    ELSE -- do something else
    END

    -- alternative:
    -- IF(v IS NOT NULL, DELETE FROM Event WHERE eventID = v, 0)

    -- other stuff here

END//
DELIMITER ;


注意:如果EventID是主键列,则可以删除LIMIT 1,因为查询结果仅返回单个值。

然后,使用CALL procedure_name(eventID)或在procedure_name中包含MySqlCommand来执行它。

08-25 12:56
查看更多