Entity Framework & LINQ Made Easy

Finding entities using a query

using (var context = new BloggingContext())
{
// Query for all blogs with names starting with B
var blogs = from b in context.Blogs where b.Name.StartsWith(“B”) select b;

// Display all Blogs from the database
var query = from b in db.Blogs  orderby b.Name   select b;

// Query for the Blog named ADO.NET Blog
     var blog = context.Blogs.Where(b => b.Name == ”ADO.NET Blog”).FirstOrDefault();

    //Selective columns with multiple conditions
var tabl = (from c in context .TableName 
where c.Date == date &&  c.Id == Id
                      select new
                             {
                                   Lat = c.Lat,
                                   Lon = c.Lon,
                                   Name = c.OutletName
                             }).ToList();
}

Finding entities using primary keys

The Find method on DbSet uses the primary key value to attempt to find an entity tracked by the context. If the entity is not found in the context then a query will be sent to the database to find the entity there. Null is returned if the entity is not found in the context or in the database.

Find is different from using a query in two significant ways:

  • A round-trip to the database will only be made if the entity with the given key is not found in the context.
  • Find will return entities that are in the Added state. That is, Find will return entities that have been added to the context but have not yet been saved to the database.

Finding an entity by primary key

The following code shows some uses of Find:

using (var context = new BloggingContext())
{
// Will hit the database
var blog = context.Blogs.Find(3);// Will return the same instance without hitting the database
var blogAgain = context.Blogs.Find(3);context.Blogs.Add(new Blog { Id = -1 });// Will find the new blog even though it does not exist in the database
var newBlog = context.Blogs.Find(-1);// Will find a User which has a string primary key
var user = context.Users.Find(“johndoe1987″);
}

Finding an entity by composite primary key

Entity Framework allows your entities to have composite keys – that’s a key that is made up of more than one property. For example, you could have a BlogSettings entity that represents a users settings for a particular blog. Because a user would only ever have one BlogSettings for each blog you could chose to make the primary key of BlogSettings a combination of BlogId and Username. The following code attempts to find the BlogSettings with BlogId = 3 and Username = “johndoe1987″:

  1. using (var context = new BloggingContext())
  2. {
  3.     var settings = context.BlogSettings.Find(3, ”johndoe1987″);
  4. }

Note that when you have composite keys you need to use ColumnAttribute or the fluent API to specify an ordering for the properties of the composite key. The call to Find must use this order when specifying the values that form the key.

Save

var blog = new Blog { Name = name };
db.Blogs.Add(blog);
db.SaveChanges();

Deleting

I looked at this before but here I am looking at it again because I think I got it wrong (or only half right anyway).

Assume that I’m working with a default 1:1 mapping of Northwind and that I have a customer with a primary key value of “DEMO1” who also has one order.

The first thing to say is that I can’t delete that customer without querying for it. That is, there is no method such as;

ObjectContext.DeleteEntities<T>(string EntitySet, Func<bool, T> predicate);

so, you have to query first.

Let’s imagine that I want to delete my customer DEMO1;

     using (NorthwindEntities ctx = new NorthwindEntities())
      {
        Customers demo1 =
          ctx.Customers.Where(c => c.CustomerID == "DEMO1").ToList()[0];

        ctx.DeleteObject(demo1);

        ctx.SaveChanges(true);
      }

A couple of notes about that code;

  1. Why is it using ToList()[0] – essentially I’m using this as a replacement for First() because there seems to be a bug inFirst() in the current beta 2 bits.
  2. SaveChanges(true) or SaveChanges(false) ? It depends – essentially SaveChanges can fail (e.g. for concurrency reasons). If it fails then do you want to preserve all the information in your ObjectStateManager about which objects are unchanged/added/modified/removed? If so, then pass false otherwise pass true.

This code will fail because the customer DEMO1 has Orders and I’ve not removed them. There’s a few ways around that;

     using (NorthwindEntities ctx = new NorthwindEntities())
      {
        Customers demo1 =
          ctx.Customers.Include("Orders").Where(c => c.CustomerID == "DEMO1").ToList()[0];

        foreach (Orders o in demo1.Orders.ToList())
        {
          demo1.Orders.Remove(o); // This orphans the order from the customer.
          ctx.DeleteObject(o);    // This deletes it - depends what you want.
        }
        ctx.DeleteObject(demo1);

        ctx.SaveChanges(true);
      }

