Entity Framework Core使用LINQ快速刪除大量資料

技術文章

找了一下似乎是不行,都會一筆一筆的刪導致速度很慢

果然寫不同的需求,就會遇到不同的問題

最近在寫一個抽獎程式

第一步一定會存入大量的抽獎名單資料,那之後如果要重匯入或刪除的話,一樣使用LINQ就發現出問題啦

什麼問題?超級的久~~~資料大概都五十萬筆起跳

曾經以為LINQ可以取代大部分"常用"的SQL指令,原來不是這樣的

假設我想指定LotteryPeopleId欄位等於2的資料都刪除,LINQ會這樣寫

var delete = from a in _lotteryContext.LotteryPeopleDataField
			 where a.LotteryPeopleId == 2
             select a;

_lotteryContext.LotteryPeopleDataField.RemoveRange(delete);
_lotteryContext.SaveChanges();

我幻想中轉成SQL是這樣

DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleId] = 2

但這樣其實轉成SQL會變這樣

DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleDataFieldId] = 1
DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleDataFieldId] = 2
DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleDataFieldId] = 3
DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleDataFieldId] = 4
DELETE FROM [LotteryPeopleDataField] WHERE [LotteryPeopleDataFieldId] = 5

會直接變成一筆一筆找主鍵的下指令刪除,所以我50萬筆跑超級久==

後來Google了一下,似乎沒有發現有其他能轉成我幻想中的SQL樣子的LINQ寫法(如果有跟我講一下XD)

那最後的寫法只能搬出SQL來用,好家在Entity Framework Core6還是能下SQL

_lotteryContext2.Database.LotteryPeopleDataField("delete from LotteryPeopleDataFieldName where LotteryPeopleId=2");

這樣就可以很快的刪除資料了

我覺得這算是很基本的用法,LINQ應該會幫我們轉成一個還不錯的SQL

但沒想到轉的SQL卻走了歪路?

所以沒辦法只好改用SQL寫這一段刪除指令了…



Copyright © 凱哥寫程式 2022 | Powered by TalllKai ❤