找了一下似乎是不行,都會一筆一筆的刪導致速度很慢
果然寫不同的需求,就會遇到不同的問題
最近在寫一個抽獎程式
第一步一定會存入大量的抽獎名單資料,那之後如果要重匯入或刪除的話,一樣使用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寫這一段刪除指令了…