mysql - 没有 Entity Framework 的 MVC

Question

来自以下源教程:

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

如何在没有 Entity Framework 的情况下仅使用 SQL 查询来完成上述代码示例？

例如上面的源码中，改为

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}

Answer 1

LINQ中的

FirstOrDefault()相当于MySQL中的LIMIT 1，因此可以使用IF或将LINQ函数转换为SQL命令CASE WHEN 像这样(假设命令在存储过程中运行):

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)或在MySqlCommand中包含procedure_name来执行它。