Select one row with LINQ TO SQL

Question

I am starting to work with LINQ To SQL, and I am trying to solve this primitive problem. I have a very simple table with two columns.

• Nick is the key, unique
• Password

I would like to delete a line with some nick value.

I am using this method:

public void DeleteSpiritUser(string nick) { var user = from u in _dc.Spirit_Users where u.Nick == nick select u; using (var scope = new TransactionScope()) { _dc.Spirit_Users.DeleteOnSubmit(user.First()); try { _dc.SubmitChanges(); } catch (Exception exception) { throw exception; } scope.Complete(); } } 

The problem is that I have to use user.First (), if I need one row, I would like select with LINQ to only know one row is IEnumerable, because Nick is unique.

Answer 1

Try this - just select only the first (if any) and delete only if you get the value:

 public void DeleteSpiritUser(string nick) { var user = (from u in _dc.Spirit_Users where u.Nick == nick select u).SingleOrDefault(); if(user != null) { using (var scope = new TransactionScope()) { _dc.Spirit_Users.DeleteOnSubmit(user); _dc.SubmitChanges(); scope.Complete(); } } } 

Answer 2

You can do:

 var user = _dc.Spirit_Users.Single(u => u.Nick == nick); 

Answer 3

Well First () is the one that will retrieve the first (and only) row (plus will add TOP 1 to the query). Linq2Sql does not know that you have only one row (or you even have one), and it is preparing to receive more than one, therefore IEnmuerable even if there is one row.