[Entity Framework] DbContext API – Querying Entities

Retrieving data from database is one of the most important tasks in database programming. DbSet is designed to work with LINQ to Entities, therefore if you are familiar to LINQ you do not have any problems.

 

1. Query All Data from a Set

When querying data, you need to create a context object and access the DbSet property for the target entity.

using (var context = new ProductContext())
{
  foreach (Product p in context.Products)
  {
    Console.WriteLine("{0}: {1}", p.Id, p.Name);
  }
}

 

2. Filtering and Sorting

You can use the LINQ syntax to filter the result set.

  • where clause
  • Where() method

Sorting is very easy with LINQ, too. The default is to sort entities in an “ascending” order.

  • orderby ascending / orderby descending clause
  • OrderBy() / OrderByDescending() methods
var query = from p in context.Products.Include(p => p.Category)
            where p.Category.Name == "food"
            orderby p.Name
            select p;
foreach (Product p in query)
{
  Console.WriteLine("{0}: {1}, {2}", p.Category.Name, p.Name, p.Price.ToString("c"));
}

foreach (Product p in context.Products.Include(p => p.Category)
                             .Where(p => p.Category.Name == "toy")
                             .OrderByDescending(p => p.Name))
{
  Console.WriteLine("{0}: {1}, {2}", p.Category.Name, p.Name, p.Price.ToString("c"));
}

 

3. Finding a Single Object

Finding the single object in a set (especially with a key) can be one of the most frequent operations.

In general, you can use LINQ’s “SingleOrDefault()” or “FirstOrDefault()” method. But if you find a single object with a key value, DbSet provides the “Find()” method.

  • public TEntity Find(params Object[] keyValues);
using (var context = new ProductContext())
{
  Product p1 = context.Products.SingleOrDefault(p => p.Name == "Bread");
  Console.WriteLine("{0}: {1}", p1.Name, p1.Price.ToString("c"));

  Product p2 = context.Products.Find(4); // p.Id == 4
  Console.WriteLine("{0}: {1}", p2.Name, p2.Price.ToString("c")); }

 

4. Querying Local Data

LINQ always send a query to the underlying database even though you already loaded the data into memory. The “Find()” method makes use of this in-memory data if possible.

If you want to send aquery to the in-memory data rather than a database, you can use the DbSet’s “Local” property.

  • public ObservableCollection<TEntity> Local { get; }
using (var context = new ProductContext())
{
  int count = context.Products.Local.Count; // before loading
  Console.WriteLine("Count: {0}", count);

  context.Products.Load();
  count = context.Products.Local.Count; // after loading

  Console.WriteLine("Count: {0}", count);
  var query = from p in context.Products.Local
              where (p.Name == "Bread")
              select p;

  Console.WriteLine("Query: {0}", query.GetType().FullName); // Just LINQ to Object

  foreach (var p in query)
  {
    Console.WriteLine("{0}: {1}", p.Name, p.Price.ToString("c"));
  }
}

 

5. ObservableCollection

Using the “System.Collections.ObjectModel.ObservableCollection<T>” class, you can monitor the context object.

public class ObservableCollection<T> : Collection<T>, INotifyCollectionChanged, INotifyPropertyChanged
{
  event NotifyCollectionChangedEventHandler CollectionChanged; // Occurs when an item is added, removed, changed, moved

  event PropertyChangedEventHandler PropertyChanged; // Occurs when a property value changes
}

public delegate void NotifyCollectionChangedEventHandler(Object sender, NotifyCollectionChangedEventArgs e); // System.Collections.Specialized

public delegate void PropertyChangedEventHandler(Object sender, PropertyChangedEventArgs e); // System.ComponentModel

By listening these events (add handlers), you can monitor any changes in the context.

public class NotifyCollectionChangedEventArgs : EventArgs
{
  public NotifyCollectionChangedAction Action { get; } // Add, Remove, Replace, Move, or Reset

  public IList NewItems { get; }  // added items

  public IList OldItems { get; }  // items affected by a Replace, Remove, or Move action
}

public class PropertyChangedEventArgs : EventArgs
{
  public string PropertyName { get; } // the name of the property that changed
}

You can access the “ObservableCollection” object through the “Local” property of DbSet.

public static void TestObservableCollection()
{
  using (var context = new ProductContext())
  {
    context.Products.Local.CollectionChanged
      += new NotifyCollectionChangedEventHandler(OnCollectionChanged);

    context.Products.Add(new Product { Id = 1, Name = "Car", Description = "Toy Car", Price = 9.99M });

    Product p = context.Products.Find(1);
    p.Price = 8.99M;

    context.Products.Remove(p);
  }
}

public static void OnCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
  Console.WriteLine(" === Action: {0}", e.Action);

  if (e.NewItems != null)
  {
    foreach(Product p in e.NewItems)
      Console.WriteLine("New - {0}: {1}", p.Name, p.Price.ToString("c"));
  }

  if (e.OldItems != null)
  {
    foreach(Product p in e.OldItems)
      Console.WriteLine("Old - {0}: {1}", p.Name, p.Price.ToString("c"));
  }
}

One thought on “[Entity Framework] DbContext API – Querying Entities

  1. Great article, came close to answering my questions. Would love to see how to start with a dbContext and perform filtering and ordering and return an ObservableCollection (Local) for use in MVVM app. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s