Fetch Single Item Using DataContext

Question

I'm doing the following:

public MyItem FetchSingleItem(int id)
{
  string query = "SELECT Something FROM Somewhere WHERE MyField = {0}";
  IEnumerable<MyItem> collection = this.ExecuteQuery<MyItem>(query, id);
  List<MyItem> list = collection.ToList<MyItem>();
  return list.Last<MyItem>();
}

It's not very elegant really and I was hoping there's something a little better to get a single item out using DataContext. I'm extending from DataContext in my repository. There's a valid reason why before you ask, but that's not the point in this question ;)

So, any better ways of doing this?

Cheers

Answer 1

If it is SQL Server, change your SQL to:

SELECT TOP 1 Something FROM Somewhere ...

Or alternatavely, change these lines

List<MyItem> list = collection.ToList<MyItem>();
return list.Last<MyItem>();

into this one:

return collection.First();

Answer 2

myDataContext.MyItem.Where(item => item.MyField == id)
    .Select(item => item.Something)
    .FirstOrDefault();

Answer 3

The record returned is undefined, since you have no ORDER BY. So it's hard to do an exact translation. In general, though, reverse the order and take the First():

var q = from s in this.Somewhere
        where s.MyField == id
        orderby s.Something desc
        select s.Something;
return q.First();

Relational tables are unordered. So if you don't specify the record you want precisely, you must consider the returned record as randomly selected.