LINQ - delete rows from database table from an Array

Question

I have a comma seperated values (1, 2, 5, 8) and I am converting them into an array like this

string s =checkboxId.Value;
int[] nums = Array.ConvertAll(s.Split(','), int.Parse);

I want to delete all this rows where id is in nums. How can I do this with LINQ?

Tried this:

  foreach(int id in nums)
    {
   DeleteById(id, uid);
   }
   public void DeleteById(int id, string userName)
    {            
        long uid = common.GetUserId(userName);

        IEnumerable<M> idList = dataContext.MD.Where(m => m.ID == id && m.UserId == uid);
        dataContext.MD.DeleteAllOnSubmit(idList);
        dataContext.dc.SubmitChanges();
    }

EDIT: I do not want to loop through every array item. Is there a way where I can easily pass the array and delete all the records from the database through LINQ query?

Possible duplicate of [Split string, convert ToList() in one line](https://stackoverflow.com/questions/911717/split-string-convert-tolistint-in-one-line) — xdtTransform, Jul 27 '18 at 13:39

Che · Answer 1 · 2018-07-27T14:00:04.713

1

Parse it to IEnumerable. Create new List with excluded values and then remove it with predicate in method RemoveAll.

An example:

        string s = checkboxId.Value;
        var nums = s.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries)
            .Select(int.Parse)
            .ToList();
        var toBeRemoved = new List<int>{1, 2, 5};
        var numberOfRemovedItems = nums.RemoveAll(toBeRemoved.Contains);

edited Jul 27 '18 at 14:00

answered Jul 27 '18 at 13:31

Che

169
8

Good point, I never realized you can just pass a function instead of a full lambda – BurnsBA Jul 27 '18 at 13:44
@BurnsBA after your comment, realized we could do the same with 'RemoveAll' :D – Che Jul 27 '18 at 13:48

score 1 · Answer 2 · answered Jul 27 '18 at 19:37

1

I believe this should work:

string s = checkboxId.Value;
int[] nums = Array.ConvertAll(s.Split(','), int.Parse);

 var uid = 1234;

 // I prefer LINQ syntax.  The lambda syntax is fine too.
 var idList = from m in dataContext.MD
              where m.UserId == uid && nums.Contains(m.id)
              select m;

dataContext.MD.DeleteAllOnSubmit(idList);
dataContext.SubmitChanges();

answered Jul 27 '18 at 19:37

James Curran

101,701
37
181
258

it says nums does not have Contains – user1254053 Jul 28 '18 at 03:18
Be sure you have "using System.Linq;" – James Curran Jul 28 '18 at 10:39

score 0 · Answer 3 · answered Jul 27 '18 at 13:28

0

You can do this with linq:

var arr = s.Split(',').Where(n => n != checkboxId.Value)
                      .Select(n => Convert.ToInt32(n))
                      .ToArray();

answered Jul 27 '18 at 13:28

richej

834
4
21

Why do you need `Where` clause? Condition `n != to checkboxId.Value` will always be true. – Renatas M. Jul 27 '18 at 13:34
Actually, n would equal checkboxId.Value when there is no comma in checkboxId.Value (i.e., when just one value is given). But you probably don't want to filter those out anyway. – James Curran Jul 28 '18 at 10:45

Antoine V · Answer 4 · 2018-07-29T06:33:17.780

0

You have a string 1,2,5,8

To convert this string to an array of int. You can do this. Attention if your string contains only int.

string s = "1,2,5,8";
var arrayInt = s.Split(',').Select(int.Parse);

Next, you want to delete all these numbers in your array of int ids.

var arrayIntCleared = ids.ToList().Except(arrayInt);

edited Jul 29 '18 at 06:33

answered Jul 27 '18 at 13:38

Antoine V

6,998
2
11
34

No need to convert the array the Split() return into a List. Also, Select takes function which takes a single object, and converts it to a different single object. You can create such a function with a Lambda, as you did. Or you can just use the one .NET gives you directly: `var arrayInt = s.Split(',').Select(int.Parse);` – James Curran Jul 28 '18 at 10:42

xdtTransform · Answer 5 · 2018-07-27T14:18:33.107

0

You can get the Uid when you select:

var toDelete = checkboxId.Value
    .Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries)
    .Select( x => {
        int parsedInt= 0;
        var isInt = int.TryParse(x , out parsedInt);
        return new {isInt, parsedInt}
    })
    .Where(x => x.isInt)
    .Select(x=> new {Id= x.parsedInt, Uid=common.GetUserId(x.parsedInt)});

foreach(var item in toDelete)
{
    dataContext.MD.DeleteAllOnSubmit(
        dataContext.MD
            .Where(m => m.ID == item.Id && m.UserId == item.Uid)
        )
}
dataContext.dc.SubmitChanges();

Or without int.TryParse check:

var toDelete = checkboxId.Value
        .Split(',')
        .Select(int.Parse)
        .Select(x=> new {Id= x.parsedInt, Uid=common.GetUserId(x.parsedInt)})

For me the best way would be to have proper link between object in your dbml. So you can travel from on object to an other and you won't need neither common.GetUserId() or m.ID == item.Id && m.UserId == item.Uid).First(). with that done you could simply:

var idToDelete = checkboxId.Value
        .Split(',')
        .Select(int.Parse);

var entitiesToDelete = dataContext.MD
                        .Where(x => toDelete.Contains(x.Id));


dataContext.MD.DeleteAllOnSubmit(entitiesToDelete);
dataContext.SubmitChanges();

edited Jul 27 '18 at 14:18

answered Jul 27 '18 at 14:00

xdtTransform

1,986
14
34

is there a way where I can pass the array list and delete all the records whose ids are in array? – user1254053 Jul 27 '18 at 14:06
@user1254053, you can pass a `enumerable – xdtTransform Jul 27 '18 at 14:09
dbml and linq queries – user1254053 Jul 27 '18 at 14:17
I have edit my question with optimal simple code but .. Your question evole a little bit to much and to fast. It has invalidate most of the answer because you was not clear enought and hid part of the issue when you posted. You should edit this question to the duplicate it was when originaly post and post and other on clear on the database delete. Stating that you already have the list of ID you wan't to delete. And be clear on the 1-1 or 1-n relation between Id and user ID – xdtTransform Jul 27 '18 at 14:23
Thanks a lot xdtTransform for your suggestion. – user1254053 Jul 27 '18 at 14:30

LINQ - delete rows from database table from an Array

5 Answers5