LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria

If your result set returns 0 records: SingleOrDefault returns the default value for the type (e.g. default for int is 0) FirstOrDefault returns the default value for the type If you result set returns 1 record: SingleOrDefault returns that record FirstOrDefault returns that record If your result set returns many records: SingleOrDefault throws an exception … Read more

Nested stored procedures containing TRY CATCH ROLLBACK pattern?

This is our template (error logging removed) This is designed to handle Paul Randal’s article “No such thing as a nested transaction in SQL Server” Error 266 Trigger Rollbacks Explanations: all TXN begin and commit/rollbacks must be paired so that @@TRANCOUNT is the same on entry and exit mismatches of @@TRANCOUNT cause error 266 because … Read more

Random row from Linq to Sql

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context: partial class MyDataContext { [Function(Name=”NEWID”, IsComposable=true)] public Guid Random() { // to prove not used by our C# code… throw new NotImplementedException(); } } Then just order by ctx.Random(); this will do … Read more

Return anonymous type results?

I tend to go for this pattern: public class DogWithBreed { public Dog Dog { get; set; } public string BreedName { get; set; } } public IQueryable<DogWithBreed> GetDogsWithBreedNames() { var db = new DogDataContext(ConnectString); var result = from d in db.Dogs join b in db.Breeds on d.BreedId equals b.BreedId select new DogWithBreed() { Dog … Read more

Difference Between Select and SelectMany

SelectMany flattens queries that return lists of lists. For example public class PhoneNumber { public string Number { get; set; } } public class Person { public IEnumerable<PhoneNumber> PhoneNumbers { get; set; } public string Name { get; set; } } IEnumerable<Person> people = new List<Person>(); // Select gets a list of lists of phone … Read more

Returning IEnumerable vs. IQueryable

Yes, both will give you deferred execution. The difference is that IQueryable<T> is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable<T>, that query will be executed in the database, if possible. For the IEnumerable<T> case, it will be LINQ-to-object, meaning that all objects matching … Read more

tech