Selecting Data with Entity Framework

Preface

Please see Entity Framework Select Functions Explained for an overview of each available select method. This post only covers a handful of them.

Selecting Single Record by Primary Key

Returns null if object with matching key not found.

Example with simple PK:

Widgets widget;
using (WidgetModel ctx = new WidgetModel()) {
    widget = ctx.Widgets.Find(SomeID);
}
return widget;

Example with composite PK (pass parameters in correct order):

UserWidgets uw;
using (WidgetModel ctx = new WidgetModel()) {
    uw = ctx.UserWidgets.Find(UserID, WidgetID);
}
return uw;

Selecting Multiple Records in a Table (with 'where' clause)

List<Widgets> shifts = new List<Widgets>();
using (WidgetModel ctx = new WidgetModel()) {
    widgets = ctx.Widgets
        .Where(x => x.Prop1 == "Value1" || x.Prop1 == "Value2")
        .ToList();
}
return widgets;

Selecting All Records in a Table (no 'where' clause)

List<Widgets> shifts = new List<Widgets>();
using (WidgetModel ctx = new WidgetModel()) {
    widgets = ctx.Widgets.ToList();
}
return widgets;

Selecting a Single Object

Example:

Widgets widget;
using (WidgetModel ctx = new WidgetModel()) {
    widget = ctx.Widgets
        .Where(x => x.Date == SomeDate)
        .Where(x => x.UserID == SomeUserID)
        .SingleOrDefault();
}

Note: If you are selecting a single object and there should never be multiple objects returned, use .SingleOrDefault(). If multiple objects are returned, the following exception will be thrown:

System.InvalidOperationException: Sequence contains more than one element

Likewise, if you are selecting a single object and there should never be a null object returned, use .Single(). If no object is returned, the following exception will be thrown:

System.InvalidOperationException: Sequence contains no elements

Selecting First of Multiple Objects

Example:

Widgets widget;
using (WidgetModel ctx = new WidgetModel()) {
    widget = ctx.Widgets
        .Where(x => x.Date == SomeDate)
        .OrderBy(x => x.Prop1)
        .FirstOrDefault();
}

Controlling Order

Order by two columns - ascending:

Widgets widget;
using (WidgetModel ctx = new WidgetModel()) {
    widget = ctx.Widgets
        .Where(x => x.Date == SomeDate)
        .OrderBy(x => x.Prop1)
        .ThenBy(x => x.Prop2)
        .ToList();
}

Order by two columns - descending:

Widgets widget;
using (WidgetModel ctx = new WidgetModel()) {
    widget = ctx.Widgets
        .Where(x => x.Date == SomeDate)
        .OrderByDescending(x => x.Prop1)
        .ThenByDescending(x => x.Prop2)
        .ToList();
}