LINQ and DataTable

//Select entire row (or List of rows)
var results = (from DataRow row in dt.Rows
               where (String)row["ColulmnA"] == "SearchCriteria"
               select row).FirstOrDefault(); //or .ToList()

var test = results["ColulmnB"];    //if .FirstOrDefault() used.
var test = results[0]["ColulmnB"]; //if .ToList() used.

//Select specific column (or, List of columns)
var query = (from row in dt.AsEnumerable()
             where row.Field<int>("ColumnA") == 123
             select row.Field<int>("ColumnB")).FirstOrDefault(); //or .ToList()
var test = query;    //if .FirstOrDefault() used.
var test = query[0];    //if .ToList() used.
Advertisements

Entity Framework 5 – CRUD Operations

Entity Framework 5 (EF5) is the latest public release of Microsoft’s ADO.net Entity Framework model. The whole purpose of Entity Framework is to make working with data easier and faster. CRUD (Create, Retrieve, Update, Delete) operations is how we add new data, delete data, update existing data and retrieve or pull data from a database.

A framework or ORM such as Entity Framework is totally useless for data operations if it no ability to perform CRUD operations.

Today we are going to look at how to perform these CRUD operations using Linq to Entities. The assumptions that we have is that you have already created your Model and you are at least familiar with Linq.

In all cases we have to have a context to programme against. So I am going to use the using (EFDemoEntities dbc = new EFDemoEntities()) method in all my examples

Retrieve Data

using (EFDemoEntities dbc = new EFDemoEntities())

{

var prodModels = from p in dbc.ProductModels

select p;

}

Above is a simple Linq statement that retrieves all data and all columns from the ProductModel table. Because we are using Linq To Entities we are programming against the Entity (Class), i.e. ProductModels. This would then return a IQueryable collection of ProductModel (IQueryable<ProductModel>). Remember that Entity Framework uses lazy loading by default. This means that the SQL statement is prepared by no actual SQL execution is performed by the database to retrieve the data until it is actually needed. That is we have to iterate over the collection or use something else that will perform some kind of iteration, like binding to a grid.

So in the example above and all other examples, unless otherwise stated, we will automatically bind to a grid to product a similar output as below. This would then be a sample result of the above Linq statement.

The above would produce the following SQL:

SELECT

[Extent1].[ProductModelID]AS[ProductModelID],

[Extent1].[Name]AS[Name],

[Extent1].[CatalogDescription]AS[CatalogDescription],

[Extent1].[rowguid]AS[rowguid],

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[ProductModel]AS [Extent1

Update Data

To update date we retrieve a single row by using a where clause. That would return a single object. We can then alter the property values of the object and the save it back to the database.

There are several ways of retrieving a subset of data. The simplest is to use a where clause.

using (EFDemoEntities dbc = new EFDemoEntities())

{

var prodModels = (from p in dbc.ProductModels

where p.ProductModelID == 10

select p).SingleOrDefault();

prodModels.Name = “New Product Name”;

dbc.SaveChanges();

}

 

Notice the use of the SingleOrDefault extension method. This ensures that we get a single populated ProductModel Object. Single would throw an exception if the resultant query returned more than one row. We would then make any changes we need to by changing the values of one or any of the properties.

prodModels.Name = “New Product Name”;

 

Now to save the changes we just call the SaveChanges method of the Context

dbc.SaveChanges();

The Following SQL would be produced:

SELECTTOP (2)

[Extent1].[ProductModelID]AS[ProductModelID],

[Extent1].[Name]AS[Name],

[Extent1].[CatalogDescription]AS[CatalogDescription],

[Extent1].[rowguid]AS[rowguid],

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[ProductModel]AS [Extent1]

WHERE 10 =[Extent1].[ProductModelID]

GO

 

— Region Parameters

DECLARE@0NVarChar(50) = ‘New Product Name’

DECLARE@1Int= 10

— EndRegion

update[SalesLT].[ProductModel]

set[Name]=@0

where ([ProductModelID]=@1)

Create New Data

In order to create or insert new date we first instantiate a new type. We then update the values of the properties. Next we add it to the underlying set of the context and then save that back to the database.

using (EFDemoEntities dbc = new EFDemoEntities())

{

ProductModel newProdModel = new ProductModel();

newProdModel.Name = “NewProductName”;

newProdModel.rowguid = Guid.NewGuid();

newProdModel.ModifiedDate = DateTime.Today;

//Add to Entity Set of context

dbc.ProductModels.Add(newProdModel);

//Save

dbc.SaveChanges();

 

}

Which would produce the following SQL:

— Region Parameters

DECLARE@0NVarChar(50) = ‘NewProductName’

DECLARE@1UniqueIdentifier= ‘bb27dbdc-5937-4c1d-83bc-699ab16f1116’

DECLARE@2DateTime2= ‘2013-04-08 00:00:00.0000000’

— EndRegion

insert[SalesLT].[ProductModel]([Name], [CatalogDescription], [rowguid],[ModifiedDate])

values (@0, null, @1, @2)

select[ProductModelID]

from[SalesLT].[ProductModel]

where@@ROWCOUNT> 0 and [ProductModelID] = scope_identity()

Notice the extra select with the scope_identity() statement. This is actually returning the inserted row back to our model. In fact we can use that new inserted row and bind it directly to a control. So if you inspected newProductModel and or bind it to a control right after the save changes method, you would expect to see data if all went well.

Deleting Data

Like updating data I need to first retrieve the row that I want to delete and then delete it. This might at first seem weird, but remember we are working with objects. So our typed Entity Object needs to know which row or data it needs to delete. Once we have that we can then just delete it from the Entity set of the context. We use the Single() extension method to make sure we get only one row back. This would through an exception if either a null or more than one row is returned.

using (EFDemoEntities dbc = new EFDemoEntities())

{

ProductModel delProdModels = (from p in dbc.ProductModels

where p.ProductModelID == 129

select p).Single();

//Delete

dbc.ProductModels.Remove(delProdModels);

//Save the changes to the database

dbc.SaveChanges();

 

}

The following SQL would be produced, excluding the initial select to find the row:

— Region Parameters

DECLARE@0Int= 129

— EndRegion

delete[SalesLT].[ProductModel]

where ([ProductModelID]=@0)

Conclusion

CRUD operations are extremely important. Using Entity Framework makes that job just a little easier. The thing we have to keep in mind is that we are not programming against a database but against the Entity Model even though our final result will affect the database.

Related Reading:

An Introduction to Entity Framework 5

The Difference between IQueryable and IEnumerable

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");