Some notes about that code;

  1. We can choose to delete the orders from the DB or just orphan them from the customer record.
  2. Why the ToList() on the foreach – it’s a cheap trick because we’re about to alter the demo1.Orders collection so we need some kind of copy to enumerate around.

We could equally well do;

      using (NorthwindEntities ctx = new NorthwindEntities())
      {
        Customers demo1 =
          ctx.Customers.Where(c => c.CustomerID == "DEMO1").ToList()[0];

        demo1.Orders.Load();

        foreach (Orders o in demo1.Orders.ToList())
        {
          demo1.Orders.Remove(o); // This orphans the order from the customer.
          ctx.DeleteObject(o);    // This deletes it - depends what you want.
        }
        ctx.DeleteObject(demo1);

        ctx.SaveChanges(true);
      }

which is just a different way of getting to the Orders.

The strange thing about this so far (to me) is that I have to query something before deleting it – I’d like to be able to just say “delete” because if I’ve got 1 million orders attached to a customer then I don’t want to query them before getting rid of them.

I guess I could use a stored procedure for that or I could set the database to cascade the delete of the customer to its orders.

Now, the conceptual model has support for cascading deletes. So, I can modify my model to look like this;

      <Association Name="FK_Orders_Customers">
          <End Role="Customers"
               Type="NorthwindModel.Customers"
               Multiplicity="0..1">
            <OnDelete Action="Cascade"/>
          </End>
          <End Role="Orders" Type="NorthwindModel.Orders" Multiplicity="*" />
        </Association>

Now, frankly I find this a bit weird. If I use this code;

      using (NorthwindEntities ctx = new NorthwindEntities())
      {
        Customers demo1 =
          ctx.Customers.Where(c => c.CustomerID == "DEMO1").ToList()[0];

        ctx.DeleteObject(demo1);

        DumpObjectStateCounts(ctx.ObjectStateManager);

        ctx.SaveChanges(true);

        DumpObjectStateCounts(ctx.ObjectStateManager);
      }

(note – DumpObjectStateCounts is my own function for seeing the state of the ObjectStateManager).

Then what this code does is deletes my customer record from the database. It does not delete any orders for that customer record so it’s only going to work if we’ve set the database up to cascade the deletes.

Let’s say that we have set the database up that way so now we’re happily going along knowing that whenever we delete a customer we will also delete all of its orders (because the database is doing that for us).

However, there’s still something a little odd. If we take a look at this code where we actually load the orders;

      using (NorthwindEntities ctx = new NorthwindEntities())
      {
        Customers demo1 =
          ctx.Customers.Include("Orders").Where(c => c.CustomerID == "DEMO1").ToList()[0];

        ctx.DeleteObject(demo1);

        DumpObjectStateCounts(ctx.ObjectStateManager);

        ctx.SaveChanges(true);

        DumpObjectStateCounts(ctx.ObjectStateManager);
      }

Then what I see is;

  1. After we have deleted the customer record that record, its orders (only one in my case) and the relationship between them all get marked as deleted by the ObjectStateManager so there is a cascade going on inside my program.
  2. When we call SaveChanges we execute SQL to;
    1. Delete the customer record causing the database to cascade the deletes.
    2. Delete the orders records (even though they aren’t in the database any more).

So, this one is a bit weird for me. It feels like the work of deleting the orders is now being done twice – once by the framework and once by the database. This will work but it seems a little “over the top”.

What conclusion can you draw from this?

  1. If you want (e.g.) orders to be deleted for a customer when those orders haven’t been loaded by your program then you’re going to have to set your database to cascade those deletes.
  2. In order that the right thing happens with respect to the ObjectStateManager you’re also going to have to mark your association to cascade the deletes in the conceptual model.

But…if you do that and you have (again e.g.) orders loaded then you’re going to send a bunch of deletes to the database that don’t have any effect which seems a little odd 😦

Quite happy to admit that I’ve got something wrong here as it doesn’t seem like this is how it’s meant to work?

SQL

DbContext ctx = ... get your DbContext somehow... 
ctx.Database.ExecuteSqlCommand("DELETE FROM xxx WHERE xxxId= 1");
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s